ControlSource Property with VBA and Excel 1

[hgrays]

I'm having trouble setting the ControlSource property of a combobox on a UserForm. I've created an add-in that includes the UserForm and a "Config" worksheet that I'm using to store some lookups. I'd like to set the controlsource property of the combobox to a range on the Config sheet. I've given the range a name, but when I reference that name in the ControlSource property, I get an error "Invalid property value". Should I be more explicit with my reference (workbook, worksheet, etc.) and if so, what's the syntax? I've tried in vain to RTFM. Any help is appreciated.

 

[hgrays]

Sorry for my confusion, I meant RowSource...uughh

 

[rmikesmith]

hgrays,

Are you sure it's the ControlSource property you want to assign. This must be assigned to a single cell only (Range names will work). If you are trying to populate the combo with a list on a worksheet, you want to assign a range to the RowSource property, which will also accept a range name.


HTH
Mike

 

[rmikesmith]

hgrays,

We must have posted at roughly the same time! Does this mean everything's OK or that you are still having trouble assigning a range name to the RowSource propery?

Mike

 

[hgrays]

Thanks for the reply Mike... Yes, I'm still having problems. Still getting "Invalid Property Value" when assigning the RowSource. The funny thing is, I can assign a named range to the RowSource property as long as that named range is in the active worksheet. If I try and assign a named range from the worksheet in the Add-In workbook, I get the error. I'm assuming then that the RowSource can not be set from a worksheet in an Add-In, only an open workbook. I did find a KB document on the MS site that talked about RowSource ranges failing when the sheet was hidden. I guess this is perhaps the same deal. I've tried using the workbook and sheet names but it still didn't work.

Thanks Again

 

[rmikesmith]

hgrays,

I've played with this awhile and I have to concur that the RowSource approach doesn't seem to work with an Add-In. In my case, I received no errors but the combo box was empty. I suggest you fill the combo at run-time. Here is some example code, which you would enter into the Userform's Initialize event handler:

Private Sub UserForm_Initialize()
Dim i As Long

  With Me.ComboBox1
    For i = 2 To 11
      .AddItem ThisWorkbook.Worksheets("Sheet1").Cells(i, 1).Value
    Next i
  End With
  
End Sub

HTH
Mike

 

[hgrays]

Thanks for looking at this Mike. I appreciate your input. I'll give it a try. Looks like a good work-around.

Thanks again,

Mark (hgrays)