Master reset code -stumped
 

I have a worksheet with 30 command buttons that open up 30 unique
UserForms.

Each UserForm has a reset command button that resets all textbox values
etc. to default settings.

I would like to have a 'Master Reset' command button on the worksheet
that resets all of the 30 UserForms to their default settings. Given
that the 'reset' code is different for each UserForm, is there a way do
accomplish this?

Any assistance much appreciated

Cheers

Peter (new too VBA)


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

You could have some code like the following for each userform

Load UserForm1
UserForm1.cmdReset_Click
Unload UserForm1

but you will need to make the rest button click procedures public.

Unfortunately you cannot use the Userforms collection, as the userforms do
not become part of the collection until they are loaded.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"peter.thompson"
<peter.thompson.218kja_1136589602.0717@excelforu m-nospam.com wrote in
message news:peter.thompson.218kja_1136589602.0717@excelfo rum-nospam.com...

I have a worksheet with 30 command buttons that open up 30 unique
UserForms.

Each UserForm has a reset command button that resets all textbox values
etc. to default settings.

I would like to have a 'Master Reset' command button on the worksheet
that resets all of the 30 UserForms to their default settings. Given
that the 'reset' code is different for each UserForm, is there a way do
accomplish this?

Any assistance much appreciated

Cheers

Peter (new too VBA)


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

Thanks Bob,

I get the idea - however I get an error "Method or data member' not
found when using the suggested code

The CommandButton name on the Userfrom is "Reset" I must be missing
something basic??

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

Hi Peter,

Presumably you are only resetting loaded userforms.

Sub Test()
Dim uf As UserForm
Dim u As Long
For Each uf In UserForms
UserForms(u).CommandButton1_Click
u = u + 1
Next
End Sub

Assumes each Userform has a routine named "CommandButton1_Click" which is
Public. (I used the click event of CommandButton1 but doesn't need to be a
control event, just a normal Sub, but must be Public). Note the index of the
first form in the collection of loaded forms is 0.

Regards,
Peter T

"peter.thompson"
wrote in
message news:peter.thompson.218mun_1136592604.647@excelfor um-nospam.com...

Thanks Bob,

I get the idea - however I get an error "Method or data member' not
found when using the suggested code

The CommandButton name on the Userfrom is "Reset" I must be missing
something basic??

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

Peter,

Can't get it to work..I'm missing something basic, I'm sure

The master reset commandbutton is in the worksheet

I have the following code in the worksheet

Sub CommandButton37_Click()
Dim uf As UserForm
Dim u As Long
For Each uf In UserForms
UserForms(u).Reset_Click
u = u + 1
Next

End Sub

In each userform the reset code is similar to:

Sub Reset_Click()
txtStaff.Text = ""
txtSalary.Value = ""
cbSalInc.Value = False
txtSavings.Value = 0
txtLife.Value = 0
txtComment.Value = ""

End sub

Cheers

Peter (new to VBA)


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

Did you make the reset button click procedure public as I said, and if the
button is not called cmdReset you will need to adapt to yours.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"peter.thompson"
wrote in
message news:peter.thompson.218mun_1136592604.647@excelfor um-nospam.com...

Thanks Bob,

I get the idea - however I get an error "Method or data member' not
found when using the suggested code

The CommandButton name on the Userfrom is "Reset" I must be missing
something basic??

Cheers

Peter


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898

Master reset code -stumped
 

Now know what 'Public' means! Works now.

Thanks for your perseverance!

Cheers

Peter (new to VBA)


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=498898