Disable Form button

[BMD44]

I have included Button from 'Form controls' in Excel.

Now, I want the button to be disabled when I open the workbook.

I used below syntax, but it doesn't disable the button.

ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False

The same syntax works for Command button

ActiveSheet.Shapes("CommandButton1").ControlFormat.Enabled = False

Can any one please help how can we disable Form Controls - Button.

Thanks in advance

 

[Logit]

.
Your syntax is correct ... it works here.

Paste a UserForm button on the Worksheet and use your syntax :

ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False


An ActiveX button on the sheet would be :

Sheets("Sheet1").CommandButton1.Enabled = False

 

[BMD44]

Hello,

I tried the same. It doesn't work for me. I can see the message displayed, but do not see the disable functionality working.

Sub Button7_Click()
ActiveSheet.Shapes("Button 8").ControlFormat.Enabled = False
MsgBox "This is button7"
End Sub

Sub Button8_Click()
ActiveSheet.Shapes("Button 7").ControlFormat.Enabled = False
MsgBox "This is button8"
End Sub

Please let me know if I am missing anything.

 

[Fluff]

I have found that disabling form control buttons, stopped working some years ago.
How about hiding the button instead?

 

[BMD44]

Thank you for the immediate reply. The requirement I have is to disable the button.

So, I am trying to figure out if we have a work around.

 

[Fluff]

So, I am trying to figure out if we have a work around.

Have a look at post#4

 

[BMD44]

Sorry, I am trying to understand what is post #4

 

[Fluff]

It is the 4th post in the thread & is indicated at the right hand side of each post.

The suggestion I made as a workaround was to hide the button.

 

[Logit]

.
Download example workbook that demonstrates.

Buttons Disable.xlsm

When the buttons are enabled, the MsgBox appears.

When the buttons are disabled, no MsgBox.

 

[Logit]

.
Here is an edit to the existing macro that changes the Button Caption from "Enabled" to "Disabled" .

Sub UserFrmButnNO()
    ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = False
    Worksheets("sheet1").Shapes("Button 1").Select
    Selection.Characters.Text = "Disabled"
    
End Sub

Sub UserFrmButnYES()
    ActiveSheet.Shapes("Button 1").ControlFormat.Enabled = True
    Worksheets("sheet1").Shapes("Button 1").Select
    Selection.Characters.Text = "Enabled"
End Sub