Macro Button

[Guest]

I have a macro assigned to a button. I want the button to disappear if a
condition exists. For example if some cell value = "Y" I want the button to
not be present. If the cell value = "N" (or not "Y") I want the button to be
active. Does anyone have an idea on how to do that?

 

[Crowbar via OfficeKB.com]

You can use

CommandButton1.Visible = False



If you don't want this to appear/not appear on loading your form you should
do something like this:

Sub Userform_Initialize

If range("A1").value = "N" then
CommandButton1.Visible = False
end if


End Sub

 

[Leith Ross]

You can use

CommandButton1.Visible = False

If you don't want this to appear/not appear on loading your form you should
do something like this:

Sub Userform_Initialize

If range("A1").value = "N" then
CommandButton1.Visible = False
end if

End Sub

Hello Doug,

If your button is on a Worksheet then you will need a second macro to
check the cell's value and change the button's Visible property. This
second macro will need to run during the Worksheet_Activate() event,
and the Worksheet_Change() event. The code for both types of buttons
is provided. You may need to change the cell address that is checked,
and the button's name (only if it is different, and is a Control
Toolbox type).

'Second macro code. Place this in a Standard VBA Module

' Use this code if the Button is from the FORMS Toolbar
Public Sub HideUnhideButton()

With ActiveSheet
If .Range("A1") = "Y" Then
.DropDowns(Application.Caller).Visible = True
Else
.DropDowns(Application.Caller).Visible = False
End If
End With

End Sub

' Use this code if the Button is from the CONTROL TOOLBOX
Public Sub HideUnhideButton()

Dim Btn As MSForms.CommandButton

With ActiveSheet
Set Btn = .OLEObjects("CommandButton1").Object 'Change
the name of the Command button if needed
If .Range("A1") = "Y" Then
Btn.Visible = True
Else
Btn.Vsible = False
End If
End With

End Sub

'Event Code Procedures

Private Sub Worksheet_Change(BYVal Target As Range)

If Target.Address = "$A$1" Then Call HideUnhideButton

End Sub

Private Sub Worksheet_Activate()

Call HidenHideButton

End Sub

Sincerely,
Leith Ross

 

[Guest]

Dear Leith: Thank you so much for your response. The button is from the Forms
toolbar and needs to reference three cells (A1,B1,B2). If either of the cells
has a value = "Y" then the button would disappear. How would I imbed an or
statement within the VBA code? Also, you said modify a standard module...Do I
get there by selecting Tools>Macro>VB Editor? If so which sheet do I modify?

Doug