Add Two Columns of Data into a ComboBox in Userform Initialize Eve

R

RyanH

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
 
D

Dave Peterson

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.)
 
R

RyanH

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 said:
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.)
Click to expand...
 
D

Dave Peterson

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


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.
Click to expand...
 
R

RyanH

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 said:
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
Click to expand...
 
D

Dave Peterson

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.


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?
Click to expand...
 
Top