Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

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.

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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    lol · 12 days ago
    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
  • To post as a guest, your comment is unpublished.
    Jordan · 21 days ago
    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
  • To post as a guest, your comment is unpublished.
    sdf · 3 months ago
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Cells(1, 1).Value <> "1" Then
    Me.Shapes("Button 1").Visible = True
    Else
    Me.Shapes("Button 1")
    End If
    Application.ScreenUpdating = True
    End Sub
  • To post as a guest, your comment is unpublished.
    Et41 · 1 years ago
    Hello

    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.
    Jeremy · 1 years ago
    Use the ActiveX Control Button.
  • To post as a guest, your comment is unpublished.
    Bryce · 2 years ago
    I'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.


    Any help?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi 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.
    sebastian · 2 years ago
    hola 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?'


    saludos.-
    • To post as a guest, your comment is unpublished.
      Andreas · 27 days ago
      Hola Sebastia,

      Dónde debería mostrarse?
      - Uno es la hoja de menú
      - El botón está integrado en una hoja diferente, no?