Disable Macro Button

[Eddie G.]

Is there a way to make a regular macro button (not a form command button) disable when the sheet where is resides is activated? I don't want a paticular button available to users when this sheet is opened. Lets say it is button 174 on sheet 1.

[Nickleby]

You could make it so that a password is required to run the macro.

Put this at the start of the macro :-

If InputBox("Enter password") <> "Eddie G." Then
MsgBox "Wrong password"
Exit Sub
End If

[Ivan F Moala]

Another option is the use the shapes visble
property & the Sheets events to Hide the
button. Obviously YOU will need to have
access to this button, so you an use another
sheet event to show it.
Heres one way how to do it.

In the Sheets Code (Sheet with the Button in it) place this code in:

Private Sub Worksheet_Activate()

ActiveSheet.Shapes("Button 174").Visible = False

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Text = "showmethemoney" Then

    ActiveSheet.Shapes("Button 174").Visible = True

    'Now remove your password

    With Application

        .EnableEvents = False

        Target = ""

        .EnableEvents = True

    End With

End If

End Sub



'

'

'

'

Now when ever the sheet is Activated the Button is Hidden.

To Unhide it Type in the password in ANY cell

Password in this case is "showmethemoney"

Change as required. Note password is deleted

if successful.