how do I handle user input not in combobox

grNadpa · #1 03-31-2022, 11:32 AM

Fundamental question, I know. But I cannot find what I expect is a very simple answer.
I load the form in my combobox from an excel spreadsheet with no problem. And I populate my other textboxes when the user SELECTS from that list.

Code:

Private Sub ComboBoxNames_Change()
    sheetRow = ComboBoxNames.ListIndex + 2
    '   listIndex is base 0; first data in sheet is row 2
    With Sheets(sheetName)
        TextBoxName.Text = .Cells(sheetRow, 1).Value
        TextBoxAddress.Text = .Cells(sheetRow, 2).Value
        TextBoxCity.Text = .Cells(sheetRow, 3).Value
        If .Cells(sheetRow, 4).Value = isActive Then
            OptionButtonActive.Enabled = True
        Else
            OptionButtonInactive.Enabled = True
        End If
            
    End With
    CommandButtonAction.Caption = updateAction
    CommandButtonAction.Enabled = True
End Sub

I know that the ComboBoxNames.Listindex is 0 if I type something not in the list, but that doesn't help. I've tried substituting

Code:

ComboBoxNames_Exit() ...

but that doesn't populate my TextBoxes.

Logit · #2 03-31-2022, 01:00 PM

Hope I've Correctly understood ...

If you arw using a Form with controls on it to write data to a sheet ... try specifying the use of a form:

Code:

.Cells(sheetRow, 1).Value =Me.TextBoxName.Text
         .Cells(sheetRow, 2).Value = Me.TextBoxAddress.Text
        .Cells(sheetRow, 3).Value = Me.TextBoxCity.Text

grNadpa · #3 03-31-2022, 02:27 PM

Thank you @Logit,

Not quite. I'm loading the form's text boxes from the combobox (that I populated from a spreadsheet.)

This is a CRUD (Create Retrieve Update Delete) attempt. My combobox serves well as the target for the Retrieve, and the source for the Update (and Delete).

But not the Create.

If I can type in a name not in the combobox, which I believe is a feature that distinguishes it from a listbox, I can eliminate the TextBoxName control and set the cursor to the address text box. Which my user would find more intuitive.

But I am not understanding how to use the "textbox" combobox capability. That is, which event I should be using insted of ComboBoxName_Change()

Note: The code you thoughtfully supplied is what close to what I use to accomplish my Update (And my Add after I programmatically insert a row).

Logit · #4 03-31-2022, 05:28 PM

Recommend you post a copy of your workbook. No confidential information ... no greater than 20 examples of 'before' and 'after' in the workbook.

Debaser · #5 04-01-2022, 01:34 AM

Quote:

Originally Posted by grNadpa

I know that the ComboBoxNames.Listindex is 0 if I type something not in the list

Actually, it's -1 if no matching item is selected. 0 would mean the first item has been selected.

grNadpa · #6 04-01-2022, 03:19 AM

By restating my question (below), I blundered into my answer.

Assume a combox containing "Adam", Beverly" and "Charles". When the client selects one of those, I want to capture the Listindex. If, instead, the client types in something else, I want to capture what the client typed (e.g. "Alfred"). Is there a way in VBA to do that? Preferably without requiring another control.

Code:

Private Sub CommandButton1_Click()
    With ComboBox1
        .AddItem "Adam"
        .AddItem "Beverly"
        .AddItem "Charles"
    End With
End Sub
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With ComboBox1
        MsgBox "Listindex: " & .ListIndex
        MsgBox "Name: " & .Text
    End With
End Sub

That is, thought I had tried _Exit event before. Whatever I coded didn't work at the time so thought it was the strategy. Apparently it was just the coding.

Logit · #7 04-01-2022, 10:32 AM

Glad you found your answer.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
User input box with IF statement	ChrisJ83	Word VBA	9	11-13-2015 06:20 PM
Taking input from InputBox from user	SeattleITguy	Excel Programming	1	01-28-2015 09:05 AM
vba: user input named argument	andrew12345	Excel Programming	2	11-18-2014 08:18 AM
User input to a variable on the document	dsm1995gst	Word VBA	1	09-03-2013 03:43 PM
Replacing text with user input.?.?.?	brad1977	Word	3	11-20-2012 10:20 AM