Permanently change CheckBox control's value use VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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 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 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
 
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 said:
How can I permanently change a CheckBox control's value, which
Click to expand...
is located on a form, using VBA code?
Background:
I have created a splash screen form that appears when the
Click to expand...
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
Click to expand...
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
Click to expand...
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'
Click to expand...
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
Click to expand...
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.
 
Back
Top