closing form semi permantly
 

VBA newbie needs your talent. Sounds like an ad in the
personals but.....
I have a workbook w/ 10 sheets or so. Each sheet shows a
form w/ sheet function descriptions and a few checkbox
choices that perform actions.All the actions can be
performed in other ways so I really don't NEED this form,
it's mostly for descriptive purposes for users. What I
need help w/ is that sooner or later users will get sick
of closing a form that opens every time they visit the
sheet. I just want a check box on the bottom of the form
ala MS and others that says "Don't show this message
again". I also would like it to reset everytime the
workbook is closed (or opened) for the next user.
Any help (and any education) is greatly appreciated.
jeffP

closing form semi permantly
 

You need a range somewhere (say cell AA1 on each sheet with a form), named "TestFormOpen1", "TestFormOpen2" etc. These will hold the value "TRUE" or "FALSE", depending on whether the form is going to be opened. Let's assume one of the sheets is called "Sheet1".

Let's call the checkbox on your form (the one that says "Don't show this message again") "cbxTest".

The idea is that we will open the form only if the range "TestFormOpen" has "TRUE" in it. This will happen if the user checks the box "cbxTest"

Let's assume your form is called "frmChoose".

Add code to the Worksheet_Activate event for the sheet in VBA as follows:

Private Sub Worksheet_Activate()
If Range("TestFormOpen1")=TRUE then frmChooose.show
End Sub

In the Code module behind the form, add this to the Click event of the OK button (I am assuming you have an "OK" button on it, called "btnOK":

Sub btnOK_Click()
If cbxTestFormOpen then Activesheet.Range("TestFormOpen1")="FALSE"
Unload Me
End sub

You need to repeat the above for each Sheet/Form combination.

Finally, in the Workbook_Close event in the "ThisWorkbook" module, add the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Range("TestFormOpen1")="TRUE"
frmChoose.cbxTestFromOpen=FALSE ...(repeat these lines for each sheet/form)
End Sub

I haven't tested any of this but the general idea should work.

Regards
Ian

"jeffP" wrote:

VBA newbie needs your talent. Sounds like an ad in the
personals but.....
I have a workbook w/ 10 sheets or so. Each sheet shows a
form w/ sheet function descriptions and a few checkbox
choices that perform actions.All the actions can be
performed in other ways so I really don't NEED this form,
it's mostly for descriptive purposes for users. What I
need help w/ is that sooner or later users will get sick
of closing a form that opens every time they visit the
sheet. I just want a check box on the bottom of the form
ala MS and others that says "Don't show this message
again". I also would like it to reset everytime the
workbook is closed (or opened) for the next user.
Any help (and any education) is greatly appreciated.
jeffP

closing form semi permantly
 

Hi JeffP,

I have a workbook w/ 10 sheets or so. Each sheet shows a
form w/ sheet function descriptions and a few checkbox
choices that perform actions.All the actions can be
performed in other ways so I really don't NEED this form,
it's mostly for descriptive purposes for users. What I
need help w/ is that sooner or later users will get sick
of closing a form that opens every time they visit the
sheet. I just want a check box on the bottom of the form
ala MS and others that says "Don't show this message
again". I also would like it to reset everytime the
workbook is closed (or opened) for the next user.
Any help (and any education) is greatly appreciated.

If you want the setting to reset with each workbook, I would store the
choice in a global variable:

'In a standard module:
Public gbSkipHelpForm As Boolean

'In the Sheet's Worksheet_Activate procedu
If Not gbSkipHelpForm Then
frmHelpForm.Show vbModeless
End If

'In the _Click event for the check box on the form:
gbSkipHelpForm = chkDontShowAgain.Value


If you want this to be remembered between sessions, I'd use SaveSetting
and GetSetting to store the choice in the registry.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie