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
3444 Views

Similar Articles

[PageSpeed] 2

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:

Alternative to Calendar Control 8.0
I noticed that the Active X Calendar Control 8.0 control is not currently installed on my machines at work, and for now I am assuming that I will NOT be allowed to install it at my highly secure workplace. (I couldnt find it anywhere in the "Additional Controls" area) So.... Does anybody have any bright ideas on how to implement something similar to a calendar control?? I just want an easy way for my users to select a date. Right now we are simply typing a date in a target cell, but I thought it would be nice if we could select dates in a more flexible manner. The...

Generating Multiple Choice Tests
I currently use Access 2003 to create tests. I am looking to use a grouping and random sampling method to improve efficiency. In as much that I use access to create tests now, is VBA an appropriate platform for randomizing and group sampling or should I go to an independent language. Any advice will be greatly appreciated. -- mhm karst, ******UNTESTED... Something like... SELECT TOP 1 Rnd([TestID]) AS Expr1, TestText FROM tblTests ORDER BY Rnd([TestID]) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, inform...

The ActiveX control could not be created
Hello, In the Project Center of PWA 2007 + SP2 and Aug CU, I did: Actions > Export Grid to Excel. But I got the error: "The ActivX control on which this feature depends could not be created. Because of this error, you can only copy the XML data to the clipboard. Do you want to continue?" How/where can I install this ActiveX control? Thanks for your help. Depending on your IE security settings, it should prompt you to install the ActiveX control. Have you set the site as a trusted site within IE? - Andrew Lavinsky Blog: http://blogs.catapultsystems.co...

Time format control
Hi everyone, I have a time of 08:38 and would like to truncate the last minute by using formatting to achieve 08:30. I currently do this using a formula to remove the last minute. I have tried custom formatting of hh.m but I still get 08:38. I have even tried hh.m"0" but get 08:380. Any Ideas would be greatly appreciated. Regards Brad You can use this formula, it sounds as though you want round down to nearest 10th minute =FLOOR(A1,1/144) -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Brad kennedy" <brad_6663@hotmail...

PDFC Reader Control
Since I didn't get much of a response on a previous post, I am going to try this from a different angle. I have a form with a PDFC Reader control on it. Various pdf files are opened and viewed in the control. I would like to be able to identify and close specific documents from code and haven't been able to determine how this is done. Does anybody have any suggestions on how this could be done? From where did you get the control? Have you contacted them? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rich K" &l...

Can we bind a control to the static member variable ?
Hi, I was wondering, can we bind a control ? e.g. Can an edit box be bound with a static variable of type CEdit ?? I have tried but giving error.. ===== Regards, Jigar Mehta Jigar Mehta wrote: > Hi, > > I was wondering, can we bind a control ? > > e.g. Can an edit box be bound with a static variable of type CEdit ?? > > I have tried but giving error.. > ===== > Regards, > Jigar Mehta > > That is a very unusual thing to want to do. I don't know if it works because I've never needed such a thing. Can you tell us about the error you get,...

Cut/Copy/Paste not working for a subclassed edit control
What do I need to do to regain this functionality? Thanks, Drew Well, why did it stop working? AliR. "Drew" <dam@dam.dam> wrote in message news:ul$zFMjyHHA.1208@TK2MSFTNGP05.phx.gbl... > What do I need to do to regain this functionality? > > Thanks, > Drew > Maybe if you can tell us how to recreate it. AliR. "AliR (VC++ MVP)" <AliR@online.nospam> wrote in message news:FEOni.22648$RX.6967@newssvr11.news.prodigy.net... > Well, why did it stop working? > > AliR. > > > "Drew" <dam@dam.dam> wrote in me...

