MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Clear TextBoxes in Excel UserForms

  Asked By: Donna    Date: Oct 26    Category: MS Office    Views: 19481

As part of my project I currently have a Autorun macro that collects
personal information and relays it to a cell in a seperate worksheet
via the controlsource.

I have two command buttons on the form. One is 'OK' effectively to
unloading the 'PersonalInfo' UserForm. The Second is 'Clear' I want
this to clear eveything text box I have on the form:

The format basically goes as this:

First Name>>>>>>>>>>>firstname (textbox)
Surname>>>>>>>>>>>>>>surname (textbox)


How can I command the clear Button to clear every textbox on the form?



5 Answers Found

Answer #1    Answered By: Mable Stone     Answered On: Oct 26

All you need to do is either cycle through the textbox  controls with a "For
Next" loop or list them all individually and set the .Value property to

textboxcontrolname.Value = "" 'substitute the actual name of you control
for textboxcontrolname

You may want to set up a UserForm_Initialize event for you form  that will
clear all the textbox controls before the user sees the form. Then you may
find that an Ok and a Cancel button  are all you need and not a "Clear"

Answer #2    Answered By: Guillermo Cox     Answered On: Oct 26

Currently I've got it all as:

Private Sub cmdClear_Click()
firstname.Value = Null
surname.Value = Null
address.Value = Null
town.Value = Null
county.Value = Null
postcode.Value = Null
email.Value = Null
phone.Value = Null
End Sub

However, this clears only one box  when I press the clear  box, and then
the next one down when I click it again. I'm guessing I need some sort
of Loop, how do I implement it?

Answer #3    Answered By: Anat Massri     Answered On: Oct 26

The code below is working fine by pressing commandbutton2 (say clear) with my
excel VBA programme. This may be useful to u. If it don't work, just tell me

Private Sub CommandButton2_Click()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
End Sub

I think it should work.

Answer #4    Answered By: Jawahir Burki     Answered On: Oct 26

The below code works in word and I'm pretty sure it should work in
Excel too.
It goes through all the text  boxes regardless of name. You will need
to have the extenibility library referenced.

Otherwise you would have to name the text boxes and clear  each one

Public Sub subClearTextBoxes(frmpForm As Object)

Dim intlNumberOfControls As Integer
Dim ctlCurrent As Control
Dim strlControlType As String
Dim slFormName As String

intlNumberOfControls = frmpForm.Controls.count
For Each ctlCurrent In frmpForm.Controls
If TypeOf ctlCurrent Is MSForms.TextBox Then
ctlCurrent.Text = ""
End If
Next ctlCurrent
End Sub

Answer #5    Answered By: Pearl Garza     Answered On: Oct 26

If you unload the userform  I have found that it will clear  the values.

PersonalInfo.Hide 'hides the form  and maintains the values
Unload PersonalInfo 'unloads the form from memory and resets the form to
original values

Didn't find what you were looking for? Find more on Clear TextBoxes in Excel UserForms Or get search suggestion and latest updates.