Muultiple userform 'reset' code question

**peter.thompson** · 10-17-2006, 04:29 AM

I have 30 commandbuttons on a page that open 30 different userforms.

On each userform there is a 'reset' command button that resets form data.

I have a 'master reset' commandbutton that resets data in all forms.

Currently I am using the code below to accomplish this. There has to be a better way. Any ideas would be appreciated.

If CommandButton1.BackColor = &HFFC0C0 Then
Load UserForm1
UserForm1.Reset_Click
Unload UserForm1
End If
If CommandButton2.BackColor = &HFFC0C0 Then
Load UserForm2
UserForm2.Reset_Click
Unload UserForm2
End If

etc. etc.

Cheers

PeterT

**raypayette** · 10-17-2006, 05:37 AM

The only potential flaw I saw is to replace Unload UserForm1
with UserForm1.Hide

**peter.thompson** · 10-17-2006, 06:26 AM

Thanks Ray,

If I hide the forms, as there are 30 of them, wouldn't that chew up a lot of memory resources?

Another question, is it necessary to 'load' the form to perform an action on it?

Cheers

Peter

**Aelyn** · 10-17-2006, 06:39 AM

I'm pretty new to excel, so you may want to take this with a pinch of salt, but would it be possible to do something like this:

Please Login or Register  to view this content.

I doubt it'll be much faster, but the code is nicer and it allows more flexibility if the code needs to be changed in the future.

Like I say though, I'm pretty new to excel - I don't know if this will actually work.

**peter.thompson** · 10-17-2006, 06:48 AM

Thanks Aelyn

Just tried the code is stops with a compile error on the "If CommandButtons" line

error says "fuction or Sub" not defined.

I'll play around with it a little more!

Cheers

Peter

**peter.thompson** · 10-18-2006, 09:29 PM

Still stuck with this, any ideas very welcome!

Cheers

Peter

**Bryan Hessey** · 10-18-2006, 10:20 PM

Originally Posted by peter.thompson

Still stuck with this, any ideas very welcome!

Cheers

Peter

Testing, for the loop, do you have buttons 1 to 30? - or have you deleted one within the range

Perhaps a 'is nothing' test is needed.

---

**peter.thompson** · 10-19-2006, 12:14 AM

Thanks Bryan

CommandButtons 1 to 30 are definitely there - the inefficient code that I'm using now (Using an 'If CommandButton1' etc. repeated 30 times works fine).

Go figure!

Stumped!

Cheers

PeterT

**peter.thompson** · 10-20-2006, 08:53 PM

Still stumped on this one - any ideas appreciated.

Cheers

Peter

**raypayette** · 10-20-2006, 08:58 PM

Your additional question:
Is it necessary to 'load' the form to perform an action on it?
Yes.
You repeat similar code 30 times, so you think this is inefficient. It isn't. If it ain't broken don't fix it!