Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically close form before closing | Excel Discussion (Misc queries) | |||
Cant get a combo box to update without closing form | Excel Discussion (Misc queries) | |||
Closing a Form | Excel Discussion (Misc queries) | |||
need form to initialize without closing | Excel Programming | |||
userform titlebar and closing form | Excel Programming |