Is there a way to test if a control exists on a userform?

I have a sub (ClearUserform) that is called by several userforms.  Sometimes 
the control may not be on the userform passed to ClearUserform, thus I get an 
error.  So I added the On Error Resume Next statement to quickly work around 
the error.  Is there a way to test if the control exists on the userform 
passed to the sub.

Sub MySub()
    Call ClearUserform(Userform1)
End Sub

Sub ClearUserform(MyForm As UserForm)

    On Error Resume Next
    MyForm.TextBox1 = ""
    
    ' i have other controls list here
    
    On Error GoTo 0
    
End Sub
-- 
Cheers,
Ryan
0
Utf
3/18/2010 10:07:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
3482 Views

Similar Articles

[PageSpeed] 15

Why not pass the control to the ClearForm macro?

-- 

HTH

Bob

"Ryan H" <RyanH@discussions.microsoft.com> wrote in message 
news:39E91873-FDFC-462D-A100-478687539E02@microsoft.com...
>I have a sub (ClearUserform) that is called by several userforms. 
>Sometimes
> the control may not be on the userform passed to ClearUserform, thus I get 
> an
> error.  So I added the On Error Resume Next statement to quickly work 
> around
> the error.  Is there a way to test if the control exists on the userform
> passed to the sub.
>
> Sub MySub()
>    Call ClearUserform(Userform1)
> End Sub
>
> Sub ClearUserform(MyForm As UserForm)
>
>    On Error Resume Next
>    MyForm.TextBox1 = ""
>
>    ' i have other controls list here
>
>    On Error GoTo 0
>
> End Sub
> -- 
> Cheers,
> Ryan 


0
Bob
3/18/2010 10:29:07 PM
Or if you're clearing all the textboxes on that passed userform...

Option Explicit
sub ClearUserForm(myform as userform)
  dim ctrl as control
  for each ctrl in myform.controls
      if typeof ctrl is msforms.textbox then
          ctrl.value = ""
      end if
  next ctrl
end sub

But you could do something like this:

Option Explicit
Sub ClearUserForm(myform As UserForm)
  Dim ctrl As Control

  Set ctrl = Nothing
  On Error Resume Next
  Set ctrl = myform.TextBox1
  On Error GoTo 0
  
  If ctrl Is Nothing Then
    'do nothing
  Else
    ctrl.Value = ""
  End If
End Sub


But I think Bob's idea is much better.  And if you're using the same module to
clear lots of types of controls, you could use typeof to determine how to clear
it.

Ryan H wrote:
> 
> I have a sub (ClearUserform) that is called by several userforms.  Sometimes
> the control may not be on the userform passed to ClearUserform, thus I get an
> error.  So I added the On Error Resume Next statement to quickly work around
> the error.  Is there a way to test if the control exists on the userform
> passed to the sub.
> 
> Sub MySub()
>     Call ClearUserform(Userform1)
> End Sub
> 
> Sub ClearUserform(MyForm As UserForm)
> 
>     On Error Resume Next
>     MyForm.TextBox1 = ""
> 
>     ' i have other controls list here
> 
>     On Error GoTo 0
> 
> End Sub
> --
> Cheers,
> Ryan

-- 

Dave Peterson
0
Dave
3/18/2010 11:17:56 PM
Because the number of controls on each userform to clear varies from 4 to 25. 
 I'd like to make my ClearUserform sub universal for all userforms.  This way 
I just send over the userform and not have to list out all the controls.
-- 
Cheers,
Ryan


"Bob Phillips" wrote:

> 
> Why not pass the control to the ClearForm macro?
> 
> -- 
> 
> HTH
> 
> Bob
> 
> "Ryan H" <RyanH@discussions.microsoft.com> wrote in message 
> news:39E91873-FDFC-462D-A100-478687539E02@microsoft.com...
> >I have a sub (ClearUserform) that is called by several userforms. 
> >Sometimes
> > the control may not be on the userform passed to ClearUserform, thus I get 
> > an
> > error.  So I added the On Error Resume Next statement to quickly work 
> > around
> > the error.  Is there a way to test if the control exists on the userform
> > passed to the sub.
> >
> > Sub MySub()
> >    Call ClearUserform(Userform1)
> > End Sub
> >
> > Sub ClearUserform(MyForm As UserForm)
> >
> >    On Error Resume Next
> >    MyForm.TextBox1 = ""
> >
> >    ' i have other controls list here
> >
> >    On Error GoTo 0
> >
> > End Sub
> > -- 
> > Cheers,
> > Ryan 
> 
> 
> .
> 
0
Utf
3/18/2010 11:24:01 PM
maybe something like this

  For s = 0 To .Frame1.Controls.Count - 1
                        If .Frame1.Controls(s).Name Like "OptionButton*" 
Then

-- 


Gary Keramidas
Excel 2003


"Ryan H" <RyanH@discussions.microsoft.com> wrote in message 
news:39E91873-FDFC-462D-A100-478687539E02@microsoft.com...
>I have a sub (ClearUserform) that is called by several userforms. 
>Sometimes
> the control may not be on the userform passed to ClearUserform, thus I get 
> an
> error.  So I added the On Error Resume Next statement to quickly work 
> around
> the error.  Is there a way to test if the control exists on the userform
> passed to the sub.
>
> Sub MySub()
>    Call ClearUserform(Userform1)
> End Sub
>
> Sub ClearUserform(MyForm As UserForm)
>
>    On Error Resume Next
>    MyForm.TextBox1 = ""
>
>    ' i have other controls list here
>
>    On Error GoTo 0
>
> End Sub
> -- 
> Cheers,
> Ryan 

0
Gary
3/19/2010 2:20:24 AM
Reply:

Similar Artilces: