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

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 = ""
[etc…]
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!



Answer this question

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

  • MESSA

    hello
    plz try like this,
    userform1.Controls(txtbx).value=""
    best regards.
    sjoo

  • anester

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

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

  • Cherming

    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


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