Looping through User Form controls

[mikeymay]

I am trying to use the following to check each Textbox in a user form

Private Sub CheckForm()

Dim ctlText As Control

Application.ScreenUpdating = False

btnAmend.Enabled = False

For Each ctlText In frmAmend.Controls
   If TypeName(ctlText) = "TextBox" Then
      If ctlText <> "" Then
         btnAmend.Enabled = True
         Else
      End If
   End If
Next ctlText

Application.ScreenUpdating = True

End Sub

but I am getting a Type Mismatch error on

For Each ctlText In frmAmend.Controls

I am struggling to see what is wrong here as from looking at several forums, this is suggested a number of times of how to loop through user form controls.

TIA

 

[Jaafar Tribak]

Is the CheckForm routine in the userform module ?

Try declaring the ctlText var as object :

Dim ctlText As Object

 

[mikeymay]

That did the job! Thanks

 

[RoryA]

Personally I'd suggest you declare it as MSForms.Control rather than late bound Object. I'd guess you have a reference set to another library with a Control class in it (e.g. Access).

 

[mikeymay]

Yes I'm using the form to send data to an access table.

Assuming I can't use the Access ref and .Forms as the same time?

 

[RoryA]

Yes you can, you just need to be more explicit when declaring variables.

 

[mikeymay]

Ok so I have the access and forms refs checked so what do I need to declare the control as if not an object?

 

[RoryA]

What I said earlier.

Dim ctlText As MSForms.Control

 

[mikeymay]

Sorted!!

All working as expected now.

Thanks all for your input.