Easy way to go from WLM to Outlook 2007
Is there an easy way to transfer 3 email accounts, calendars, events etc. over to Outlook from Windows Live Mail? Thank you. "Michael H. Lewis" <mikalis@comcast.net> wrote in message news:h58jo0$mi7$1@news.eternal-september.org... > Is there an easy way to transfer 3 email accounts, calendars, events etc. > over to Outlook from Windows Live Mail? > > Thank you. Are you using WLM for Hotmail? Hotmail and two other email accounts. I know about Outlook Connector(?). "Gordon" <gordonbparker@yahoo.com> wrote in message news:E10880A8-CD32-43...

Attempting to create a CRM Test Environment
I am attempting to create a CRM test environment using the database name "Adventure Works Cycle". After the database is created and the users added I have attempted to import the XML schema and form changes from our Production Environment but everytime I do this I recieve the error "An Error has occurred. For more information, contact your system administrator". This error appears when I click on the "Services" tab. I know that it is attempting to query the database at this time. I am able to click on the "New Case" button and bring up the p...

controling excel from vba access and using sub procedures
Hi All, I'm having trouble getting my code to work using sub procedures in my vba code, this only happens when i am interacting with excel. I've tried to copy all the dims and defs from the calling procedure but to no avail at this time. sample code below. Dim strPath As String Dim rst As DAO.Recordset Dim Excel_Application As Excel.Application Dim Excel_Workbook As Excel.Workbook Dim Current_Worksheet As Excel.Worksheet Dim Data_Range Dim Worksheet_Name Dim db As Database Dim rs As Recordset D_now = Format(Date, "dd-mm-yy") ' Formated to use as part of fi...

CMSFlexGrid control
Hi Group I have created a dll which houses dialogs and controls for all my application. I have a cmsflexgird control in one of a CPropertyPage dialog. I find i am unable to acess this control directly from my main application program. where else i am able to acess other standard controls& variables from the same property page. Currently iam acessing this control only from the interface class of the dll. -- Regards Did you remember to export the flexgrid classes that the ide created for you in your dll? Ali R. "beauwlf" <jamesg@pd.jaring.my> wrote in message new...

How to create a "really" transparence MFC control?
I wrote a D3D app based on the MFC dialog,the app's draw frequency is 30FPS.It has a main dialog,i create the D3D device on it,also i create some sub dialogs and controls on this main dialog.When i run this app, some errors appear.When i click or move the sub dialogs and controls it reflash on the background(I think the MFC's draw frequency unequal to the D3D app's draw frequency cause this problem), this bring on the bad effect.Is there any methods let this sub dialogs and controls didn't reflash or redraw themself to be "really" transparence:only receive or send...

Hide subforms control from main forms control
I have no idea how to hide some controls in my subform if value in a textbox in main form is >1 or =1.I can't write a code. Please help. Ciao New_Access wrote: > I have no idea how to hide some controls in my subform > if value in a textbox in main form is >1 or =1.I can't write a code. > Please help. Private sub form_current() if youControlName => "1" then forms!NameForm!SubForm.form.YouControlName.visible = false end if end sub -- Ciao Geppo geppo, why does access think my FormName is a fieldname. "geppo" <ccc@tin.it.inva...

ActiveX control problem
Sometime ago I created an OCX control in Visual Basic 6. It's basically two VB controls in one with some added features. I have been using it in a CDialog class and dropped in the dialog. It works fine on a Win98 machine but when installed on a WinXP machine the dialog is not displayed. Nothing happens! The control is registered on the XP machine. Thanks, Mark > Sometime ago I created an OCX control in Visual Basic 6. It's basically > two VB controls in one with some added features. I have been using it in > a CDialog class and dropped in the dialog. It works fine on a Win...

insert WPF user control in ppt slide
Any way to insert WPF user control in ppt slide? Thanks, Vatia ...

control a sub/function via a dropdown menu
Hello, I have a dropdown menu on a chart. What must I do to control a sub/function depending on the selection of the dropdown menu. To make it clear .. every time I make a selection on the dropdown menu a function should be executed. Hope somebody can help me! Thanks in advance! daMike Mike - The dropdown is a Forms Toolbar dropdown, so you can right click on it to assign a macro. Also, you can link it to a cell, and the cell holds the index of the selected item. So your macro can check the index, then perform the appropriate actions. - Jon ------- Jon Peltier, Microsoft Excel MVP h...

