View Full Version : How to hide a Button
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
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.
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
Bob Phillips
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.