Hide a button

[leelee23]

Hi all,

Hopefully this is a simple one, what would be the vba code to hide a button according to the value of a cell?

Thanks in advance,

Lee

[stevebriz]

Here is a way:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" And Target.Value = 1 Then
Sheets(1).CommandButton1.Visible = False

Else
Sheets(1).CommandButton1.Visible = True

End If


End Sub

[leelee23]

Thanks, appreciate that.

Is there a way of writing it instead of the Private Sub so that I can assign it to a button in Excel?

Cheers,

Lee

[stevebriz]

yes..

Sub showCMD()
If Sheets(1).Range("A1").value = 1 Then
Sheets(1).CommandButton1.Visible = False

Else
Sheets(1).CommandButton1.Visible = True

End If
End Sub

[Leith Ross]

Hello Lee & Steve,

Here is way to write the macro so the button name isn't hard code into the macro. This way the macro can be assigned to a Button without knowing its name. This works only with Forms buttons, not Control Toolbox command buttons.

Sub HideButton()

  With ActiveSheet.Buttons(Application.Caller)
    If Range("A1").Value = 1 Then
       .Visible = False
    Else
       .Visble = True
    End If
  End With

End Sub

Sincerely,
Leith Ross

[leelee23]

Thank you very much!

One silly questions, if the button was called "Button 42" how is that effected?

I changed "CommandButton1" to "Button42" and "CommandButton42" but it didn't work.. any idea?

Cheers once more,

Lee

[stevebriz]

have a look at the properties of the bottom and be sure you are referring to by its (name)....Not its caption.