Disable/Enable Buttons in UserForms

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.

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.

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.

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.

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.