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

Hot Topic

Output Parameter in Excel VBA
How to convert excel file to .DAT file?
VBA Harddisk serial
Projects, objects & modules
Left, Mid and Right functions not working on Office 2003
How to pass a value of a clicked cell to another cell in a different worksheet?
opc
calling a function and passing the name of the recordset
Order By DateDiff() in Access Report
How to pass a value of a clicked cell to another cell in a different worksheet?

Microsoft ISV

How do I write this!!
Create ProjectItem Error. (Core API)
Help with interface?
Problem regarding VBA in excel - Combine 52 VBA codes for 52 weeks in a year to one VBA code
hyperlinks don't work when pointing to file shortcuts
Validation with List drop down?
Access 2000 RecordSet Sort problem
[Access SQL]: Have a function similar Oracle SQL function Minus use in MS Access SQL?
seek for a file
VBA sheet using registered dll functions across a network

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?)

  • johnd2

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

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

  • TrevorD

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

  • velkan

    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?)
Answer this question