Results 1 to 4 of 4

Looping through text boxes on a user form

This is a discussion on Looping through text boxes on a user form within the Excel Questions forums, part of the Question Forums category; On a user form, I have 30 text boxes named txtFund1 through txtFund30 . In VBA , I am trying ...

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    39,669

    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

    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
    39,669

    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

    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

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