Userform - Listbox update

[Gregg Wolin]

The listbox in the attached userform works as designed, however I would like two of the listbox columns to be shown in a single column. I created a helper column and was able to get the listbox to work, but when the double-click sub is activated (which populates the textboxes with the values from the listbox), it doesn't populate properly.

Thanks in advance!

 

[Kenneth Hobson]

If you don't want the last column, change the number of columns in the listbox.

Using the data starting in K1:

Sub refresh_data()
  Dim r As Range, sh As Worksheet
 
  Set sh = ThisWorkbook.Sheets("DATA_Parcels")
  Set r = Worksheets("DATA_Parcels").Range("K1").CurrentRegion
 
  With ListBox1
    .Font.Name = Arial
    .Font.Size = 9
    .ColumnHeads = True
    .ColumnCount = 7
    .ColumnWidths = "20,40,40,40,60,30,60"
    .RowSource = r.Address(External:=True)
  End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim i As Integer, wd As String
 
  If ListBox1.ListIndex = 0 Then Exit Sub
 
  For i = 1 To 3
    Controls("tbox" & i) = ListBox1.List(ListBox1.ListIndex, i)
  Next i
 
  wd = ListBox1.List(ListBox1.ListIndex, 4)
  If InStr(wd, "x") > 0 Then
    a = Split(wd, "x")
    tbox4 = a(0)
    tbox5 = a(1)
  End If
 
  tbox6 = ListBox1.List(ListBox1.ListIndex, 5)
  TBox99 = ListBox1.List(ListBox1.ListIndex, 0)   'Counter box (hidden)
End Sub

 

[Gregg Wolin]

If you don't want the last column, change the number of columns in the listbox.

Using the data starting in K1:

Sub refresh_data()
  Dim r As Range, sh As Worksheet

  Set sh = ThisWorkbook.Sheets("DATA_Parcels")
  Set r = Worksheets("DATA_Parcels").Range("K1").CurrentRegion

  With ListBox1
    .Font.Name = Arial
    .Font.Size = 9
    .ColumnHeads = True
    .ColumnCount = 7
    .ColumnWidths = "20,40,40,40,60,30,60"
    .RowSource = r.Address(External:=True)
  End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim i As Integer, wd As String

  If ListBox1.ListIndex = 0 Then Exit Sub

  For i = 1 To 3
    Controls("tbox" & i) = ListBox1.List(ListBox1.ListIndex, i)
  Next i

  wd = ListBox1.List(ListBox1.ListIndex, 4)
  If InStr(wd, "x") > 0 Then
    a = Split(wd, "x")
    tbox4 = a(0)
    tbox5 = a(1)
  End If

  tbox6 = ListBox1.List(ListBox1.ListIndex, 5)
  TBox99 = ListBox1.List(ListBox1.ListIndex, 0)   'Counter box (hidden)
End Sub

Thanks Ken. This is an interesting method. However, notwithstanding that your code for the listbox includes ".ColumnHeads = True" when i launch the form, the headers now show up in the first row of values. I thought it might be the result of setting the "r" variable to Range ("K1") but when i change it to "K2", the headers still show up in the first line of values rather than the header.

 

[Kenneth Hobson]

Sub refresh_data()
  Dim r As Range
  
  With ThisWorkbook.Sheets("DATA_Parcels")
    Set r = .Range("K2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 7)
  End With
  
  With ListBox1
    .Font.Name = Arial
    .Font.Size = 9
    .ColumnHeads = True
    .ColumnCount = 7
    .ColumnWidths = "20,40,30,30,30,30,30"
    .RowSource = r.Address(External:=True)
  End With
End Sub