looping controls on userforms

Cbrehm · #1 05-10-2011, 03:54 AM

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

macropod · #2 05-10-2011, 06:16 AM

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.

Colin Legg · #3 05-10-2011, 08:30 AM

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?

Cbrehm · #4 05-10-2011, 01:17 PM

Like I said, I have comboboxes and listboxes and txtboxes, need to clear all

Colin Legg · #5 05-11-2011, 02:29 AM

Quote:

Originally Posted by Cbrehm

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?

Cbrehm · #6 05-11-2011, 02:53 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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