Could not set the RowSource property. Invalid property value

[Denny57]

I have tried to add a range into a combobox both by using VBA code and control properties but cannot get the details to be accepted.

When I add a range (eg Sheet1!M3:M11) in the control properties (Row Source) I get the abovre error message

When I try to add the code using VBA in the initialize macro, the User Form will not open and an error is reported at the module level, which from experience means that there is a generic error in the code. As soon as I remove the code, the user form opens.

Sample Codes which stop the user from from opening

Dim listdetails as Variant
'listdetails = Range("Sheet2!M3:M11")
'cbo1.List = listdetails
ALSO
Dim listdetails as Variant
'listdetails = Range("Sheet2!M3:M11")
'cbo1.RowSource= listdetails

Any help would be appreciated

 

[Micron]

Must be some other aspect of your code that's not gelling with what you posted. I had no sheet/range/combobox name like that but this modified code worked no problem. You should probably post all of the event. Please use code tags (vba button on posting toolbar) to maintain indentation and readability if you post code.

Private Sub UserForm_Initialize()
Dim listdetails As Variant
listdetails = Range("Sheet2!I4:I10")
ComboBox1.List = listdetails
End Sub

 

[Denny57]

Must be some other aspect of your code that's not gelling with what you posted. I had no sheet/range/combobox name like that but this modified code worked no problem. You should probably post all of the event. Please use code tags (vba button on posting toolbar) to maintain indentation and readability if you post code.

Private Sub UserForm_Initialize()
Dim listdetails As Variant
listdetails = Range("Sheet2!I4:I10")
ComboBox1.List = listdetails
End Sub

Micron

Thanks for your help with this but apart from a couple of extraneous apostrophes in my examples, there is no difference in the code.

As I have indicated. if I "hide" the ComboBox code then the user form works fine.

I cannot understand why the control properties will not accept the correctly formatted source.

 

[Micron]

As soon as I remove the code, the user form opens.

That indicates the problem is in your code. Not sure why you think one or two "extraneous" apostrophes don't seem to matter.

I cannot understand why the control properties will not accept the correctly formatted source.

Then maybe it's not correct? What are you putting there?

 

[RoryA]

Rowsource needs an address string, not a range object, so:

listdetails = "Sheet2!M3:M11"
cbo1.RowSource= listdetails

 

[Micron]

But the list property will accept a range object, which didn't work for OP but worked for me?

 

[RoryA]

which didn't work for OP but worked for me

OP stated there was no difference between your code and his other than apostrophes, so I am assuming he didn't actually try the List property.

 

[Micron]

Dim listdetails as Variant
'listdetails = Range("Sheet2!M3:M11")
'cbo1.List = listdetails

I have to assume that it was tried then commented out because it didn't work.

 

[RoryA]

Ah, I didn’t spot that in the original post!

 

[Denny57]

Hi

What is frustrating me is that I have used the same syntax in multiple ComboBoxes in multiple files and have double-checked the way that I have populated the Row Source and Control source fields in ComboBox properties in these files.

It is almost as if Office 365 has made a change which prevents this property from being populated, regardless of via the properties or via VBA code. It would appear that my only option will be to replace Comboboxes with Textboxes.

Just to confirm that the source formats are the same in all files, which is what does not make any sense.