Inserting additional data into an existing cell?
Excel for Dummies Question!: How do I insert additional data into an existing cell following my existing data and NOT replacing it? There are several different ways: 1. If you have text in a cell and want to append stuff to it, then do it in the formula bar. Click at the end of the old material in the formula bar and type the additional stuff in. 2. If you want to append stuff from another cell then, use concatinate =A1 & B1 and copy back inbto A1. 3. If you have numbers that you want to label, say "miles", then use custom formatting -- Gary's Student "CSJ5...

Way to put multiple rows into the same column.
I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. Hi, Try my EastyText_Rev1.xls at http://www.geocities.com/excelmarksway It might work, er, maybe, hmmm... - Mark >-----Original Message----- >I have data that is in the format of 3 columns wide by about 3000 r...

Graphic LCD ActiveX control
Hi, I'm looking for an ActiveX control which simulates an LCD panel and can be used to show different graphics within it. I've found one (Anon LCD, Puma) which can display only text, but no graphics in it. Appreciate if anyone could help me find one with graphic capabilities. Thanks, Sucharit ...

problem with clearing image in a CStatic control
Hi, I'm working on a code to load a preview image. I'm able to load the image in the control but unable to clear it. I'm developing something like an Open file dialog where in if I click a file I should see the preview of the file. Once I double click on a subfolder, it should show me the contents of the subfolder and reset the image in the Static control. Now I'm able to load my Bitmap using CStatic::SetBitmap(hBitmap). But once I click on a folder I should reset it to nothing and I dont know how to do that. Thank you. Rajesh. How about setting it to a bitmap that is 1x1 ...

Controlling Static Controls at Runtime
This is a multi-part message in MIME format. ------=_NextPart_000_0098_01C40088.4260AA00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need to change the properties of static text and groupbox at runtime = using VS .NET 2002. Specifically, I need to a.. Disable (gray out) b.. Enable c.. Hide (visible =3D false) d.. Show (visible =3D true) I've already changed the control ID to something other than IDC_STATIC = and created a control variable. ShowWindow(SW_HIDE) seems to have no = effect. Thanks. --=20 ~voidxor ------=_Next...

Edit control height after font change
Hi all, I'm trying to change the font at runtime for some controls (textbox, combobox, ...), I did it using SetFont method. The problem I have is that comboboxes resizes themselves after font changing, modifying their height in order to contain text with the new font, and that is fine. TextBox controls, instead, don't resize themselves; I can resize their height, but I dont't know exactly which new value I should use. I can try to get font size with GetTextMetrics and add some gap value in order to calculate the height to assign to textbox controls, but I don't like this ...

Any way to filter cells with formulas ?
Hi, I need to filter cells with formulas and not to select them , is that possible by a simple or advanced filter ? Thank you . -- gaftalik ------------------------------------------------------------------------ gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6450 View this thread: http://www.excelforum.com/showthread.php?threadid=488232 Maybe.... But I think you'll have to expand on what you really want... gaftalik wrote: > > Hi, > I need to filter cells with formulas and not to select them , is that > possible by a simple or a...

I can't select non adjacent cells using the control button
For some reason I am having a terrible time selecting or highlighting a group of cells and then using the control button and selecting another group of cells Are you selecting say A1:A3 then hold CTRL key and select D1:D3? What occurs when you try? How about hitting SHIFT + F8 then selecting A1:A3 and D1:D3? Gord Dibben MS Excel MVP On Sat, 19 Sep 2009 15:24:01 -0700, mustbesimple <mustbesimple@discussions.microsoft.com> wrote: >For some reason I am having a terrible time selecting or highlighting a group >of cells and then using the control button and selecting anothe...

test #45
test ...