Using Controls on a Worksheet
 

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

Using Controls on a Worksheet
 

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
HIS


"lurker111" wrote in message
...
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

Using Controls on a Worksheet
 

"lurker111" wrote in message
...
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-

Using Controls on a Worksheet
 

"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).

Using Controls on a Worksheet
 

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
HIS

Using Controls on a Worksheet
 

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
HIS


"lurker111" wrote in message
...
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
HIS