Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Enable or Disable Button Based on Cell Value in Excel?
In Excel, buttons are an effective tool for automating processes and improving user experience. You can regulate the availability or accessibility of a button's functionality by connecting it to a certain cell value, which makes your spreadsheet more dynamic and user-friendly.
In this article, we'll look at how to activate or deactivate a button depending on the value of a cell. You can create Excel spreadsheets that respond logically to user inputs by mastering these approaches. We'll assume that you have a fundamental understanding of Excel and its features throughout this course. Let's get going and see how to use Excel to activate or disable buttons based on cell values!
Enable or Disable Button Based on Cell Value
Here we will add the VAB code to the sheet to complete the task. So let us see a simple process to know how you can enable or disable buttons based on cell values in Excel.
Step 1
Consider an Excel workbook where you have two buttons.
First, right-click on the sheet name and select View Code to open the VBA application.
Right-click > View Code.
Step 2
Then copy the below code into the text box.
Code
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
Step 3
Then close the VBA using Alt + Q. From Now on, the following things happen −
If you type data in Column A, button 1 is enabled and button 2 is disabled.
If you type data in Column C, button 2 is enabled and button 1 is disabled.
If both columns A and C have data, and two buttons are enabled.
If two buttons are disabled if there is no data in columns A and C.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can enable or disable buttons based on cell values in Excel to highlight a particular set of data.
306 Views
