Display or Hide Controls on UserForm Using Option Buttons

A message from a website visitor:
I am working with user-forms in MS Excel. I have two textboxes on my form which I wish to hide till an option-button is selected. How can this be done using VBA code?

We create a form with two textbox controls and two option buttons. You could also create only one option button
We create the code for the form when it is first opened _form initialization). Here we want only the option buttons to be visible. Here we use the text function left() to extract the word ‘Text’ from the names of the ‘TextBox1’ and ‘TextBox2’ to make them invisible.
Next we code the option buttons so that on clicking on option button 1 the textbox controls are displayed and on clicking on option button 2 the text box controls are hidden. We use a ‘for… next’ loop to display or hide the controls on the form. We also ensure that when the textbox controls are hidden our option button controls still remain visible.
The code is given below.

VBA Code for the various user-form controls:

Private Sub OptionButton1_Click()
For Each objCtrl In Me.Controls
If OptionButton1.Value Then objCtrl.Visible = True
Next

End Sub

Private Sub OptionButton2_Click()
For Each objCtrl In Me.Controls
If OptionButton2.Value Then objCtrl.Visible = False
Next
OptionButton1.Visible = True
OptionButton2.Visible = True
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Dim objCtrl As Control

OptionButton1.Value = False
OptionButton2.Value = False

For Each objCtrl In Me.Controls
If Left(objCtrl.Name, 4) = “Text” Then objCtrl.Visible = False
Next
End Sub

The training video below explains the details of displaying or hiding the controls on an Excel user-form as desired by the user:


Leave a Reply

Your email address will not be published. Required fields are marked *