Excel UserForm ComboBox/ListBox

[pujo]

In an excel UserForm, I have 6 text boxes and a list box.
The listbox list is a table. When I open the userform and click on an item in the list box, the values are displayed in the text boxes on the userform.
This allows the user to edit the record.

Private Sub ListBoxBillingAddress_Click()
    Me.txtbxBillingOffice.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 0)
      Me.textboxCompany.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 1)
        Me.textboxAddress.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 2)
        Me.textboxCity.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 3)
      Me.comboState.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 4)
    Me.textboxZip.Value = Me.ListBoxBillingAddress.List(ListBoxBillingAddress.ListIndex, 5)
End Sub

I want to incorporate a combobox to do the same thing.

The combobox list is as followed:

Me.cboSearch.List = ThisWorkbook.Worksheets("Billing").Range("tblBilling[Billing Office]").Value

When a "Billing Office" is selected, I want to populate the text boxes on the userfrom the same was as when I click on the list box item.
The user does not have access to the actual table, I am doing it all from a userform. When there is lots of entries in the table,
I think it would be easier to select the record to edit from a combobox rather than looking through all the listbox records.

I can't seem to figure out the syntax. Everything I have tried results in an error, could not et the list property...

All help is much appreciated!

 

[Fluff]

How about like

Private Sub ComboBox1_Click()
   Dim Rw As Long
   
   Rw = Me.ComboBox1.ListIndex + 1
   With ThisWorkbook.Worksheets("Billing").ListObjects("tblBilling").ListColumns("Billing Office").DataBodyRange
      Me.TextBox1.Value = .Cells(Rw, 2).Value
      Me.TextBox2.Value = .Cells(Rw, 3).Value
      Me.TextBox3.Value = .Cells(Rw, 4).Value
   End With
      
End Sub

 

[pujo]

@Fluff
You never cease to amaze me!

Perfect as always Fluff!
Happy New Year!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[pujo]

@Fluff
So I thought that I could use Rw to select the same listbox item as selected in the combobox.
Since the ListBox item is highlighted, I didn't want to confuse the user when the combobox is different.
I was wrong....

Private Sub cboSearch_Click()
   Dim Rw As Long
   Rw = Me.cboSearch.ListIndex + 1
   
   With ThisWorkbook.Worksheets("Billing").ListObjects("tblBilling").ListColumns("Billing Office").DataBodyRange
        Me.textboxBillingOffice.Value = .Cells(Rw, 1).Value
        Me.textboxCompany.Value = .Cells(Rw, 2).Value
        Me.textboxAddress.Value = .Cells(Rw, 3).Value
        Me.textboxCity.Value = .Cells(Rw, 4).Value
        Me.comboState.Value = .Cells(Rw, 5).Value
        Me.textboxZip.Value = .Cells(Rw, 6).Value
   End With
   
    For Rw = 0 To ListBoxBillingAddress.ListCount + 1
        ListBoxBillingAddress.Selected(Rw) = True
    Next Rw
  
Debug.Print Rw

End Sub

When I debug Rw, the ListCount is always 2.
I have also tried ListIndex but the logic didn't like that..

Any ideas?

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   Dim Rw As Long
   
   Rw = Me.ComboBox1.ListIndex + 1
   With ThisWorkbook.Worksheets("Billing").ListObjects("tblBilling").ListColumns("Billing Office").DataBodyRange
      Me.TextBox1.Value = .Cells(Rw, 2).Value
      Me.TextBox2.Value = .Cells(Rw, 3).Value
      Me.TextBox3.Value = .Cells(Rw, 4).Value
   End With
   Me.ListBox1.Selected(Rw - 1) = True
End Sub

 

[pujo]

You are a true Rock Star in the world of VBA!
I just found another thread that you replied to with a similar question here. listbox, select item based on cell/variable value vba
I used that example and changed it up just a bit and it worked just fine.

 Dim i As Long
 Dim Search As String
 
 Search = cboSearch.Value
 With ListBoxBillingAddress
    For i = 0 To .ListCount - 1
        If .List(i) = Search Then
            .Selected(i) = True
        End If
    Next i
 End With

However, if it is better to use

Me.ListBox1.Selected(Rw - 1) = True

Then that's what I will do.

Thanks again!!!

 

[Fluff]

Not necessarily, better, but gets rid of the loop

 

[pujo]

@Fluff

Good Morning & Happy New Year!

While creating my workbook and userform…..
I came to the realization that while actual data tables are useful, they really give me a headache when manipulating data from a userform.

I’ve converted the table to a range to try and make things easier…
Now that I have done so, I have noticed an issue that may have been there prior to converting to range.

Userform has a ListBox and a ComboBox.

