PDA

View Full Version : How to hide a Button



uaku
05-01-2011, 11:07 AM
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

xld
05-01-2011, 11:12 AM
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


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
05-01-2011, 01:17 PM
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
05-01-2011, 03:08 PM
Try the following

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

xld
05-01-2011, 03:31 PM
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