Combobox

[scottnshelly]

I want to put a Combobox directly on the worksheet. I have searched th
web high and low for a code to put my list in this Combobox without
userform and can't find one. Is it possible to make it work without
userform?
Thank

 

[Vasant Nanavati]

Activate the Forms Toolbar or the Control Toolbox, click the combo box icon,
and draw it on the worksheet. What exactly is the difficulty?

 

[Doug Glancy]

You can set the ListFillRange, which is the equivalent of a RowSource in a
userform Combobox

Sub test()
With Sheet1.ComboBox1
.ListFillRange = "A1:A8"
End With
End Sub

If you are using a named range you could use something like:

Sub test()
With Sheet1.ComboBox1
.ListFillRange = "tester"
End With
End Sub

If you want to add items in your code something like this would work:

Sub test()
Dim i As Integer
With Sheet1.ComboBox1
For i = 1 To 10
.AddItem i
Next i
End With
End Sub

You can't combine the ListFillRange and the Additem methods. Use one or the
other.

hth,

Doug

 

[scottnshelly]

Thanks Doug, No Thanks to Vasant
I appreciate your response. I guess i'm even more novice than i
thought. I tried all three options that you've given me and the combo
box is still empty. I know how to make it work on a userform, but i
don't want a userform. any suggestions?

 

[Doug Glancy]

I should have made clear that I was using a combobox from the controls
toolbar, not the forms toolbar. And of course it has to be on Sheet1 and be
named ComboBox1.

If that doesn't help, reply with your code and a full description of what
your trying to do.

Doug

 

[scottnshelly]

Thanks again for the prompt response. I hate to say it, but i'm stil
not getting any results. Here's what i did:
I opened a workbook, added a combo box onto the workbook. i went t
the VBA and pasted the first sub that you told me about earlier. wen
back to the worksheet and put some random stuff in those cells.
clicked the box and nothing. so i went back to the VBA and put tha
code into a module, and put the module on the combo box. nothing.
have done this for all three codes that you told me about.
i appreciate you helping. i'm an idio

 

[Doug Glancy]

You are using a combobox from the controls toolbar, right?
The code should be in a general module that you've inserted or a worksheet
module, not a click event or other event module.
Also make sure that you've exited design mode (the little triangle in the
control toolbar is not highlighted).

Other than that I'd repost the question with as much detail as possible.
There are people on the list who know this stuff better than me and may spot
something.

Doug