How to make a Command Button visible/invisible

[txfrazier]

Hello,

I have a command button on an excel sheet that I want to make visible / invisible based on the results of a formula or value of a cell. How would I do this in VBA?

Thanks!

 

[Mike H..]

Hello,

I have a command button on an excel sheet that I want to make visible / invisible based on the results of a formula or value of a cell. How would I do this in VBA?

Thanks!

Hi,

The worksheet calculate event should catch that for you. Right click the sheet tab, view code and depending on which button you used then paste the code in on the right.

Private Sub Worksheet_Calculate()
'ActiveX button
If Range("A1").Value = 1 Then
  Sheets("Sheet6").CommandButton1.Visible = False
Else
  Sheets("Sheet6").CommandButton1.Visible = True
End If
End Sub



Private Sub Worksheet_Calculate()
'Forms Button
If Range("A1").Value = 1 Then
  Sheets("Sheet6").Shapes("Button 4").Visible = msoFalse
Else
  Sheets("Sheet6").Shapes("Button 4").Visible = msoTrue
End If
End Sub

 

[txfrazier]

Thanks it works perfectly . . . but only when I manually run the code. How do I get it to run when the user goes to that sheet?

Thanks again. Almost there.

 

[NARAYANK991]

Hi Frazier ,

If you want the above code to run when ever the user goes to a sheet , there are two event procedures for Worksheet_Activate and Worksheet_Deactivate.

Thus , something like the following should do :

Private Sub Worksheet_Activate()
'           ActiveX button
             If Range("A1").Value = 1 Then
               Sheets("Sheet6").CommandButton1.Visible = False
            Else
               Sheets("Sheet6").CommandButton1.Visible = True
            End If
End Sub

Private Sub Worksheet_Activate()
'           Forms Button
             If Range("A1").Value = 1 Then
               Sheets("Sheet6").Shapes("Button 4").Visible = msoFalse
            Else
               Sheets("Sheet6").Shapes("Button 4").Visible = msoTrue
            End If
End Sub

Narayan

 

[txfrazier]

Narayan,

I've tried this:

Private Sub Worksheet_Activate()
'ActiveX button
If Range("e13").Value = 0 Then
Sheets("RA NE").ViewRA_NE_GT_2_Days.Visible = False
Else
Sheets("RA NE").ViewRA_NE_GT_2_Days.Visible = True
End If
End Sub

However, when I go to the "RA NE" sheet where the value of "e13" = 0, I still see the ActiveX button. However, when I run the code in the VBA editor, the button becomes invisible. Is there a setting under the properties of the button or the sheet that I need to change?

Thanks for all of your help.

 

[NARAYANK991]

Hi Frazier ,

See this file.

Narayan

 

[txfrazier]

Narayan,

Thanks. The file you sent works exactly as I expected. The button becomes visible/invisible as soon as I change the value in e13. However, once I open my file, your file no longer works. Even when I close my fiel, yours still doesn't work. I have to completely exit excel and re-open your file to get it to work as expected.

I'm using excel 2013 on Window7.

I'm assuming there must be something in the vba on my spreadsheet that is affecting how the code functions. I'll start digging but if anyone has any ideas, please let me know.

Thomas

 

[txfrazier]

Got it figured out!!

I had other routines that included: "Application.EnableEvents = False" without ending the routine with "Application.EnableEvents = True"

I added the following to each sheet that included the button that I needed to make named command buttons visible/invisible and everything worked perfectly!!

Sub Worksheet_Change(ByVal Target As Range)
' ActiveX button
With Me
If .Range("e13").Value = 0 Then
.ViewRA_NE_GT_2_Days.Visible = False
Else
.ViewRA_NE_GT_2_Days.Visible = True
End If
End With
End Sub

Thanks Narayan, Mike and the rest of the Chandoo community! You Rock!!

Thomas