Combobox

[olatzcelaya]

Hi all,

I am triying to create a combobox in excel but when i run the VB editor it gives me the following error:

error 424
Object required

I do not why because i have assigned the following macro to the combobox button of excel (here i attach the code):

Sub ComboBox1_Click()
With ComboBox1
.AddItem "YES"
.AddItem "NO"

End With
End Sub

It appears the line of the "YES" highlighted. Could anybody help me with this matter? Any help will be very appreciated.

Thank you,

Olatz

 

[xlbo]

try this - don;t think you need to reference the control name as you are in teh command procedure for it:

Sub ComboBox1_Click()
With Me
.AddItem "YES"
.AddItem "NO"
End With
End Sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MakeItSo]

Not sure if the "Click" event is going to fire anyway for a combobox.
Sure you want to fill the combobox there? Not earlier?
Perhaps in the "auto_open" event of the respective workbook?

Works fine for me if I add a module with this code:

Sub auto_open()
With Sheets(1).ComboBox1
    .AddItem "yadda"
    .AddItem "yaddayadda"
End With
End Sub

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[olatzcelaya]

hi Geoff,

I have just tried at it does not work, it appears:

Invalid use of Me keyword


Regards,

Olatz

 

[MakeItSo]

P.S: sure you have the Fill code in the right place? (correct worksheet or module?)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[HarleyQuinn]

MekeItSo is right, the click event shouldn't fire unless there is data in the combobox to select.

Plus, you realise that if you do have it on the click event and the user makes more than one selection (without your code clearing the combobox) that it will add a Yes and a No everytime?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[WalkerEvans]

Try splitting this into two subs; I believe it might give you a cleaner result. The following may give you a starting point:

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "YES"  'ListIndex = 0
    ComboBox1.AddItem "NO"   'ListIndex = 1
End Sub

Private Sub ComboBox1_Click()
    Select Case ComboBox1.Value
      Case 0
      'Whatever you need done if YES selected
      Case 1
      'Whatever you need done if NO selected
    End Select
End Sub

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long