Using Controls on a Worksheet

[lurker111]

I have used the "Userforms" and did many great things with controls to get
data from a user.
Now I want to create a "form" by placing the controls directly on a
worksheet. The problem I am having is determing how to access the controls.
I can't figure out the object to reference.

For instance I have a combobox named PMcombo
I have tried:
activeworksheet.PMcombo
activeworksheet.forms.PMcombo
activeworksheet.controls.PMcombo
....(add about 75 other combinations of various objects)
I've tried creating an object to use the controls.findcontrol (type:=
msocontrolcombobox)...and tried it with a variant.

I have searched the help files...and now I have ended up here unable to
figure out such an easy little thing which prevents me from doing anything at
all.

I plan to use the workbook_open method to populate the combobox from a list
of data on the worksheet. I considered using a "form combobox" but unlike
the control combobox, it seems you cannot type your own value into it.

Thanks

[Nick Hodge]

Use ActiveSheet of explicitly address a worksheet like

Sub addressComboBoxOnSheet()
Worksheets("Sheet1").ComboBox1.Visible = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"lurker111" <[email protected]> wrote in message
news:[email protected]...
>I have used the "Userforms" and did many great things with controls to get
> data from a user.
> Now I want to create a "form" by placing the controls directly on a
> worksheet. The problem I am having is determing how to access the
> controls.
> I can't figure out the object to reference.
>
> For instance I have a combobox named PMcombo
> I have tried:
> activeworksheet.PMcombo
> activeworksheet.forms.PMcombo
> activeworksheet.controls.PMcombo
> ...(add about 75 other combinations of various objects)
> I've tried creating an object to use the controls.findcontrol (type:=
> msocontrolcombobox)...and tried it with a variant.
>
> I have searched the help files...and now I have ended up here unable to
> figure out such an easy little thing which prevents me from doing anything
> at
> all.
>
> I plan to use the workbook_open method to populate the combobox from a
> list
> of data on the worksheet. I considered using a "form combobox" but unlike
> the control combobox, it seems you cannot type your own value into it.
>
> Thanks

[42N83W]

"lurker111" <[email protected]> wrote in message
news:[email protected]...
>I have used the "Userforms" and did many great things with controls to get
> data from a user.
> Now I want to create a "form" by placing the controls directly on a
> worksheet. The problem I am having is determing how to access the
> controls.
> I can't figure out the object to reference.
>
> For instance I have a combobox named PMcombo
> I have tried:
> activeworksheet.PMcombo
> activeworksheet.forms.PMcombo
> activeworksheet.controls.PMcombo
> ...(add about 75 other combinations of various objects)
> I've tried creating an object to use the controls.findcontrol (type:=
> msocontrolcombobox)...and tried it with a variant.
>
> I have searched the help files...and now I have ended up here unable to
> figure out such an easy little thing which prevents me from doing anything
> at
> all.
>
> I plan to use the workbook_open method to populate the combobox from a
> list
> of data on the worksheet. I considered using a "form combobox" but unlike
> the control combobox, it seems you cannot type your own value into it.
>
> Thanks

You may be aware of this, maybe not, but...when placing controls on a
worksheet, the code that handles these controls is placed in the code module
for that worksheet, not a generic code module. In design Mode, place a
control (combobox) on the worksheet, right click on it and choose View Code.
You'll see that you're taken to the code module for that worksheet if you
look over in your Project Explorer window.

I'm thinking that might have something to do with it.

-gk-

[lurker111]

"42N83W" wrote:

> You may be aware of this, maybe not, but...when placing controls on a
> worksheet, the code that handles these controls is placed in the code module
> for that worksheet, not a generic code module. In design Mode, place a
> control (combobox) on the worksheet, right click on it and choose View Code.
> You'll see that you're taken to the code module for that worksheet if you
> look over in your Project Explorer window.
>
> I'm thinking that might have something to do with it.
>
> -gk-

Well, I am aware of that and it looks to be done in the same way as a
"Userform" code window. The only thing is, in the standard Worksheet
programming window or a Module programming window I can always reference:

Userform1.PMcombo

Whenever I want to do something to that combobox. I have not tried to do
the "Workbook_open" event from inside that form programming window as I
figured it would have to be done in the worksheet/workbook programming window
(workbook is not available on the pull down menu of the programming window
that actually has the access to the comboboxes and listboxes I made
*embedded* in the worksheet).

[lurker111]

I have tried that as well.

activeworkbook.worksheets("Sheet1").PMCombo
worksheets("Sheet1").PMCombo
Worksheets(1).PMCombo
dim wksht as worksheet
set wksht = activeworkbook.worksheets("sheet1")
wksht.PMCombo

None of the above works. I am always told that PMCombo is not a declared
variable or is not ...not a member? (I don't remember the exact error message)

I may just have a single button pop up a Userform window. Execute that
window and then close down. Then when the user wants to enter in more data
they just have to keep popping up the window. I want to have it so that they
don't have to keep popping up a window though as it makes entering multiple
sets of data more tedious.

>"Nick Hodge" wrote:
> Use ActiveSheet of explicitly address a worksheet like
>
> Sub addressComboBoxOnSheet()
> Worksheets("Sheet1").ComboBox1.Visible = False
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS

[Nick Hodge]

What property of the combobox are you looking to use, as all your examples
don't show one and Excel may be considering it an undeclared variable as a
consequence. If you are looking to reference it from an object variable,
then you need to declare and set it

This code works *in a standard module* (It doesn't need to be in the
worksheet class module as intimated by another poster)

Sub AddressComboBox()
Dim myCombo As ComboBox
Set myCombo = Worksheets("Sheet1").ComboBox1
With myCombo
.Enabled = True
.List = Array("ListItem1", "ListItem2", "ListItem3")
End With
End Sub

I guess you are using a combobox from the 'Control' toolbox (ActiveX) and
not from the 'Forms' toolbox?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"lurker111" <[email protected]> wrote in message
news:[email protected]...
>I have tried that as well.
>
> activeworkbook.worksheets("Sheet1").PMCombo
> worksheets("Sheet1").PMCombo
> Worksheets(1).PMCombo
> dim wksht as worksheet
> set wksht = activeworkbook.worksheets("sheet1")
> wksht.PMCombo
>
> None of the above works. I am always told that PMCombo is not a declared
> variable or is not ...not a member? (I don't remember the exact error
> message)
>
> I may just have a single button pop up a Userform window. Execute that
> window and then close down. Then when the user wants to enter in more
> data
> they just have to keep popping up the window. I want to have it so that
> they
> don't have to keep popping up a window though as it makes entering
> multiple
> sets of data more tedious.
>
>>"Nick Hodge" wrote:
>> Use ActiveSheet of explicitly address a worksheet like
>>
>> Sub addressComboBoxOnSheet()
>> Worksheets("Sheet1").ComboBox1.Visible = False
>> End Sub
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> www.nickhodge.co.uk
>> [email protected]HIS
>