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.
Disable/Enable Buttons on UserForms
Automatically Disable/Enable Buttons Based on Events in the Form
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.
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.
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.
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.
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.
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.
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.