Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Permanently change CheckBox control's value use VBA
How can I permanently change a CheckBox controls value, which is located on a form, using VBA code
Background I have created a splash screen form that appears when the workbook is first opened followed by a custom utility. Currently the splash screen can be removed by pressing the esc key or it will automatically be removed after approximately 5 seconds at which time the users can use the utility. I would like to give the user an option of permanently removing the splash screen so I added a CheckBox control to the form, whereby if the CheckBox is empty then the Splash Screen would perform as before. But if the CheckBox contains a check mark then the Splash Screen would be removed permanently and utility would appear by itself The users can also access the utility through the Tools menu where I added a custom CommandBar routine to bring up the tool. The splash screen appears before the utility but I would like process to change depending on what option is used on the splash scree Currently the default value for the CheckBox is false. With is false value, all the functionality works the way it was originally designed. I have placed a routine in the CheckBox click event which checks to see if a value is true then perform some action. Quickly I realized that when I closed the workbook and reopened it that the CheckBox value showed the initial false value rather than the changed true value. Located in ThisWorkbook code module Private Sub Workbook_Open( If frmSplashScreen.cbDoNotShowSplashScreen.Value The CreateMen frmDataTool.Sho Els CreateMen frmSplashScreen.Sho frmDataTool.Sho End I End su So I got the bright idea to set the CheckBox value in the forms click event. But that did not work either. Then thought maybe I needed to save the workbook after it was changed but again it did not work. Private Sub cbDoNotShowSplashScreen_Click( If cbDoNotShowSplashScreen.Value The Debug.Print frmSplashScreen.cbDoNotShowSplashScreen.Valu cbDoNotShowSplashScreen.Value = Tru ThisWorkbook.Sav Unload frmSplashScree End I End Su If I change the value using the properties and save the workbook, then everything works as intended but I lose the functionality of letting the users decided whether they want the splash screen to show. I even tried a global variable but it also revered back to the default value when the workbook reopened. I could do with some assistance with is issue and thank you in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Permanently change CheckBox control's value use VBA
Use the SaveSetting and GetSetting functions to store a value in
the registry. Before the form loads, call GetSetting to determine the value, and if True, don't show the form. When the form is closed, save the setting of the checkbox with SaveSetting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ilona" wrote in message ... How can I permanently change a CheckBox control's value, which is located on a form, using VBA code? Background: I have created a splash screen form that appears when the workbook is first opened followed by a custom utility. Currently the splash screen can be removed by pressing the esc key or it will automatically be removed after approximately 5 seconds at which time the users can use the utility. I would like to give the user an option of permanently removing the splash screen so I added a CheckBox control to the form, whereby if the CheckBox is empty then the Splash Screen would perform as before. But if the CheckBox contains a check mark then the Splash Screen would be removed permanently and utility would appear by itself. The users can also access the utility through the "Tools" menu where I added a custom CommandBar routine to bring up the tool. The splash screen appears before the utility but I would like process to change depending on what option is used on the splash screen Currently the default value for the CheckBox is false. With is false value, all the functionality works the way it was originally designed. I have placed a routine in the CheckBox click event which checks to see if a value is true then perform some action. Quickly I realized that when I closed the workbook and reopened it that the CheckBox value showed the initial false value rather than the changed true value. Located in ThisWorkbook code module: Private Sub Workbook_Open() If frmSplashScreen.cbDoNotShowSplashScreen.Value Then CreateMenu frmDataTool.Show Else CreateMenu frmSplashScreen.Show frmDataTool.Show End If End sub So I got the bright idea to set the CheckBox value in the form' s click event. But that did not work either. Then thought maybe I needed to save the workbook after it was changed but again it did not work. Private Sub cbDoNotShowSplashScreen_Click() If cbDoNotShowSplashScreen.Value Then Debug.Print frmSplashScreen.cbDoNotShowSplashScreen.Value cbDoNotShowSplashScreen.Value = True ThisWorkbook.Save Unload frmSplashScreen End If End Sub If I change the value using the properties and save the workbook, then everything works as intended but I lose the functionality of letting the users decided whether they want the splash screen to show. I even tried a global variable but it also revered back to the default value when the workbook reopened. I could do with some assistance with is issue and thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i permanently change the page setup in excel? | Excel Discussion (Misc queries) | |||
how to Permanently change default font for comments | Excel Discussion (Misc queries) | |||
How do I permanently change the Microsoft Office author? | Excel Discussion (Misc queries) | |||
How do I change $ to £ permanently in Excel? | Excel Discussion (Misc queries) | |||
how do I change a cell permanently to PM from AM? | Excel Discussion (Misc queries) |