how to disable/enable command button (ActiveX) using a macro

[enahs_naneek]

I have tried to create a macro using recorder to disable/enable command
button (ActiveX) with the following code being created

ActiveSheet.Shapes("CommandButton1").Select

This only results in the command button being selected - with the procedure
of enabling/disabling being "lost". So i tried the following code

ActiveSheet.CommandButton1.Select
CommandButton1.Enabled = False

This presents runtime error message "object required"

What is the "simplest" way to create this macro? (first time user)

Any help much appreciated

 

[abu abdullah........halsaeed85]

you can try this to hide/unhide specific button like"button1"

Sub hide_specific_button()
ActiveSheet.Shapes("Button 1").Visible = False
End Sub
Sub unhide_specific_button()
ActiveSheet.Shapes("Button 1").Visible = True
End Sub
i hop this helpful

 

[Per Jessen]

Hi

You don't have to select the button to enable/disable it, just use the
object reference as recorded:

If Activesheet.Shapes("CommandButton1").Enabled=true then
Activesheet.Shapes("CommandButton1").Enabled=False
Else
Activesheet.Shapes("CommandButton1").Enabled=True
End If

Hopes this helps.
....
Per

 

[Matt Richardson]

Hi

You don't have to select the  button to enable/disable it, just use the
object reference as recorded:

If Activesheet.Shapes("CommandButton1").Enabled=true then
     Activesheet.Shapes("CommandButton1").Enabled=False
Else
     Activesheet.Shapes("CommandButton1").Enabled=True
End If

Hopes this helps.
...
Per

In fact, that's probably where you got the error from, as if the
CommandButton1 isn't on the active sheet, it will throw an exception..

Matt
http://teachr.blogspot.com

 

[enahs_naneek]

Thank you for your reply
I have entered the code you provided within my "Disable" macro (shown below)
Sub Disable()
'
' Disable Macro
' Macro recorded 05/02/2010 by enahs naneek
'

'
If ActiveSheet.Shapes("CommandButton1").Enabled = True Then
ActiveSheet.Shapes("CommandButton1").Enabled = False
Else
ActiveSheet.Shapes("CommandButton1").Enabled = True
End If
End Sub

Results in error message "object doesnt support this property or method"

This macro (forming part of a larger macro) will be assigned to a second
command button so that when the full macro process is complete i can either
disable or enable the first command button by editing the code to what is
required (true or false)

e.g. "Add" command button enables or disables "Edit" command button.

Will the code you provided work this way

Much appreciated


______________________________________________________

Hi

You don't have to select the button to enable/disable it, just use the
object reference as recorded:

If Activesheet.Shapes("CommandButton1").Enabled=true then
Activesheet.Shapes("CommandButton1").Enabled=False
Else
Activesheet.Shapes("CommandButton1").Enabled=True
End If

Hopes this helps.
...
Per

I have tried to create a macro using recorder to disable/enable command
button (ActiveX) with the following code being created

[quoted text clipped - 13 lines]

Any help much appreciated

 

[keiji kounoike]

There are some ways to disable commandbutton.
One way is

Sub Disable()
ActiveSheet.OLEObjects("CommandButton1").Enabled = False
End Sub

Keiji

Thank you for your reply
I have entered the code you provided within my "Disable" macro (shown below)
Sub Disable()
'
' Disable Macro
' Macro recorded 05/02/2010 by enahs naneek
'

'
If ActiveSheet.Shapes("CommandButton1").Enabled = True Then
ActiveSheet.Shapes("CommandButton1").Enabled = False
Else
ActiveSheet.Shapes("CommandButton1").Enabled = True
End If
End Sub

Results in error message "object doesnt support this property or method"

This macro (forming part of a larger macro) will be assigned to a second
command button so that when the full macro process is complete i can either
disable or enable the first command button by editing the code to what is
required (true or false)

e.g. "Add" command button enables or disables "Edit" command button.

Will the code you provided work this way

Much appreciated


______________________________________________________

Hi

You don't have to select the button to enable/disable it, just use the
object reference as recorded:

If Activesheet.Shapes("CommandButton1").Enabled=true then
Activesheet.Shapes("CommandButton1").Enabled=False
Else
Activesheet.Shapes("CommandButton1").Enabled=True
End If

Hopes this helps.
...
Per

I have tried to create a macro using recorder to disable/enable command
button (ActiveX) with the following code being created

[quoted text clipped - 13 lines]

Any help much appreciated

 

[enahs_naneek]

Thank you keiji - it worked perfectly
_______________________________

There are some ways to disable commandbutton.
One way is

Sub Disable()
ActiveSheet.OLEObjects("CommandButton1").Enabled = False
End Sub

Keiji

Thank you for your reply
I have entered the code you provided within my "Disable" macro (shown below)

[quoted text clipped - 46 lines]

[quoted text clipped - 13 lines]
Any help much appreciated

 

[keiji kounoike]

You're welcome.

Keiji

Thank you keiji - it worked perfectly
_______________________________

There are some ways to disable commandbutton.
One way is

Sub Disable()
ActiveSheet.OLEObjects("CommandButton1").Enabled = False
End Sub

Keiji

Thank you for your reply
I have entered the code you provided within my "Disable" macro (shown below)

[quoted text clipped - 46 lines]

[quoted text clipped - 13 lines]
Any help much appreciated