Help looping through controls on a userform within a frame 3

[JasonEnsor]

Hi Guy's

I might be being daft but for some reason I can't get this code to work. I am using a VBA Userform in Excel 2010.

Option Explicit

Dim t As MSForms.TextBox
Dim chk As MSForms.CheckBox

Private Sub CommandButton1_Click()

For Each t In UserForm1.Frame1.Controls
    On Error Resume Next
    t.Value = CCur(t.Value)
    If Err.Number <> 0 Then
    t.Value = Format$(0, "Currency")
    MsgBox "Error"
    On Error GoTo 0
    End If
    t.Value = Format$(t.Value, "Currency")
Next t

For Each chk In UserForm1.Frame1.Controls

    chk.Value = True

Next chk

End Sub

My first for loop works for setting the value of the textboxes in the frame. The issue I am facing is I can not get it to update the value of the checkboxes. I get an error 13 type miss match on the

next t

.

As I have loads of textboxes, comboboxes and checkboxes on my userform I thought looping would be a good way to clear the userform as my userform is set up using frames to separate the different sections.

I am currently testing this on a userform with a single frame, two textboxes and two checkboxes.

Any help would be appreciated.

J.

 

[Andrzejek]

Consider this:

Dim c As msforms.Control

For Each c In UserForm1.Frame1.Controls
    If TypeOf c Is msforms.TextBox Then
        c.Text = "Some text"
    ElseIf TypeOf c Is msforms.CheckBox Then
        c.Value = 1
    Else
        ...
    End If
Next c

Have fun.

---- Andy

 

[JasonEnsor]

Hi Andy,

Thanks for the reply. Using some of what you suggested I came up with a solution that works with the code to validate input that I was using.

I have listed my code below for future reference

Dim t As MSForms.Control
Dim chk As MSForms.Control

Private Sub CommandButton1_Click()

For Each t In UserForm1.Frame1.Controls
    If TypeOf t Is MSForms.TextBox Then
    On Error Resume Next
    t.Value = CCur(t.Value)
    If Err.Number <> 0 Then
    t.Value = Format$(0, "Currency")
    MsgBox "Error"
    On Error GoTo 0
    End If
    t.Value = Format$(t.Value, "Currency")
    End If
Next t

For Each chk In UserForm1.Frame1.Controls
    If TypeOf chk Is MSForms.CheckBox Then
    chk.Value = True
    End If
Next chk

End Sub

Many Thanks

J.

 

[PHV]

Why not simply this ?

Private Sub CommandButton1_Click()
Dim t As MSForms.Control
For Each t In UserForm1.Frame1.Controls
  If TypeOf t Is MSForms.TextBox Then
    On Error Resume Next
    t.Value = CCur(t.Value)
    If Err.Number <> 0 Then
      t.Value = 0
      MsgBox "Error"
    End If
    On Error GoTo 0
    t.Value = Format$(t.Value, "Currency")
  ElseIf TypeOf t Is MSForms.CheckBox Then
    t.Value = True
  End If
Next t
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Hi PHV,

I just tested your solution and that works perfectly. I guess in Answer to your question.....I overthought the solution and should have simplified it.

Many Thanks

J.

 

[Andrzejek]

PHV, I can do this in VB 6, but not in VBA in Excel:

Dim t As MSForms.Control
For Each t In UserForm1.Controls
  If t.[red]Container[/red] = Frame1 Then
    If TypeOf t Is MSForms.TextBox Then
...

which is a nice way to loop thru all controls on the Form, and pick the controls on certain Container.
Does it mean MSForms.Control does NOT have Container property? :-(


Have fun.

---- Andy

 

[combo]

Andy,
you can use Parent instead:
[tt]If t.Parent Is Frame1 Then[/tt]

combo

 

[strongm]

I think this is a case where Select Case may be a better option, as it more easily allows the code to be expanded to deal with additional controls. So something like the following:

[blue]Private Sub CommandButton1_Click()
    Dim t As MSForms.Control
    For Each t In UserForm1.Frame1.Controls
        Select Case TypeName(t)
            Case "TextBox"
                If Not IsNumeric(t) Then t = 0
                t.Value = Format$(t.Value, "Currency")
            Case "CheckBox"
                t.Value = True
            Case Else
                [green]' Default handling[/green]
        End Select
    Next
End Sub[/blue]

 

[JasonEnsor]

Thanks guys, some really good suggestions here.

J.

 

[Andrzejek]

Combo,
Thanks. Same animal, different name

JasonEnsor,
Couple of small things:
[tt]Dim t As MSForms.Control[/tt]
I know you named it t because originally it was a TextBox. But now, since it is a 'generic' Control, I would name it something like Ctrl or just c

And, align your code. It is a lot easier to read and spot any problems (See PHV and strongm's posts)

Have fun.

---- Andy

 

[JasonEnsor]

Hi Andy,

I apologise for the poorly formatted code, it was pure laziness on my part, it was purely as I had just created a basic userform to test out the theory. Normally I indent everything. As for renaming t to ctrl.....it was the first thing I did this morning when I added the code to my project, again it was laziness.

Thanks Again
J.