CODEWITHSUNDEEP
excelvba
Ashutosh Rai
Ashutosh Rai

Reputation: 125

Regarding Disabling a Button on Excel using VBA

I have a button on my sheet named PROCESS_FLOW.When I click on that button it displays one sheet and hides other sheets.


Sub CREATE_CHARACTERISTICS()
Dim b1 As Button
Set b1 = Sheets("PROCESS_FLOW").Buttons("Button 2")

' unhide the sheet and hide all other sheets
    Sheets("INFOOBJECT_CREATE").Visible = True
    Sheets("CREATE_KEYFIGURES").Visible = False
    Sheets("DTP_CREATE").Visible = False
   ' Goto the "Create CHAR" sheet
    Sheets("INFOOBJECT_CREATE").Select
     b1.Enabled = False
End Sub

But Now I want to disable that button on PROCESS_FLOW Sheet so that once we go to Sheet1 we cannot go to that sheet once again.But I am facing problem in finding reference to that Button in VBA code so that i can disable it.Above is the code how i tried to do it but it doesnt work.

Upvotes: 0

Views: 1514

Answers (3)

RetiredGeek
RetiredGeek

Reputation: 3158

Ashutosh,

This macro will list the names of the shapes (buttons) on the active sheet.

Sub ShapeNames()

  Dim MyShape As Shape

  For Each MyShape In ActiveSheet.Shapes
    Debug.Print MyShape.Name
  Next MyShape

End Sub

Output:

Rounded Rectangle 1
CommandButton1

You can then disable it via:

Sub DisableButton()

   'If you inserted a shape from the Insert Tab and added a macro

   Sheets("ButtonSheet").Shapes("Rounded Rectangle 1").Visible = True

End Sub

Sub DisableButton2()

  'If you inserted a button from the Developer Tab

  Sheets("ButtonSheet").Shapes("CommandButton1").Visible = True

End Sub

Note: I got a 438 error if I tried to use the Enabled property!

HTH

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

On the sheet you can add two type of controls. Form Controls and ActiveX Control. The one you are using is Form Control. That behaves a bit different than ActiveX Control. You need to access the ControlFormat for the underlying shape to manipulate its properties.

Note that even setting the Enabled to False for the Form Control button doesn't prevent it from running the assigned macro. After all its just shape. You need to remove the OnAction property to prevent it from running the macro on click.

See the code. Refer code comments for explanation.

Option Explicit

Sub DisableButtons()

    '/ For Form Control Button
    Sheet1.Shapes("Button 1").ControlFormat.Enabled = False
    '~~>This still allows button click and runs the macro assigned.
    '~~> To remove that you need to remove the OnAction
    Sheet1.Buttons("Button 1").OnAction = ""

    '/ For Activex Control Button
    Sheet1.CommandButton1.Enabled = False

End Sub

Sub EnableButtons()

    '/ For Form Control Button
    Sheet1.Shapes("Button 1").ControlFormat.Enabled = True
    '~~>This still doesn't runs the macro assigned.
    '~~>To enable you need to assign OnAction
    Sheet1.Buttons("Button 1").OnAction = "Module1.button1_click"

    '/ For Activex Control Button
    Sheet1.CommandButton1.Enabled = True

End Sub


Sub button1_click()
    MsgBox "Button 1 clicked."
End Sub

Upvotes: 2

Edward Ziemiński
Edward Ziemiński

Reputation: 26

Each sheet/worksheet has two references. Please take a look at the screenshot below. One reference is visible before the bracket (the yellow one), and the second one is in the bracket (the green one). You need to use the yellow one corresponding to the sheet on which you have the button.

Modules list in VBA window

Sheet1.ButtonName.Enabled = False

Upvotes: 1

Related Questions