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

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

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

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

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

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

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

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