Loop Through TextBoxes on UserForm
Results 1 to 8 of 8

Thread: Loop Through TextBoxes on UserForm

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loop Through TextBoxes on UserForm

    I have to pages of a MultiPage control on a UserForm that each contain 17 items, each comprised of a checkbox, label, and textbox. When a box is checked, a percentage is entered. Not all boxes will be checked. When this data is passed to the spreadsheet, I need all blanks to be 0 not "" in order for certain calculations to work. I would like to know if there is a way to loop through only the TextBox controls and a) format them to show a percentage to 2 decimal places and b) fill any blanks with "0".

    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,136
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    just name all your textboxes with a number at the end like default and then look at the example here... http://www.excel-easy.com/vba/exampl...ollection.html

    This is how they loop the textboxes...
    Code:
    For i = 1 To 10
        Controls("TextBox" & i).Value = Cells(i + 1, 1).Value
    Next i
    i didnt read it thoroughly but the userform should have a Controls property that holds a collection of controls which you can retrieve by their name. If not you can add all the controls to a collection and loop your collection
    Last edited by cerfani; Feb 1st, 2018 at 10:45 AM.

  3. #3
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,051
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    looping

    Code:
    for each ctrl in userform1.controls
    If TypeName(ctrl) = "TextBox" Then'do something to ctrl
    endif
    next 
    for more go to rodericke.com/xlsuper

  4. #4
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    893
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    Suppose the controls you need are on the second tab of the multipage:
    Code:
        Dim oCtl As Control
        For Each oCtl In MultiPage1.Pages(1).Controls
            If TypeName(oCtl) = "TextBox" Then
            MsgBox oCtl.Name
            End If
        Next
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    Apologies if this shows up twice. My first reply never appeared.

    Thank you all for your time. Based on your responses plus a little tinkering, I was able to fill the blank boxes with zeros. I am stuck, however, on the format. Ideally I would like:
    1. Entering "1.96" displays as "1.96%"
    2. Format is already in place when navigating to the page. I don't want to rely on the "submit" button to update the formats.
    3. I'm NOT looking for any kind of "format as you type"; on exit of the box is fine.


    Here is the code I'm using:
    Code:
    For Each ctrl In Me.MultiPage1.Pages(2).Controls
                    Debug.Print TypeName(ctrl)
                    
                    If TypeOf ctrl Is MSForms.TextBox Then
                        ctrl.value = Format(ctrl.value / 100, "#.00%")
                    End If
                Next ctrl
    I get a "Run-time error 13: Type mismatch" in regards to the ctrl.value = Format ... line

    Any input on where I should put the code and how to apply it to only 2 pages of a 4 page MulitPage would also be appreciated.

  6. #6
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,136
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    a textbox value is a string unlike an excel cell which is a variant. You can't perform arithmetic operations on a string. You can just append a "%" to the end of the textbox value...

    ctrl.Value = ctrl.Value & "%" ... that will result in a new string "1.96%" and set it to the textbox... assuming the textboxvalue began as "1.96"

  7. #7
    New Member
    Join Date
    Oct 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    I figured that out after a bit of trial and error. Right now I have ctrl.Value = Format((Val(ctrl.Value)/100,"0.00%") and it's working. Except for every once in a blue moon, while debugging something else, it gives me an error. I'm going to try your suggestion to see if that works and stops the weird error from popping up. Appreciate the input. Everyone has been awesome in helping me figure out where I go wrong.

  8. #8
    New Member
    Join Date
    Aug 2019
    Location
    sonarweb.ir
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through TextBoxes on UserForm

    hi evry one!
    My work was done by Mr. cerfani's codes! Good luck Mr. cerfani !
    thank you
    mrexcel !
    I merged 260 lines of code into 4 lines!!

Some videos you may like

User Tag List

Tags for this Thread

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
  •