Loop Through TextBoxes on UserForm

[DixiePiper]

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

 

[cerfani]

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/examples/controls-collection.html

This is how they loop the textboxes...

[COLOR=#548CD1][FONT='inherit']For[/FONT][/COLOR][COLOR=#666666][FONT='inherit'] i = 1 [/FONT][/COLOR][COLOR=#548CD1][FONT='inherit']To[/FONT][/COLOR][COLOR=#666666][FONT='inherit'] 10[/FONT][/COLOR]
[COLOR=#666666][FONT='inherit']    Controls("TextBox" & i).Value = Cells(i + 1, 1).Value[/FONT][/COLOR]
[COLOR=#548CD1][FONT='inherit']Next[/FONT][/COLOR][COLOR=#666666][FONT='inherit'] i[/FONT][/COLOR]

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

 

[Roderick_E]

looping

[FONT=arial]for each ctrl in userform1.controls
[/FONT][FONT=arial]If TypeName(ctrl) = "TextBox" Then[/FONT][FONT=arial]'do something to ctrl
endif
next [/FONT]

 

[jkpieterse]

Suppose the controls you need are on the second tab of the multipage:

    Dim oCtl As Control
    For Each oCtl In MultiPage1.Pages(1).Controls
        If TypeName(oCtl) = "TextBox" Then
        MsgBox oCtl.Name
        End If
    Next

 

[DixiePiper]

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:

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.

 

[cerfani]

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"

 

[DixiePiper]

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.

 

[arianfar]

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!!