Microsoft Office Forums looping controls on userforms

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2011, 03:54 AM
Cbrehm Cbrehm is offline looping controls on userforms Windows 7 64bit looping controls on userforms Office 2003
Novice
looping controls on userforms
 
Join Date: May 2011
Posts: 8
Cbrehm is on a distinguished road
Default looping controls on userforms

Excel 2002


On a userform I have some Textboxes, listboxes, etc that I want to set .value=empty

Currently I do it by
'clear some values
Code:
TSEFAN.Value = empty
    TSEFPD.Value = empty
    TSEFAP.Value = empty
Now I have tried,

Code:
Sub controlempty(formname As MSForms)
    'pass form name to use

Dim cCont As Control

    For Each cCont In formname

        .Value = Empty

    Next cCont

End Sub
then in the code for saving the data I have the line
Code:
controlempty ADDCORT
ADDCOURT is the name of the form

get error "Expected user-defined type, not project"

Help Thanks

Last edited by Cbrehm; 05-10-2011 at 05:02 AM. Reason: More info
Reply With Quote
  #2  
Old 05-10-2011, 06:16 AM
macropod's Avatar
macropod macropod is offline looping controls on userforms Windows 7 32bit looping controls on userforms Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,657
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Assuming you're using TextBoxes and you're calling the code from a button or something such on the form, you could implement code like:
Code:
Dim Obj As TextBox
For Each Obj In Me.Controls
  Obj.Text = vbNullString
Next
The above code will clear all textboxes. To clear just a limited set, you could test their names and delete via a Case statement.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-10-2011, 08:30 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline looping controls on userforms Windows 7 32bit looping controls on userforms Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi Cbrehm,

If you want to implement it like that then ControlEmpty should be a method in the userform class, so you shouldn't need to pass the instance of the userform into it. The method's signature would just be:
Code:
Sub ControlEmpty
(or perhaps you could call the procedure "ClearControls"?)

Now, if, for some reason, you did want to pass in a userform object (you don't here as I said above), then your procedure would be:
Code:
Sub controlempty(FormInstance As MSForms.Userform)
If you want to clear all the controls on the userform, then why not just create a new userform object?
Reply With Quote
  #4  
Old 05-10-2011, 01:17 PM
Cbrehm Cbrehm is offline looping controls on userforms Windows 7 64bit looping controls on userforms Office 2003
Novice
looping controls on userforms
 
Join Date: May 2011
Posts: 8
Cbrehm is on a distinguished road
Default

Like I said, I have comboboxes and listboxes and txtboxes, need to clear all
Reply With Quote
  #5  
Old 05-11-2011, 02:29 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline looping controls on userforms Windows 7 32bit looping controls on userforms Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Quote:
Originally Posted by Cbrehm View Post
Like I said, I have comboboxes and listboxes and txtboxes, need to clear all
Did you try this?
Quote:
Originally Posted by Colin
If you want to clear all the controls on the userform, then why not just create a new userform object?
Reply With Quote
  #6  
Old 05-11-2011, 02:53 AM
Cbrehm Cbrehm is offline looping controls on userforms Windows 7 64bit looping controls on userforms Office 2003
Novice
looping controls on userforms
 
Join Date: May 2011
Posts: 8
Cbrehm is on a distinguished road
Default

Yes Thank you, Sometime I over think things and make it do hard.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
looping controls on userforms how to use Building Blocks or controls to add a page verbster Word 11 03-06-2011 04:05 PM
looping controls on userforms Grouping Content Controls cksm4 Word VBA 2 03-01-2011 12:46 PM
looping controls on userforms Format picture controls are acting weird. WaltR Drawing and Graphics 1 02-11-2011 02:47 PM
looping controls on userforms hide ghosted controls designer PowerPoint 1 01-03-2011 07:11 AM
looping controls on userforms Can't Download Controls BillTheAdmin Project 2 12-05-2005 10:20 AM


All times are GMT -7. The time now is 06:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft