getting a run time error 1004, trying to create a combobox

[dmcgov]

here is my code to initialize the form:

Private Sub userform_initialize()
OptionButton3 = True
OptionButton15 = True
OptionButton1 = True
TextBox1.Value = Format(Now(), "h:mm AM/PM")
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
    For Each cLoc In ws.Range("Names")
        With Me.ComboBox1
           .AddItem cLoc.Value
        End With
    Next cLoc
Me.ComboBox1.SetFocus
End Sub

what am i doing wrong?

[CK76]

Is scope of named range set to workbook or worksheet?

If former, try just using Rane("Names") instead of ws.Range("Names")

[dmcgov]

one quick question, im trying to create a combobox that lists two ranges, the first one is named Hours and the second is offset to that. so why isn't this working?

Private Sub userform_initialize()
OptionButton3 = True
OptionButton15 = True
OptionButton1 = True
'TextBox1.Value = hours & minutes & segment
TextBox1.Value = Format(Now(), "h:mm AM/PM")
Dim cLoc As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
    For Each cLoc In ws.Range("Hours")
        With Me.ComboBox1
           .AddItem cLoc.Value
           .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
        End With
        With Me.ComboBox1
        MsgBox .ListCount
        End With
    Next cLoc
Me.ComboBox1.SetFocus
End Sub

[dmcgov]

thanks CK, but i changed this one line and it's working now.

Set ws = ThisWorkbook.Sheets("Sheet1")

i added the ThisWorkbook to the set statement.

[CK76]

Glad you got it solved. I actually ran some tests and scope had no impact when it's set to workbook.
I guess, you had another workbook active when the code ran.

[CK76]

What's the end result you are trying to achieve?

Should both be loaded to same combobox and selected as one?

If so do something like.

Private Sub userform_initialize()
OptionButton3 = True
OptionButton15 = True
OptionButton1 = True
TextBox1.Value = Format(Now(), "h:mm AM/PM")
Dim cLoc As Range
Dim ws As Worksheet
Dim listArr
Set ws = ThisWorkbook.Sheets("Sheet1")
listArr = ws.Range("Hours").Resize(, 2)
    With Me.ComboBox1
        .ColumnCount = 2
        .List = listArr
    End With
    With Me.ComboBox1
        MsgBox .ListCount
    End With
Me.ComboBox1.SetFocus
End Sub

Or are you trying to do something different?

[dmcgov]

ok so that shows the combobox with both columns of text, but when i pick something off the list, then it only returns the first column in the combobox. see snapshot. how do i fix that?

combobox1.png

[CK76]

That's expected behavior of Combobox. Combobox can only display value of first visible column.

I've experimented with it before. But there really isn't direct way to show both columns.

One method is to use caption or text box above combo box to show both.

Another is to have 3rd column added to range concatenating 1 & 2.
Then setting column width of 1st and 2nd to 0. Hence, showing 3rd column only.

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Dim listArr
Set ws = ThisWorkbook.Sheets("Sheet1")
listArr = ws.Range("Hours").Resize(, 3)
    With Me.ComboBox1
        .ColumnCount = 3
        .ColumnWidths = "0;0;50"
        .List = listArr
    End With
End Sub

Or use _Change event to change ColumnWidth.

Private Sub ComboBox1_Change()
With Me.ComboBox1
    .ColumnWidths = "0;0;50"
End With
End Sub

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Dim listArr
Set ws = ThisWorkbook.Sheets("Sheet1")
listArr = ws.Range("Hours").Resize(, 3)
    With Me.ComboBox1
        .ColumnCount = 3
        .ColumnWidths = "50;50;0"
        .List = listArr
    End With
End Sub

Or just use listbox instead of Combobox.