initialize combobox

**joe_for_good** · 09-21-2010, 07:27 PM

Hi everyone,
I have created a combobox on a worksheet (Sheet1). I would like to initialize with some values when the excel file opens. I use the following code:

Private Sub Workbook_Open()
Dim i As Integer

Sheet1.ComboBox1.Clear

For i = 1 To 4
Sheet1.ComboBox1.AddItem i
Next i
End Sub

in ThisWorkbook. But when I run the code I am getting error "Method or Data member not found", and ComboBox1 is highlighted. I really have ComboxBox1 in Sheet1.

Please, I need your help
Thanks in advance for your help.

Joe

**shg** · 09-21-2010, 07:43 PM

Welcome to the forum.

ComboBox1 is an ActiveX control (i.e., from the Control Toolbox, not the Forms toolbar?)

Does the worksheet named Sheet1 have the CodeName Sheet1?

**joe_for_good** · 09-21-2010, 08:14 PM

Thanks for your quick reply.
ComboBox1 was created from Forms toolbar (Form Controls). Initially the name was "Drop Down 1" and I rename it to "ComboBox1"
The worksheet named Sheet1 have the CodeName Sheet1.
Thank you.

**shg** · 09-21-2010, 08:16 PM

That syntax is only valid for an ActiveX control.

Why are you using a combo box instead of a data validation drop-down?

**joe_for_good** · 09-21-2010, 08:27 PM

I would like to create Combobox1 only once and setup in such a way that it appears in all cells of column A for exemple.

**shg** · 09-21-2010, 08:56 PM

Forms combo boxes get their source from a range:

    With ActiveSheet.Shapes("Drop Down 1").ControlFormat
        .ListFillRange = "A1:A5"
        .LinkedCell = "B1"
        .DropDownLines = 5
    End With

**joe_for_good** · 09-21-2010, 09:30 PM

I would like to fill the combo box with some values not from a range(like "a","b","c"); those values will be loaded into the combo box when I open the sheet for the first time.
Thanks

**joe_for_good** · 09-21-2010, 09:46 PM

I can initialize the drop down now with that code. Thank you. Now I would like to have the combox box appearing automatically in each cell of column B. Now I put it in B1. any idea?

**royUK** · 09-22-2010, 01:19 AM

I agree with Shg, why not just use Data Validation's List feature?

**shg** · 09-22-2010, 10:00 AM

Now I would like to have the combox box appearing automatically in each cell of column B.

I'll leave that to someone else -- I think it would be an appallingly bad design.

**romperstomper** · 09-22-2010, 10:05 AM

Use Data Validation in column B, set the Allow option to List, and enter:
a,b,c
in the source box.

**shg** · 09-22-2010, 10:12 AM

The scale seems to be tilting definitively, Joe ...

**royUK** · 09-22-2010, 10:14 AM

That's what i thought you were suggesting earlier Shg

**shg** · 09-22-2010, 10:39 AM

It was -- Romper just didn't read the whole thread, as I frequently don't.

**romperstomper** · 09-22-2010, 10:41 AM

I actually did (for once) - it just seemed that your suggestion had been ignored. I should have reiterated that it was your original suggestion but on that point I was being lazy!

**shg** · 09-22-2010, 10:56 AM

I didn't feel slighted, and was happy to have you weigh in.

**royUK** · 09-22-2010, 11:23 AM

Some feedback from the OP would be good!

**joe_for_good** · 09-23-2010, 08:52 AM

Thanks to all of you guys. Definitely, let's close the discussion with Data Validation List.

**royUK** · 09-23-2010, 08:54 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

initialize combobox

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Re: initialize combobox

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions