Microsoft Visual Basic UserForm 2 columns, Excel 2000 & 2003

[jfcby]

Hello,

I'm using a worksheet that list my data like so:

C D
5 AHU Air Handling Unit
6 DKFTN Drinking Fountain
7 FCU Fan Coil Unit
8 Fan Exhaust Fan

I've setup a Microsoft Visual Basic UserForm with a ListBox in 1
column list Worksheet Column C data. When I click the data in the
Listbox it inserts Column C data into the active worksheet cell and D
column is inserted into the cell to the right. (For example: Column C
= Column F6 and Column D = Column F7)

Now I'm needing a Listbox with 2 columns: Column 1 = C and Column 2 =
D.

How can the following code be modified to display 2 columns of data?

Private Sub ListBox4_Click()
With Me.ListBox4
ActiveCell.Value = .List(.ListIndex, 0)
ActiveCell.Offset(0, 1).Value = .List(.ListIndex, 1)
End With
End Sub

Private Sub UserForm_Initialize()
Dim r As Range
Dim wb As Workbook
'>>>>
With Sheets("Major_Category_MODIFY 2")
Set r = .Range(.Range("C5"), _
.Range("C" & Rows.Count).End(xlUp))
End With
With ListBox4
.ColumnCount = 2
.ColumnWidths = .Width - 1 & ";0"
.List = r.Resize(, 2).Value
End With
'<<<<
End Sub

Thank you for your help,
jfcby

 

[James F Cooper]

Hello,

Private Sub UserForm_Initialize()
Dim r As Range
Dim wb As Workbook
'>>>>
With Sheets("Major_Category_MODIFY 2")
Set r = .Range(.Range("C5"), _
.Range("C" & Rows.Count).End(xlUp))
End With
With ListBox4
.ColumnCount = 2
.ColumnWidths = .Width - 1 & ";0"
.List = r.Resize(, 2).Value
End With
'<<<<
End Sub

I kept working with the above code, searhing the newsgroup, and googled the
User Forms. The link that helped me figure out how to modify the code was:

http://www.erlandsendata.no/english/index.php?d=endownloaduserforms

Scrool down to "ListBox example 1".

This is the modified working code:

Private Sub UserForm_Initialize()
Dim r As Range
Dim wb As Workbook
'>>>>
With Sheets("Major_Category_MODIFY 2")
Set r = .Range(.Range("C5"), _
.Range("C" & Rows.Count).End(xlUp))
End With
With ListBox4
.ColumnCount = 2
.ColumnWidths = "100,75" '.Width - 1 & ";0"
.List = r.Resize(, 1).Value
.List = r.Resize(, 2).Value
End With
'<<<<
End Sub

Thank you for your help,
jfcby