How to hide a Button

[uaku]

Hi,
I have created a excel and added a button through the forms menu. Now is it possible to hide this macro until certail cell criteria is met.

Lets a Cell "A1" has yes & No option. For yes I want to show the Button which I can assign certain macro and if A1 = No then I don't want the Button to be visible.

Thanks for your help
Aku

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Buttons("Button 1").Visible = Target.Value = "Yes"
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[uaku]

Nihil,
Thanks for your code, it works great only when I set the values of A1 as yes and no either by typing manually Yes or NO or using a drop down list.

What I am trying to do is get the yes and no value from a vlookup criteria and that thing does not work. I mean it disappears on NO, but does not appear back when the A1 value is Yes

[Aussiebear]

Try the following
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value = “Yes” Then
Me.Buttons("Button 1").Visible = True
Else
Me.Buttons(“Button 1”).Visible = False
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

[/vba]

[Bob Phillips]

[vba]

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "A1"

Me.Buttons("Button 1").Visible = Me.Range(WS_RANGE).Value = "Yes"

ws_exit:
Application.EnableEvents = True
End Sub[/vba]