Object required error setting combo box row source

[tigersden]

Hi All,

Can someone please point in the right direction with this object required error.
The line of code that produces it is:
cboPPoint1.RowSource = Worksheets("Sheet2").Range("I4").Value


Thanks in advance

 

[Norie]

Where are you running this code?

What type of combobox are you using?

Have you checked it's name?

 

[tigersden]

Hi Norie,

The code is run from a module triggered from another combo boxes chage event, the combo box is on a worksheet & from the controls toolbar.
I have checked its name in the properties window & it is correct.

Really stumped by this

 

[Norie]

Can you post all the code and indicate where it is located?

I'm thinking that the combobox is outwith the scope of the module.

Why don't you just put this code in the change event for the other combobox?

By the way like I said in the other thread RowSource is for comboboxes on userforms, you need ListFillRange for one from the Control Toolbox.

 

[tigersden]

Hi Norie,
The code is in module 3.
The complete code listing is shown below, I have stripped out a lot of the previous stuff as it was not needed. I have also altered the rowsource property to ListFillRange as you suggested.

Sub DropDown22_Change()
cboPPoint1.ListFillRange = Worksheets("Sheet2").Range("I4").Value
End Sub


Thanks in advance.

 

[Norie]

Why do you have this in a standard module?

It should be in the worksheet module of the worksheet that DropDown22 and cboPPoint1 are on.

The easiest way to find where to put code for controls is to double click on the worksheet when you are in design mode.

 

[tigersden]

Hi Norie,
Thanks for taking the time to have a look.
I have moved the code onto the worksheet module. Everything is working fine now, there was one other problem I had not named the sheet for the range that the vlookup function returned, eg A1:A5 should have been Sheet2!A1:A5.
Many thanks for all your help

 

[Norie]

Glad it's working.

You should only need the sheet name if the range is on a different sheet to the code.

 

[tigersden]

Hi Norie,

Thanks for all your help one lat thing is there anyway to clear the second comb box prior to filling it, so nothing is shown as selected?

 

[Norie]

Try either setting it's ListIndex to -1 or it's Value to "".