[RESOLVED] [Excel] How do I go through all controls on a userform

[adifel]

Hi,

I have this userform in VBA Excel 2010, which has around 300 controls, most majority being checkboxes, the rest are option buttons, labels and textboxes; however, I need to save the status for each and all of them (value for checkboxes and option buttons, text and caption for textboxes and labels), so it can be recovered at a later time.

I thought to do this with a txt file with each line having a stat for one of the controls.

Question is, is there an easy way to go through all the controls, without checking them one by one? Or is there an better way to save them?

Thanks.

[incidentals]

you will have to do a for each control in controls type loop and collect each and every setting that you think is important and you will need to interogate the controls as you find them to know the property or properties that are appropriate to each type..

the advantage to this process it that the controls will be collected in order and the data will be able to be read back in order too...

unless of course you store the control enumeration as well as the properties, then you can fill in the controls in any order!

here to help

[adifel]

Thanks for the response. As I'm kinda new to VBA, can you give an example of a loop? How do I go through all the controls of one type (e.g. all textboxes), given that they all have different names?

[westconn1]

can you give an example of a loop?

for each c in me.controls
    'for most controls you will want to save the default value against the name value
   print #f, c.name & vbtab & c.value
next

where f is the number of a file opened for output or append

if you have to do them in some order it is more difficult as you probably have to loop separately for each type of control

if you just want to use to repopulate the form at later time the order does not matter
read the text file into an array and for each line set the value of the control like

for i = 0 to ubound(filearray)
   tmp = split(filearray(i), vbtab)
   me.controls(tmp(0)).value = tmp(1)
next

where filearray is an array filled by splitting the lines from the textfile

[dovbah]

Do it like this

Private Sub CommandButton1_Click()
    
    Dim Counter As Integer
        
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Counter = Counter + 1
            Sheets("Sheet1").Range("A" & Counter).Value = Ctrl.Name
            Sheets("Sheet1").Range("B" & Counter).Value = Ctrl.Text
        End If
    Next
End Sub

[adifel]

I will try that. Thank you!