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


"Chris" wrote:

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

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

"Chris" wrote in message
...
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

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" wrote:


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

"Chris" wrote in message
...
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

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



"Chris" wrote in message
...
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" wrote:


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

"Chris" wrote in message
...
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

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.





"Chris" wrote in message
...
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) ''' THIS IS INCORRECT '''
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" wrote:


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

"Chris" wrote in message
...
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