Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Button
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Button
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 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Button
On Oct 17, 8:53 am, "Crowbar via OfficeKB.com" <u15117@uwe wrote:
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 -- Message posted viahttp://www.officekb.com 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Button
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 ToolsMacroVB Editor? If so which sheet do I modify? Doug "Leith Ross" wrote: On Oct 17, 8:53 am, "Crowbar via OfficeKB.com" <u15117@uwe wrote: 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 -- Message posted viahttp://www.officekb.com 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
Creating a macro which presses a button containing a recorded macro | Excel Programming | |||
Can't select macro button after other button is pressed | Excel Programming | |||
Pause macro, add form button to sheet, continue macro when button clicked! | Excel Programming | |||
How to end macro on inital active worksheet containing macro button that was clicked | Excel Programming |