Simple Form questions

[Stuart]

A command button on a sheet presents a form (Frm_Initial) which has a
textbox displaying information for the user, plus Ok and Cancel buttons
(where Cancel should exit the routine, and Ok present the next form)....
except that neither button works.

The initial button on the sheet:
Private Sub CommandButton1_Click()
Load Frm_Initial
Frm_Initial.Show
End Sub

and the form code:
Private Sub Cb2_Cancel_Click()
'user clicked Cancel
Unload Frm_Initial
End Sub

Private Sub Cb1_Ok_Click()
'user clicked OK
'Unload Me
Frm_SelectCert.Show
End Sub

What am I doing wrong, please?

Regards.

 

[Stevie_mac]

Add a public Property to your forms called UserAcepted as Boolean
In the Click event for OK, add UserAcepted = True.
Replace Unload Me with Me.Hide
Where your forms are launch the 1st form, do this...

frm_Initial.Show
If frm_Initial.UserAcepted = True then
frm_SelectCert.Show
If frm_SelectCert.UserAcepted = True then
frm_whatever
End If
End If

Basically, don't unload forms & control the showing of forms from 1 point.

 

[Stuart]

Many thanks.

Basically, don't unload forms ...............

Q1. when do you? ... Do they need to be unloaded?

Basically, ......control the showing of forms from 1 point.

Q2. Assuming this is not an addin, would you advise one
standard module in the workbook to hold all the
controlling code, rather than sheet code?

Regards and Thanks

 

[Stevie_mac]

Q2. Assuming this is not an addin, would you advise one

standard module in the workbook to hold all the controlling code, rather than sheet code?

It depends on what you are doing. But personally, I like to have a Module named "Main" that does the overall control &
aptly named modules or classes of routines that perform related functions (or just 1 module called Aux if working on
smallish project)

Sometimes on very small projects where just a quick routine is required, I'll leave it in the sheet (no modules).

Sometimes, you must have code in a sheet (event handlers for example), but if the functions performed can be
parameterised & will be used in other functions or sheets, put that in a module or class. That way, if a problem exists
in your code, you only make the correction in one place.
Don't repeat the same code, parameterise it in a function, or build a class (if appropriate)
e.g.

...::Sheet Code::..
Private Sub UpdateCol_A
Aux.UpdateCol("A")
End Sub
Private Sub UpdateCol_B
Aux.UpdateCol("B")
End Sub

...::Module Aux ::..
Public Sub UpdateCol(col as String)
'Lots of stuff...
'Lots of formatting...
'Lots of clever things... '***Some problem here fixed.***
'Lots of calculations...
End Sub

Q1. when do you? ... Do they need to be unloaded?

You can (if you wish) unload forms when you are finished e.g.

frm_Initial.Show
If frm_Initial.UserAcepted = True then
frm_SelectCert.Show
If frm_SelectCert.UserAcepted = True then
frm_whatever
End If
End If

Unload frm_Initial
Unload frm_SelectCert
Unload frm_whatever

But is it even necessary? VB will clean up when excel is closed.
Its not like the forms you are loading will hold MB's of data in variables - are they?

It is kind of a judgement call, perhaps your forms loads data from a file - or DB. Perhaps it takes 5 seconds to load
that. Would you want to have that happen everytime the form is to be shown? I wouldn't, I'd simply hide the form.

If the forms are for input & you don't clear them explicitly (ie Textbox1.Text = "": List1.Clear etc) then when they
are shown again, they will hold the old input data. Now this sometimes is a bonus & other times a pain!

If the forms are quick loading & must be cleared, Unload them after use!

From the info above, you should be able to make your own judgement

Regards - Steve.

 

[Stuart]

Thanks for the advice and examples.
Clearer now.

Regards.