Problem with Control Declaration?

[The Archn00b]

Hi everyone. Happy New Year to you all.

I'm working on a user form that has a number of check boxes on the left. On the right hand side there are two text boxes for each check box. When a check box is ticked (set to true) the two text boxes on the right hand side become active.

Rather than write an event subroutine for each check box I came up with this subroutine:

Dim ctl As Control
For Each ctl in Form.Controls
If ctl.Tag = checkbox.Tag And ctl.ControlType = acTextBox Then
Select Case checkbox.Value
Case True
ctl.Enabled = True
ctl.BackColor = &H80000009
Case False
ctl.Enabled = False
ctl.BackColor = &H8000000E
End Select
End If
Next ctl

It fails on:

For Each ctl in Form.Controls

With the Error Message:

Run time error number 13: Mismatch.

I don't understand why. I've declared ctl as a Control.

Any ideas? Thanks.

 

[Frothingslosh]

Try using Me.Controls instead of Form.Controls.

 

[CJ_London]

try

for each ctl in me.controls

 

[The Archn00b]

No sorry guys. Still the same error on the same line.

 

[CJ_London]

presume your code is in your form module

 

[Frothingslosh]

Okay, I'm looking into this more in-depth - I even have a form with three checkboxes and two text boxes per checkbox built - and I'm running into a couple issues.

First of all, it appears that this is just a snippet from a larger piece of code, because there is an issue in this:

If ctl.Tag = checkbox.Tag And ctl.ControlType = acTextBox Then

Specifically, checkbox is being used as a variable, but it is not defined in your code. As-is, if you're using Option Explicit, the module won't compile, and if you're not, then it is a variant with nothing assigned to it.

Also, if you're cycling through the form to check all the text boxes, when precisely are you running the code? Is it after the form's After Update event, manually called, or something else?

 

[Frothingslosh]

Okay, this code works - I've tested it in a form on my system.

Dim chk As Control
Dim txt As Control
 
    For Each chk In Me.Controls
        If chk.ControlType = acCheckBox Then
            For Each txt In Me.Controls
                If txt.ControlType = acTextBox Then
                    If chk.Tag = txt.Tag Then
                        If chk.Value = True Then
                            txt.Enabled = True
                            txt.BackColor = &H80000009
                        Else
                            txt.Enabled = False
                            txt.BackColor = &H8000000E
                        End If
                    End If
                End If
            Next txt
        End If
    Next chk

You'll need to insert it into whatever routine you're running to do this check. Personally, I think you'd be better off either just creating a subroutine which requires the checkbox as a parameter, and modifies the textboxes with a tag matching the tag on the passed control, but this will work fine as long as the form doesn't have a crazy number of controls.

 

[MarkK]

I'm working on a user form

Do you mean a VBA.UserForm in Excel? That is not the same as an Access.Form.

 

[smig]

Do you mean a VBA.UserForm in Excel? That is not the same as an Access.Form.

Even vba code is not the same