How to hide or unhide a Command Button based on specified cell value in Excel?
Supposing you are using a Command Button to trigger a VBA script in your worksheet. When the VBA script is unused in some cases, you need to make the Command Button hidden instead of displaying in the worksheet. And display it again when the VBA script is needed. This article is talking about hiding or showing a Command Button based on a specified cell value in Excel. Please do as follows.
You can run the below VBA code to hide or unhide a Command Button based on specified cell value in Excel.
1. Right click the sheet tab which contains the Command Button you need to show hide, then click View Code from the right-clicking menu.
2. In the popping up Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the Code window.
VBA code: Hide or unhide a Command Button based on specified cell value
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Cells(1, 1).Value <> "1" Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.ScreenUpdating = True End Sub
Note: in the code, Cells(1, 1), 1 and CommandButton1 indicate that the CommandButton1 will be hidden when cell A1 contains number 1, and displayed if cell A1 contains any other values except number 1. See below screenshot. And you can change them based on your need.
- How to copy Combo Box value to active cell in Excel?
- How to display date format in combo box output in Excel?
- How to open a specific worksheet by the selected value of a Combo Box in Excel?
- How to prevent or disable typing in a combo box in Excel?
- How to tab out of combo box to select specific cell in Excel?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agohola, el codigo esta bien? es que no me funciona y ya intente varias cosas para que funcione y nana, ayuda por favor es que lo necesito para algo importante :c
- To post as a guest, your comment is unpublished.· 3 months agoFor some reason after the button go from invisible back to visible, the button no longer works. If I alt + F11 into the code select the XXX() sub and run with F5 the code works for my macro. Why doesn't it work when i click the button anymore? in properties 'Enabled' is set to true
- To post as a guest, your comment is unpublished.· 7 months agoPrivate Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Cells(1, 1).Value <> "1" Then
Me.Shapes("Button 1").Visible = True
Me.Shapes("Button 1")End If
Application.ScreenUpdating = True
- To post as a guest, your comment is unpublished.· 1 years agoHello
I am new to VBA, I have put this code into a new Excel. One CommnadButton, on Sheet1 but the code doesnt work.
Do I have to do anything before this?
Thanks in Advance
- To post as a guest, your comment is unpublished.· 2 years agoUse the ActiveX Control Button.
- To post as a guest, your comment is unpublished.· 2 years agoI've put this code in and entered my button name (Finish), referenced the reference cell (P11 - 16, 11) but every time I make a change to the reference cell (P11) I get a compile error and it highlights the ".Finish" section of the code, seemingly meaning the button name I entered "Finish" is not found on the worksheet. I've confirmed, both in the drop down area to the left of the formula bar and in the "define name" area on the Formula tab that my button name is, in fact, "Finish", but it still does not work.
- To post as a guest, your comment is unpublished.· 2 years agoHi Bryce,
I changed the button name to "Finish" in my worksheet, and it works well without any error displaying.
Do you mind testing the code in a new blank workbook?
Thank you for your comment.
- To post as a guest, your comment is unpublished.· 2 years agohola que tal, tengo un boton con macro, que me devuelve a una hoja 'menu', pero no quiero que ese boton aparezca en la hoja menu como le puedo hacer?'
- To post as a guest, your comment is unpublished.· 4 months agoHola Sebastia,
Dónde debería mostrarse?
- Uno es la hoja de menú
- El botón está integrado en una hoja diferente, no?