Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize Eve
I currently have a ListStyle ComboBox. The ComboBox contains product codes.
I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize Eve
If your data is on a worksheet, you can pick up both columns with code like:
Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex < 0 Then 'nothing selected in the combobox Exit Sub End If With Me.ComboBox1 MsgBox .List(.ListIndex, 0) & vbLf & .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" .List = myRng.Value End With End Sub ================== If you have to use .additem, you could use: Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" For Each myCell In myRng.Columns(1).Cells .AddItem myCell.Value .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value Next myCell End With End Sub (only the _initialize event changed.) RyanH wrote: I currently have a ListStyle ComboBox. The ComboBox contains product codes. I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize
Is there a way to code the Col.1 and Col.2 options in the Intialize Event,
because I don't use ranges as a source.? I just want to type it in manually in that event. -- Cheers, Ryan "Dave Peterson" wrote: If your data is on a worksheet, you can pick up both columns with code like: Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex < 0 Then 'nothing selected in the combobox Exit Sub End If With Me.ComboBox1 MsgBox .List(.ListIndex, 0) & vbLf & .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" .List = myRng.Value End With End Sub ================== If you have to use .additem, you could use: Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" For Each myCell In myRng.Columns(1).Cells .AddItem myCell.Value .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value Next myCell End With End Sub (only the _initialize event changed.) RyanH wrote: I currently have a ListStyle ComboBox. The ComboBox contains product codes. I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize
One way:
Private Sub UserForm_Initialize() With Me.ComboBox1 .ColumnCount = 2 .ColumnWidths = "12;0" .AddItem "AF" .List(.ListCount - 1, 1) = "Aluminum Faces" .AddItem "BP" .List(.ListCount - 1, 1) = "Banner Prints" 'and so on End With End Sub RyanH wrote: Is there a way to code the Col.1 and Col.2 options in the Intialize Event, because I don't use ranges as a source.? I just want to type it in manually in that event. -- Cheers, Ryan "Dave Peterson" wrote: If your data is on a worksheet, you can pick up both columns with code like: Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex < 0 Then 'nothing selected in the combobox Exit Sub End If With Me.ComboBox1 MsgBox .List(.ListIndex, 0) & vbLf & .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" .List = myRng.Value End With End Sub ================== If you have to use .additem, you could use: Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" For Each myCell In myRng.Columns(1).Cells .AddItem myCell.Value .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value Next myCell End With End Sub (only the _initialize event changed.) RyanH wrote: I currently have a ListStyle ComboBox. The ComboBox contains product codes. I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize
That is what I was look for, Thanks!
I do have a quick question though. Since the TextBox portion of the ComboBox is only large enough to display 2 letters, the drop down portion of the ComboBox is too narrow. Can I enlarge the width of the drop down view without enlarging the actual combobox width? -- Cheers, Ryan "Dave Peterson" wrote: One way: Private Sub UserForm_Initialize() With Me.ComboBox1 .ColumnCount = 2 .ColumnWidths = "12;0" .AddItem "AF" .List(.ListCount - 1, 1) = "Aluminum Faces" .AddItem "BP" .List(.ListCount - 1, 1) = "Banner Prints" 'and so on End With End Sub RyanH wrote: Is there a way to code the Col.1 and Col.2 options in the Intialize Event, because I don't use ranges as a source.? I just want to type it in manually in that event. -- Cheers, Ryan "Dave Peterson" wrote: If your data is on a worksheet, you can pick up both columns with code like: Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex < 0 Then 'nothing selected in the combobox Exit Sub End If With Me.ComboBox1 MsgBox .List(.ListIndex, 0) & vbLf & .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" .List = myRng.Value End With End Sub ================== If you have to use .additem, you could use: Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" For Each myCell In myRng.Columns(1).Cells .AddItem myCell.Value .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value Next myCell End With End Sub (only the _initialize event changed.) RyanH wrote: I currently have a ListStyle ComboBox. The ComboBox contains product codes. I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Two Columns of Data into a ComboBox in Userform Initialize
I would just widen the column a bit.
But you could experiment with widening (temporarily) the combobox when it takes focus and then shrink it when you leave the combobox. RyanH wrote: That is what I was look for, Thanks! I do have a quick question though. Since the TextBox portion of the ComboBox is only large enough to display 2 letters, the drop down portion of the ComboBox is too narrow. Can I enlarge the width of the drop down view without enlarging the actual combobox width? -- Cheers, Ryan "Dave Peterson" wrote: One way: Private Sub UserForm_Initialize() With Me.ComboBox1 .ColumnCount = 2 .ColumnWidths = "12;0" .AddItem "AF" .List(.ListCount - 1, 1) = "Aluminum Faces" .AddItem "BP" .List(.ListCount - 1, 1) = "Banner Prints" 'and so on End With End Sub RyanH wrote: Is there a way to code the Col.1 and Col.2 options in the Intialize Event, because I don't use ranges as a source.? I just want to type it in manually in that event. -- Cheers, Ryan "Dave Peterson" wrote: If your data is on a worksheet, you can pick up both columns with code like: Option Explicit Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() If Me.ComboBox1.ListIndex < 0 Then 'nothing selected in the combobox Exit Sub End If With Me.ComboBox1 MsgBox .List(.ListIndex, 0) & vbLf & .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" .List = myRng.Value End With End Sub ================== If you have to use .additem, you could use: Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range With Worksheets("Sheet1") Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.ComboBox1 .ColumnCount = myRng.Columns.Count .ColumnWidths = "12;0" For Each myCell In myRng.Columns(1).Cells .AddItem myCell.Value .List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value Next myCell End With End Sub (only the _initialize event changed.) RyanH wrote: I currently have a ListStyle ComboBox. The ComboBox contains product codes. I want to add a second column that describes the product code, but only display the Col 1 data in the Textbox portion of the Combobox. For Example, Col. 1 Col.2 AF Aluminum Faces BP Banner Prints CC Custom Cabinets DP Digital Prints EC Extruded Cabinets This is what I currently have: Private Sub UserForm_Initialize() With cboProductCode .AddItem "AF" .AddItem "BP" .AddItem "CC" .AddItem "DP" .AddItem "EC" End With End Sub -- Cheers, Ryan -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Initialize | Excel Programming | |||
Userform Initialize | Excel Programming | |||
UserForm initialize event run when UserForm is shown | Excel Programming | |||
userform initialize | Excel Programming | |||
Userform initialize combobox using Chr() function. | Excel Programming |