Populate combo box with a range

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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
 
Private Sub UserForm_Initialize()
With ComboBox1
.RowSource = "Sheet2!B5:B12"
End With
End Sub
 
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
 
Set r = Range("A5:A" & lr)



Chris said:
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
Click to expand...
 
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.
 
Back
Top