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 optionbutton 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