rowsource field in properties

[glennjrobertson1980]

Hi,

Hope somebody can help me. I am using Excel 2007.

I am creating a userform, the only object on this userform is a combo box. When i click on the combo box in the properties within VBA I have RowSource i am completing this as I normally would with - 'sheet1'!B11:B16
when i click on the drop down for the combo box it shows the source data as it should.

I then have a macro on my spreadsheet that when clicked shows the userform.

However when the form loads there is no information in the combo box and when I go back into VBA and click on the combo box and look in properties the RowSource field has totally disappeared...

Please can somebody help.....

 

[Norie]

What happens if you set the RowSource in the userform Initlalize event?

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "'Sheet1'!B11:B16"
End Sub

Or use List instead.

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = ""
    ComboBox1.List = Sheets("Sheet1").Range("B11:B16").Value
End Sub

 

[glennjrobertson1980]

Thanks for your reply.

I have tried the above but under both option is get a run time error 424 object required

 

[Norie]

Did you change ComboBox1 to the name of your combobox?

 

[glennjrobertson1980]

I havent changed the name so it is called ComboBox1

 

[Norie]

Where did you put the code?

It belongs in the userform module and will be triggered when the userform is loaded.

 

[glennjrobertson1980]

This is where I am putting code is but I get a message saying.

Could not set the rowsource property. Member not found

 

[Norie]

If I create a userform, add a combobox, add the code I posted in the userform module I can't replicate any of the errors you describe.

Could you upload a sample file to a file sharing site like Box.net?

 

[glennjrobertson1980]

When I do this in Excel 2003 it works fine, its only when I do it in Excel 2007 is when I have the issue.

Also can't explain why the rowsource property box is actually disappearing form the properties menu with VBA

 

[Norie]

If you goto Tools>References... is anything marked as missing?

Does the code work if you try if you start over in a blank workbook? ie open a new workbook, add a userform, combobox etc