Populate combo box with a range

[Chris]

Hi All,

I've built a form in VB and need to populate a combo box with a range from
another sheet in the workbook when I initialize it. I cannot for the life of
me get the syntax correct.

Any suggestions?

Thanks

 

[FSt1]

hi
here is code from one of my play forms using the add item method.
Private Sub UserForm_Initialize()
TextBox1 = Format(Second(Time), "00")
Me.CommandButton2.ControlTipText = "Click to continue."
With Combo1
.AddItem "John"
.AddItem "Hank"
End With
Combo1.Value = ""
End Sub
and from another play form using the row sorce method. the row source method
for list box and combo box is the same.
Private Sub UserForm_Initialize()
TextBox2.Value = Date
Dim lr As Long
Dim r As Range

lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A2:A" & lr)
Me.ListBox1.RowSource = "Sheet2!" & r.Address
CommandButton1.SetFocus
End Sub

regards
FSt1

 

[Patrick Molloy]

Private Sub UserForm_Initialize()
With ComboBox1
.RowSource = "Sheet2!B5:B12"
End With
End Sub

 

[Chris]

Thanks Guys,

I'm getting runtime errors on both of these...

for FSt1 I tried;
Dim lr As Long
Dim r As Range

lr = Sheets("Sheet1").Cells(5, "A").End(xlUp).Row
Set r = Range("A5:A20" & lr)
Me.cboAudititem.RowSource = "Sheet1!" & r.Address
cmdAdd.SetFocus

I'm using the following Sheets;
Sheet1 (Audit Log)
Sheet2 (Follow Up Actions)
And the range i am looking to populate the Combo with is; A5:A20

I'm a bit lost now to be honest!

Thanks

 

[Patrick Molloy]

Set r = Range("A5:A" & lr)

Thanks Guys,

I'm getting runtime errors on both of these...

for FSt1 I tried;
Dim lr As Long
Dim r As Range

lr = Sheets("Sheet1").Cells(5, "A").End(xlUp).Row
Set r = Range("A5:A20" & lr)
Me.cboAudititem.RowSource = "Sheet1!" & r.Address
cmdAdd.SetFocus

I'm using the following Sheets;
Sheet1 (Audit Log)
Sheet2 (Follow Up Actions)
And the range i am looking to populate the Combo with is; A5:A20

I'm a bit lost now to be honest!

Thanks

 

[Patrick Molloy]

if the range is ALWAYS A5:A20 then set it directly in the combobox rather
than using code.

by way of explanation

lr = Sheets("Sheet1").Cells(5, "A").End(xlUp).Row

the last row (lr) is found by going to the first cell (A5) then doing
END/DOWN to select the last cell, and .ROW returns the row number

with
Set r = Range("A5:A" & lr)

assume 20 was the last row, so lr has the value 20
"A5:A" & lr results in "A5:A" & "20" --> A5:A20
so r is set to the range "A5:A20"

the extra 20 in the original code (see below) is an error.