Looping through text boxes on a user form

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Looping through text boxes on a user form

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,620
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Looping through text boxes on a user form

     
    On a user form, I have 30 text boxes named txtFund1 through txtFund30. In VBA, I am trying to access these boxes to initialize them, and update values on my Excel spreadsheet with these values.

    I can to do all this pretty easily by writing a line for each inidividual text box. However, I want to be more efficient and use a loop. However, I can not get it to work. Here is my code to initialize the values, that isn't working:

    Code:
        Dim i As Integer
        Dim MyTextBox
        For i = 1 To 30
            MyTextBox = txtFund & i
            MyTextBox.Value = ""
        Next i
    What do I need to do to be able to loop through all 30 text boxes? Keep in mind that there are also other text boxes on my form, so I can't just through all text boxes on the form.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping through text boxes on a user form

    Try:

    Code:
    Dim i As Integer 
    For i = 1 To 30 
        Controls("txtFund" & i).Value = "" 
    Next i

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,620
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Looping through text boxes on a user form

    Andrew,

    Thank you!!! That was just what I needed.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    New Member
    Join Date
    Jun 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looping through text boxes on a user form

    Quote Originally Posted by Andrew Poulsom View Post
    Try:

    Code:
    Dim i As Integer 
    For i = 1 To 30 
        Controls("txtFund" & i).Value = "" 
    Next i

    I am wondering about a step that would be before this. I am creating my textboxes in a loop but believe I am not naming them properly because the code above does not work to fill them however if I put in text boxes by hand it does.

    Here is what I have tried for creating the textboxes

    Sub Add_Stages()
    Dim txtB2 As Control
    Dim txtB1 As Control
    Dim lbl1 As Control
    Dim lbl2 As Control
    Dim i As Integer
    i = 1

    For i = 1 To fsrform.Stages.Value

    Set lbl1 = fsrform.MultiPage1.Pages("StagesTab").Controls.Add("Forms.Label.1")

    With lbl1
    .Name = "Stagelbl" & i
    .Caption = "Stage " & i & " name:"
    .Height = 16
    .Width = 80
    .Left = 20
    .Top = 30 + (i * 30)
    End With

    Set txtB1 = fsrform.MultiPage1.Pages("StagesTab").Controls.Add("Forms.TextBox.1")

    With txtB1
    .Name = "Stage" & i
    .Height = 16
    .Width = 220
    .Left = 110
    .Top = 30 + (i * 30)
    End With

    Set lbl2 = fsrform.MultiPage1.Pages("StagesTab").Controls.Add("Forms.Label.1")

    With lbl2
    .Name = "Dayslbl" & i
    .Caption = "Days in Stage " & i & ":"
    .Height = 16
    .Width = 80
    .Left = 340
    .Top = 30 + (i * 30)
    End With

    Set txtB2 = fsrform.MultiPage1.Pages("StagesTab").Controls.Add("Forms.TextBox.1")

    With txtB2
    .Name = "Days" & i
    .Height = 16
    .Width = 40
    .Left = 430
    .Top = 30 + (i * 30)
    End With

    Next

    End Sub

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How could i select specific text boxes from multiple text boxes in excel userform vba?

      
    Dear Sir,
    How could i select specific text boxes from multiple text boxes in excel userform vba?

    please help me in this regards.

    Regards.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com