Microsoft Visual Basic UserForm 2 columns, Excel 2000 & 2003
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 |
Microsoft Visual Basic UserForm 2 columns, Excel 2000 & 2003
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/...nloaduserforms 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 |
All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com