| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make CommandButtons Not Visable With A Macro
Greetings,
I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
hi
if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
Not sure why you would mix Forms controls with the Toolbox controls. But
anyway the code to address the Toolbox control is:- Sheets("Sheet1").CommandButton1.Enabled = False -- Regards, OssieMac "FSt1" wrote: hi if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
hi
i did test this before posting. works in my xl03 without the sheet reference. regards FSt1 "OssieMac" wrote: Not sure why you would mix Forms controls with the Toolbox controls. But anyway the code to address the Toolbox control is:- Sheets("Sheet1").CommandButton1.Enabled = False -- Regards, OssieMac "FSt1" wrote: hi if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
Sorry FST1. I should have made myself clear that the reply was meant for
Minitman. He refers to Forms ComboBox and Toolbox (which is ActiveX) Command Button with the forms combo code in a module (where it would be) and the command button code in the sheet code and I just wonder why he would mix forms and ActiveX controls. I prefer to go all ActiveX controls. -- Regards, OssieMac "FSt1" wrote: hi i did test this before posting. works in my xl03 without the sheet reference. regards FSt1 "OssieMac" wrote: Not sure why you would mix Forms controls with the Toolbox controls. But anyway the code to address the Toolbox control is:- Sheets("Sheet1").CommandButton1.Enabled = False -- Regards, OssieMac "FSt1" wrote: hi if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
hi
sorry too. you're right. it is odd. i prefer to remain consistant also. form controls for forms, sheet controls for sheets. but some form controls do work with sheets and some are disabled for sheet use. so i just appeared to me that all he needed to do was get all the code in the right placed. after reading his original post, he didn't asked about code so i may have misunderstood too. oh well. he seems to have it fixed now. that is what's important. Regards FSt1 "OssieMac" wrote: Sorry FST1. I should have made myself clear that the reply was meant for Minitman. He refers to Forms ComboBox and Toolbox (which is ActiveX) Command Button with the forms combo code in a module (where it would be) and the command button code in the sheet code and I just wonder why he would mix forms and ActiveX controls. I prefer to go all ActiveX controls. -- Regards, OssieMac "FSt1" wrote: hi i did test this before posting. works in my xl03 without the sheet reference. regards FSt1 "OssieMac" wrote: Not sure why you would mix Forms controls with the Toolbox controls. But anyway the code to address the Toolbox control is:- Sheets("Sheet1").CommandButton1.Enabled = False -- Regards, OssieMac "FSt1" wrote: hi if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
Thanks OssieMac
I only used the Forms ComboBox because I could not figure out how to load the RowSource into the Control ToolBox ComboBox. Thanks again for the syntax. It works great. -Minitman On Sun, 30 Mar 2008 19:13:01 -0700, OssieMac wrote: Not sure why you would mix Forms controls with the Toolbox controls. But anyway the code to address the Toolbox control is:- Sheets("Sheet1").CommandButton1.Enabled = False |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make CommandButtons Not Visable With A Macro
Hey,
Thanks for the reply. Putting the DropDown change event macro into the sheet code area worked like a charm. A bit slow, but it works. Thank you. -Minitman On Sun, 30 Mar 2008 16:54:00 -0700, FSt1 wrote: hi if the controls are on the sheet then both command button code and combobox code need to be in the sheet code of the sheet on which they reside. right click the tab, click view code. paste code there. as to the code to enable/disable the button, try something like this.. Private Sub ComboBox1_Change() If ComboBox1.Value = "aaa" Or _ ComboBox1.Value = "bbb" Then CommandButton1.Enabled = False Else If ComboBox1.Value = "ccc" Or _ ComboBox1.Value = "ddd" Then CommandButton1.Enabled = True End If End If End Sub regards FSt1 "Minitman" wrote: Greetings, I have a Form Toolbox ComboBox on my sheet. I works fine. I also have a CommandButton from the Control ToolBox on this same sheet, it also works fine. It is when I try to get the button to become not enabled when I choose a different item from the ComboBox, that I ran into a wall. The ComboBox's code is in Module1 and I can't get the ComboBox's change event to recognize that there is a Control Toolbox CommandButton that needs to be disabled. The CommandButton code is in the code section of the sheet. I tried to move the CommandButton's code to Module1, but it is not recognized there as being from a sheet. It keeps asking for the "variable" to be defined. The "variable it keeps asking to be defined is not a variable, it is the CommandButton's name. How do I get them to see each other? Any help will be appreciated. -Minitman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I make the autosum refer to visable rows / filter? | Excel Worksheet Functions | |||
| always visable header row | Excel Worksheet Functions | |||
| How to keep 1st column visable when scrolling | Excel Discussion (Misc queries) | |||
| tracer arrows not visable | Excel Worksheet Functions | |||
| Visable Cells | Excel Discussion (Misc queries) | |||
Hybrid Mode
