clear all textboxs on a form

[_denis_]

Hi all

I have a userform with about 200 textboxs, I have a reset button on the form, which is to clear all the value's in each textbox. Is there a quick code to clear all textboxs values rather than clearing each one by one per my code below

userform3.textbox1.value = ""
userform3.textbox2.value = ""
userform3.textbox3.value = ""
userform3.textbox4.value = "" .........so on and so on


Thanks

Denis

 

[Greg Truby]

Assuming you've not dramatically renamed your textboxes:

<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> ctl <SPAN style="color:#00007F">As</SPAN> Control
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ctl <SPAN style="color:#00007F">In</SPAN> UserForm1.Controls
<SPAN style="color:#00007F">If</SPAN> Left(ctl.Name, 7) = "TextBox" <SPAN style="color:#00007F">Then</SPAN> ctl.Value = ""
    <SPAN style="color:#00007F">Next</SPAN> ctl
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Smitty]

And another way:

For i = 1 to 200
   Me.Controls("textbox" & i).Value = ""
Next i

HTH,

Smitty

 

[Nimrod]

Here's some code that does not require the Name of the object to have the string "text" in it . This procedure looks at the "type" of object versus the Name. So if you text object are name Curly Mo and Tom the clearing procedure will still work.

Private Sub CommandButton1_Click()
Dim ThisControl As Control
For Each ThisControl In UserForm1.Controls
If TypeName(ThisControl) = "TextBox" Then
ThisControl.Value = ""
End If
Next ThisControl
End Sub

 

[Mark O'Brien]

Not specifically answering this question and I agree with Nimrod's approach, but would it be easier for you just to unload the userform and then reload/show it again?

For example:

Private Sub CommandButton1_Click()
  Unload Me
  UserForm1.Show
End Sub

...and I have no fricking idea why this works, because it appears to go against logic. (If the form doesn't exist, how can "UserForm1.Show" be executed?)

 

[Todd Bardoni]

Not specifically answering this question and I agree with Nimrod's approach, but would it be easier for you just to unload the userform and then reload/show it again?

For example:

Private Sub CommandButton1_Click()
  Unload Me
  UserForm1.Show
End Sub

...and I have no fricking idea why this works, because it appears to go against logic. (If the form doesn't exist, how can "UserForm1.Show" be executed?)

Just pointing out that this will clear ALL controls, not just Textboxes.

 

[Mark O'Brien]

Just pointing out that this will clear ALL controls, not just Textboxes.

Yup, thanks for that. I forgot to say that.

 

[Greg Truby]

To Nimrod, I can only say "DOH! TypeName()!" I looked in the object browser for a .Type property on controls for just that reason, but forgot about TypeName()! Geez...