Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Visual Basic errors displaid when opening Microsoft Word 97 & Excel (7 | Setting up and Configuration of Excel | |||
Microsoft Visual Basic Error in Excel | Excel Discussion (Misc queries) | |||
Automate Microsoft Excel from Visual Basic .NET KB 301982 | Excel Programming | |||
Microsoft Office 2003 issues with Visual Basic | Excel Programming | |||
Excel-Microsoft Visual Basic File Not Found | Excel Programming |