Skip to main content

How to hide or unhide a Command Button based on specified cell value in Excel?

Author: Siluvia Last Modified: 2020-07-01

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.

Hide or unhide a Command Button based on specified cell value with VBA code


Hide or unhide a Command Button based on specified cell value with VBA code

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.


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Cool idea. Pity it doesn't work :(
This comment was minimized by the moderator on the site
Der Code funktioniert wie er ist prima.
Ich würde nur gerne den Button bei jeder 0 und leeren Zelle ausblenden lassen. Irgendwie bekomme ich es nicht hin. Es funktioniert immer nur entweder oder. Könnten Sie mir bitte weiter helfen danke
This comment was minimized by the moderator on the site
Hi Christoph,

Sorry I don't understand what you mean. For clarity, please attach a sample file or a screenshot with your data and desired results.
This comment was minimized by the moderator on the site
Hola!
Alguien sabe como puedo hacer para que el boton comando aparezca o desaparezca segun información de otra hoja?
El ejemplo está muy bueno, pero las celdas son de la misma hoja donde esta el boton. Yo necesito ocultar un boton segun info de otra hoja.

Gracias!
This comment was minimized by the moderator on the site
Hi,
Suppose, the value used to display or hide the command button locates in A1 of Sheet1, and the comman button locates in Sheet2. Please open the Sheet1, right click the sheet tab and click View Code. In the opening Microsoft Visual Basic for Applications window, copy the following VBA code into the Sheet1 (Code) window, and then press the Alt + F11 keys to close it.
Note: Please change Cells(1, 1), "1" and "Sheet2" as you need.
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Cells(1, 1).Value <> "1" Then
        Sheets("Sheet2").CommandButton1.Visible = True
    Else
        Sheets("Sheet2").CommandButton1.Visible = False
    End If
    Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
hola, 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
This comment was minimized by the moderator on the site
For 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
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations