This might be really easy, or maybe it's really hard I'm pretty new to VBA

I'm using the VBA editor in Excel 2003 to automate some data entry into a spreadsheet. I have several userforms all with various numbers of textboxes, the largest having 34. What I am trying to do is use some sort of loop to clear the value out of each textbox when the user creates a new record. I want to do this mostly because I¡¯m lazy, but also because I foresee the need to be able to expand this later. I¡¯m trying to avoid the following situation:

With userform1
.TextBox1.value = ""
.TextBox2.value = ""
.TextBox3.value = ""
End With

I¡¯ve tried a lot of variations on the following:

For iCount = 1 To 34
userform1.TextBox & iCount.value = ""
Next iCount

And things like:

Dim txtbx As Object
For iCount = 1 To 34
txtbx = ¡°TextBox¡± & iCount
userform1.txtbx.value = ""
Next iCount

For all of these I keep getting a ¡°Method or Data Member not found¡± error. Can anyone give me a suggestion Thanks!

Re: Efficent Way to manage multiple textboxes (maybe a loop?)


plz try like this,
best regards.

Re: Efficent Way to manage multiple textboxes (maybe a loop?)


Excellent! Thanks! That's exactly what I wanted to do!

Thanks for the help, I'd been fighting this silly thing for hours.

Re: Efficent Way to manage multiple textboxes (maybe a loop?)

Joe Dawson

I found this post interesting and toyed with some code that counts the text boxes so that when you add one to a userform you dont have to adjust the number of times it loops. If someone has a more elegant way to count the userforms than what I have pasted below, please share.

Dim tCont As Control

For Each tCont In Me.Controls
If TypeName(tCont) = "TextBox" Then
i = i + 1
End If
Next tCont

MsgBox i