Setting ComboBox ControlSource in code

[JimPNicholls]

Excel 2k

Morning!

I've trying to set the control Source for a combobox (On a
userform). I have:

Private Sub UserForm_Initialize()
Me.ComboBox1.ControlSource = Range("C1:C11").Value
End Sub

This isn't working (Type mismatch). Can anyone help?

 

[Rob Bovey]

Hi Jim,

I think you've got the purpose of the ControlSource property
misconstrued (easy to do given it's ambiguous name). The ControlSource is
the address of a single cell on a worksheet where you want the value
selected in the ComboBox to be placed.

The RowSource property is the one you want to use to load a list from a
worksheet. The syntax for this property would look something like this:

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1!C1:C11"
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Bob Phillips]

Jim,

It is a string property so you should use Address not Value.

But, it can also only return a value to one cell, not many.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JimPNicholls]

Rob

Thanks for your promt response. Works a treat!

 

[Richard Buttrey]

Excel 2k

Morning!

I've trying to set the control Source for a combobox (On a
userform). I have:

Private Sub UserForm_Initialize()
Me.ComboBox1.ControlSource = Range("C1:C11").Value
End Sub

This isn't working (Type mismatch). Can anyone help?

From memory, isn't the control source a string?
In addition isn't the control source a reference to a single cell
In which case I guess the correct syntax is

Me.ComboBox1.ControlSource = Range("C1").Address

Since you're specifying C1:C11 are you sure you're not looking for the
Row Source property for the combobox?

in which case the syntax would be:

Me.ComboBox1.RowSource = Range("C1:C11").Address

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________