Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controls in a Worksheet | Excel Programming | |||
Controls on worksheet | Excel Programming | |||
controls on worksheet | Excel Programming | |||
Worksheet Controls | Excel Programming | |||
Referencing to controls on a worksheet | Excel Programming |