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

 

[Fluff]

@Logit
Button 1 always shows the msgbox for me.
It is a known problem, although I'm not sure when it started happening.

 

[Logit]

.
It works here.

Perhaps a difference in the Excel Version ?

BMD44 .. what version are you using ?

????

 

[Fluff]

Yes it is down to the version, but not sure when it started.

 

[BMD44]

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

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

Thank you for the clarification provided.

 

[BMD44]

.
It works here.

Perhaps a difference in the Excel Version ?

BMD44 .. what version are you using ?

????

Hi, My Excel version is Microsoft Excel 2016

 

[BMD44]

.
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

Thanks much for the code. I see Button1 always shows msg even if it is disabled/enabled.

 

[Logit]

Sorry the code won't work for your version of Excel. Suggest you try to hide the button as Fluff indicated or perhaps there is a method
of using a SHAPE to look like a button ? Maybe Excel 2016 will let you disable the shape or perform another measure on it.

 

[BMD44]

Sorry the code won't work for your version of Excel. Suggest you try to hide the button as Fluff indicated or perhaps there is a method
of using a SHAPE to look like a button ? Maybe Excel 2016 will let you disable the shape or perform another measure on it.

Thanks much Logit and Fluff for your prompt replies. Sorry to ask more,but want to know if using Command button will have any issue (as we can disable command button)

 

[Logit]

Sometimes, an ActiveX button will cause unintended issues that vary without warning. Just go with it and see how you fare.

 

[BMD44]

Sometimes, an ActiveX button will cause unintended issues that vary without warning. Just go with it and see how you fare.

Thank you for the inputs. Will definitely try it.