ListBox:
In the ListBox, a user can double click an item, and the associated TextBoxes on the userform will populate with the selected line.
The purpose of this was to give the user the ability to update the record.

ComboBox:
I am trying to think ahead when there is a lot of data in the workbook so I put a ComboBox on the userform that will pull only the data from column 1 of the data range
for a quicker search function. When a record is selected from the ComboBox, it also populated the associated TextBoxes on the userform as well as select the same ListBox line item
so that the user isn't confused by the highlighted line in the ListBox and the data in the TextBoxes.

Issue:
When selecting an item from the ListBox, the row number of that record is correct. (column 7 of data range =Row() )
When selecting the same record from the ComboBox, the row number is one less. I am sure I can correct this by adding 1 to the TextBox but is this correct??

I am using the actual row number not -1 for the headeer row. My plan is to use the actual row number to update the record.

Code ListBox/ComboBox
WorkBook shared on my OneDrive. Link--> UserForm_Test.xlsm

ListBox:

'<-- UserForm Initialization Step 1 -->
Private Sub PopListBox() '<-- Populate the UserForm ListBox with data from Billing
 Dim Sh As Worksheet
 Set Sh = ThisWorkbook.Worksheets("Billing")
 Dim lRow As Long
 lRow = Application.WorksheetFunction.CountA(Sh.Range("A:A"))
  With ListBoxBilling
    .ColumnHeads = True
    .ColumnCount = 7
    .ColumnWidths = "160,170,125,100,100,70,0"
    .RowSource = "Billing!A2:G" & lRow
  End With
End Sub

ComboBox:

'<-- UserForm Initialization Step 2 -->
Private Sub PopComboSearch() '<-- Populate the ComboSearchBox with values from First Column of Billing
 Dim Sh As Worksheet
 Set Sh = ThisWorkbook.Worksheets("Billing")
 Dim lRow As Long
  lRow = Application.WorksheetFunction.CountA(Sh.Range("A:A"))
  Me.cboSearch.List = Sh.Range("A2:A" & lRow).Value
End Sub

ListBox DoubleClick:

'<------------------------- Set ListBox Record In UserForm TextBoxes for Editing ------------------------->
Private Sub ListBoxBilling_DblClick(ByVal Cancel As MSForms.ReturnBoolean) '<-- Set ListBox Record for Editing
 Dim MyRow As Long
 If ListBoxBilling.ListIndex <> -1 Then
  With ListBoxBilling
    textboxBillingOffice.Value = .List(.ListIndex, 0)
    textboxCompany.Value = .List(.ListIndex, 1)
    textboxAddress.Value = .List(.ListIndex, 2)
    textboxCity.Value = .List(.ListIndex, 3)
    comboState.Value = .List(.ListIndex, 4)
    textboxZip.Value = .List(.ListIndex, 5)
    textboxID.Value = .List(.ListIndex, 6)
  End With
 End If
End Sub

ComboBox Click:

'<------------------------- Item from cboSearch will select the same line item in ListBox ------------------------->
Private Sub cboSearch_Click() '<-- Set the cboSearch ListIndex
 Dim Rw As Long
  Rw = Me.cboSearch.ListIndex + 1
    With ThisWorkbook.Worksheets("Billing").Columns(1)
      Me.textboxBillingOffice.Value = .Cells(Rw, 1).Value
      Me.textboxCompany.Value = .Cells(Rw, 2).Value
      Me.textboxAddress.Value = .Cells(Rw, 3).Value
      Me.textboxCity.Value = .Cells(Rw, 4).Value
      Me.comboState.Value = .Cells(Rw, 5).Value
      Me.textboxZip.Value = .Cells(Rw, 6).Value
      Me.textboxID.Value = .Cells(Rw, 7).Value
    End With
 Me.ListBoxBilling.Selected(Rw - 1) = True
 Me.cboSearch.Value = Null
End Sub

 

[Fluff]

As it's no longer a table you will need to use

Private Sub cboSearch_Click() '<-- Set the cboSearch ListIndex
 Dim Rw As Long
  Rw = Me.cboSearch.ListIndex + 2
    With ThisWorkbook.Worksheets("Billing").Columns(1)
      Me.textboxBillingOffice.Value = .Cells(Rw, 1).Value
      Me.textboxCompany.Value = .Cells(Rw, 2).Value
      Me.textboxAddress.Value = .Cells(Rw, 3).Value
      Me.textboxCity.Value = .Cells(Rw, 4).Value
      Me.comboState.Value = .Cells(Rw, 5).Value
      Me.textboxZip.Value = .Cells(Rw, 6).Value
      Me.textboxID.Value = .Cells(Rw, 7).Value
    End With
 Me.ListBoxBilling.Selected(Rw - 2) = True
 Me.cboSearch.Value = Null
End Sub