Issue with ComboBox on a UserForm

[ryguy7272]

A suggestion was made to post my question to the Programming DG after I
didn’t get many responses in the Worksheets Functions DG (originally posted
in the General Questions DG) so here it goes.

Basically, I am encountering some bizarre behavior from an Excel model. I
have a ComboBox on a UserForm and it works fine if this model is the only one
open at the time I open the model. If there is any other Excel SS open and
running, before I open this particular model with the ComboBox on the
UserForm, the ComboBox will not display any data in the drop down menu. As
soon as I close any open SS, and open the model again, the ComboBox works
just fine! I’ve been using Excel for 10 years or so...not exactly new to the
scene but I’ve never dealt with this... What could possible cause this
behavior? Why would a UserForm not be able to load a ComboBox if other SS are
open?


There really is not any code for the ComboBox. Under Properties I have the
ComboBox set to this RowSource reference: =WELCOME!AG42:AG92

I could envision using some kind of coed like this:
Application.Workbooks("MyBook.xls").Worksheets("Sheet1").Cells(1,1).ComboBox1
However, I thought it was unnecessary. Is this required for Excel to keep
track of a certain ComboBox is referencing a certain SS? I wouldn't even
know how to implement this code, which was given to me by a person who read
my post in the Worksheets Functions DG.



Regards,
Ryan--

 

[Jim Cone]

Ryan-
Try...

[NameOfWorkbook]WELCOME!AG42:AG92
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ryguy7272"
wrote in message
A suggestion was made to post my question to the Programming DG after I
didn’t get many responses in the Worksheets Functions DG (originally posted
in the General Questions DG) so here it goes.

Basically, I am encountering some bizarre behavior from an Excel model. I
have a ComboBox on a UserForm and it works fine if this model is the only one
open at the time I open the model. If there is any other Excel SS open and
running, before I open this particular model with the ComboBox on the
UserForm, the ComboBox will not display any data in the drop down menu. As
soon as I close any open SS, and open the model again, the ComboBox works
just fine! I’ve been using Excel for 10 years or so...not exactly new to the
scene but I’ve never dealt with this... What could possible cause this
behavior? Why would a UserForm not be able to load a ComboBox if other SS are
open?

There really is not any code for the ComboBox. Under Properties I have the
ComboBox set to this RowSource reference: =WELCOME!AG42:AG92

I could envision using some kind of coed like this:
Application.Workbooks("MyBook.xls").Worksheets("Sheet1").Cells(1,1).ComboBox1
However, I thought it was unnecessary. Is this required for Excel to keep
track of a certain ComboBox is referencing a certain SS? I wouldn't even
know how to implement this code, which was given to me by a person who read
my post in the Worksheets Functions DG.
Regards,
Ryan--

 

[ryguy7272]

Thanks for the look Jim, but still no-go here. It looks reasonable, but when
I tried it I got ‘Could not set RowSource property. Invalid Property
Value.’. Any more ideas out there?

Regards,
Ryan--

 

[Jim Cone]

RyGuy,
Looks like what I posted only works if the source range workbook is an external workbook..

Vba code like this works for me...

UserForm1.ComboBox1.RowSource =
ThisWorkbook.Worksheets("Welcome").Range("AG42:AG92").Address(External:=True)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ryguy7272"
wrote in message
Thanks for the look Jim, but still no-go here. It looks reasonable, but when
I tried it I got ‘Could not set RowSource property. Invalid Property
Value.’. Any more ideas out there?
Regards,
Ryan--

 

[ryguy7272]

That's it!!
I am humbled.

Regards,
Ryan---

--
RyGuy

RyGuy,
Looks like what I posted only works if the source range workbook is an external workbook..

Vba code like this works for me...

UserForm1.ComboBox1.RowSource =
ThisWorkbook.Worksheets("Welcome").Range("AG42:AG92").Address(External:=True)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ryguy7272"
wrote in message
Thanks for the look Jim, but still no-go here. It looks reasonable, but when
I tried it I got ‘Could not set RowSource property. Invalid Property
Value.’. Any more ideas out there?
Regards,
Ryan--
--
RyGuy

Ryan-
Try...

[NameOfWorkbook]WELCOME!AG42:AG92
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)