Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to enable or disable button based on cell value in Excel?

This article, I will talk about some methods to enable or disable button based on cell value in Excel worksheet.

Enable or disable two buttons based on two column data with VBA code

Enable or disable a button based on two cell values with VBA code


Enable or disable two buttons based on two column data with VBA code


For example, I have two command buttons in a worksheet, if Column A has data, the button1 is enable, and button 2 is disable; if Column C contains data, the button 2 is enable, and button 1 is disable. To solve this task, the following VBA code may help you.

1. Right click the sheet that contain the buttons, and then choose View Code from the context menu, see screenshot:

2. In the Microsoft Visual Basic for Applications window, please copy and paste the below code into the blank module:

VBA code: Enable or disable two buttons based on two columns:

Private Sub Worksheet_Change(ByVal Target As Range)
    CommandButton1.Enabled = False
    CommandButton2.Enabled = False
    If Application.WorksheetFunction.CountA(Range("C:C")) > 0 Then
        CommandButton2.Enabled = True
    End If
    If Application.WorksheetFunction.CountA(Range("A:A")) > 0 Then
        CommandButton1.Enabled = True
    End If
End Sub

Note: In the above code, CommandButton1 and CommandButton2 are the buttons names, A:A and C:C are the two columns that you use to enable or disable two buttons, please change them to your need.

3. Then save and close the code window, now, you will get the following result as you need:

(1.) If type data in Column A, the button 1 is enabled and button 2 is disabled, see screenshot:

(2.) If type data in Column C, the button 2 is enabled and button 1 is disabled, see screenshot:

(3.) Both Column A and Column C have data, two buttons are all enabled, see screenshot:

(4.) Two buttons are all disabled if there is no data in Column A and Column C, see screenshot:


Enable or disable a button based on two cell values with VBA code

If you want to enable or disable a command button based on two cell values, such as, when value in A1 is greater than value in B1, the button is enable, else, the button is disable. The following VBA code also can do you a favor.

1. Right click the sheet that contain the button, and then choose View Code from the context menu, in the Microsoft Visual Basic for Applications window, please copy and paste the below code into the blank module, see screenshot:

VBA code: Enable or disable a button based on two cell values:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TargetAdd As String
    Dim xTemp As Double
    On Error GoTo ExitSub
    TargetAdd = Target.Address
    CommandButton1.Enabled = False
    If (TargetAdd = Range("A1").Address) Or (TargetAdd = Range("B1").Address) Then
        If Range("A1") > Range("B1") Then
            CommandButton1.Enabled = True
        End If
    End If
ExitSub:
End Sub

Note: In the above code, A1 and B1 are the cell values that you use, CommanButton1 is the button name, change them to your own.

2. Then save and close the code window, now, if the cell value in A1 is greater than B1, the button is enabled, else, if the cell value in A1 is less than B1, the button is disabled, see screenshot:


Recommended Productivity Tools for Excel

kte tab 201905

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Mike · 3 months ago
    I receive a "Expected Function or variable" error when I incorporate this code - could you show the code you set up for the two buttons? May help me solve the error.