Disable/Enable Buttons in UserForms

Add to Favorites
Author: don | Edits: don

How to have a button in a UserForm disabled until the desired event occurs. For instance, you could prevent a "submit" button from being clicked until the user enters some information in a TextBox.

Sections:

Disable/Enable Buttons on UserForms

Automatically Disable/Enable Buttons Based on Events in the Form

Notes

Disable/Enable Buttons on UserForms

Using VBA

Disable a Button:

CommandButton2.Enabled = False

Enable a Button:

CommandButton2.Enabled = True

CommandButton2 is the name of the button that you want to disble/enable. You see this name when you click the desired button and look to the Name property in the Properties Window (F4).

Enabled is the property that controls if the button is enabled or not.

True/False True means that the button is enabled and can be clicked. False means that it is disabled and can't be clicked.

Without Using VBA Code

You can also control if a button is enabled or not without using any code; however this is not the best option, because you can only do this before the form runs. That means that you will still need VBA to change the status of the button while the form is running.

Click the desired button > look to the Properties Window (F4) > change the Enabled property to False to disable the button and True to enable the button.

0f4c9aad64a44c27ef35a45cf99b6e87.jpg

Automatically Disable/Enable Buttons Based on Events in the Form

Knowing how to enable and disable a button isn't very useful unless you know how to make something trigger that.

Here, I'll show you how to have a button disabled until a user inputs some text into a TextBox.

Disable Buttons When the UserForm Starts

This disables the button from the start, and allows you to have an action enable it later.

Go to the UserForm_Initialize() event and input this code:

CommandButton2.Enabled = False

CommandButton2 is the name of the button to disable; you can disable as many buttons as you want here.

56b0aeb2542b8960b476aa37f3554cfa.png

In your workbook, and the one included with this tutorial, there will probably be more code in the UserForm Initialize event; it doesn't matter where you put the code to disable the button.

Enable the Button When a User Enters Text

To do this, we have to use the Change event for the desired text box.

Go to the code section for your form and choose the name of the desired TextBox from the left drop-down menu and the Change event from the right drop-down menu.

9070f59495fb36bbb562914dd7368364.jpg

TextBox1 is the name of the TextBox in my form.

This section of code is what runs each time the user adds or removes text from the TextBox and the code to enable/disable the button goes here.

Code to enable/disable the button:

If TextBox1.Value <> "" Then

    CommandButton2.Enabled = True

Else

    CommandButton2.Enabled = False

End If

TextBox1 is the name of the TextBox that must have a value in it in order for the button to be enabled.

CommandButton2 is the button to enable.

This code will enable the button when text is entered into the text input and it will disable the button when all text is removed from the text input.

814566210473e45063712428d1c31534.png

Ignore Blanks or Spaces in the Input Area

You can update the above code to ignore when a user hits only the space key in the TextBox.

If Trim(TextBox1.Value) <> "" Then

    CommandButton2.Enabled = True

Else

    CommandButton2.Enabled = False

End If

The TRIM() function was all that was added. You can see the effect of this by removing it and hitting the space key in the TextBox of the sample file for this tutorial.

Notes

Disabling and enabling buttons in a form is rather easy to do; it's more complex once you add some logic to figure out what should cause the button to be disabled/enabled. In the end, try to keep it simple and easy to follow, especially if you have a lot of things that should cause the button to be disabled/enabled.

Make sure to download the attached file to see the code in operation in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
UserForm Properties List and Explanation
Tutorial: This is a listing of all properties for the UserForm in Excel. Each property includes an e...
Reference Other Excel Files with Formulas and Functions
Tutorial: In Excel you can use formulas and functions to reference data that is stored in another Ex...
Overwrite an Existing File Using VBA in Excel
Tutorial: How to allow your macro/vba code to overwrite an existing Excel file. This allows you to d...
Disable Calculation of Cells when Running a Macro in Excel
Tutorial: How to stop formulas and functions from updating in Excel when running a macro. This can s...
Disables the "Save As" Feature in Excel
Macro: This macro will disable the Save As feature in excel. This means that a user will not...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Tutorial Details
Downloadable Files: Excel File
Similar Content
UserForm Properties List and Explanation
Tutorial: This is a listing of all properties for the UserForm in Excel. Each property includes an e...
Reference Other Excel Files with Formulas and Functions
Tutorial: In Excel you can use formulas and functions to reference data that is stored in another Ex...
Overwrite an Existing File Using VBA in Excel
Tutorial: How to allow your macro/vba code to overwrite an existing Excel file. This allows you to d...
Excel Forum