Store a Checkbox true or false choice

[Jak]

Hello

I created an Excel Add-in.xla recently and added some code to load a Splash Screen at start up. No problems there. What I would like to do is offer the user a chance to display the splash screen or disable it each time the add-in starts up.

So far...

Once the splashscreen has finished a customised toolbar is added to the MS Toolbar. By selecting the appropriate choice from the custom menu the user can launch frmOptions. This form has CheckBox1 on it. If CheckBox1 is ticked I would like to "retain" the True value and by pass running the splash screen the next time the Add-in is run. This would involve checking frmOptions checkbox1 value. If the value was true then the code would jump past loading "FlashMe" (splash screen form) and run the rest of the code. Else the code would load the FlashMe form at start up. See code below:

Private Sub Workbook_Open()

If frmOptions.CheckBox1 = True Then GoTo ByPassIntro:

FlashMe.Show 'skip showing the splash screen

ByPassIntro:

'Run rest of code now

Requirement:

I need a way keep checkbox1 value depending on whether it is selected or de-selected so that my start up code can check this value and decide whether or not to load the splash screen. At the moment each time I select it and reload the add-in the checkbox reverts back to a de-selected (false) state. Please also note that this is all part off an add-in and I won't be saving the true or false value to a worksheet.

Any ideas or code would be welcomed.

Jak

 

[Jon Peltier]

You could save the value to a worksheet within the add-in or to a custom document property, but I think it's bad form to change the add-in once it's deployed. Better to save it to a workbook or text file that the add-in saves in its own directory, or to the system registry.

 

[Jak]

Hello Jon

I could add an additional checkbox to the form with some code to write 1 or 0 to a cell reference on a worksheet i.e. True or False and save this file on the users hard drive as hidden, then check the range value to see if the file and value exist on startup. However, I would like to be able to save the value to a worksheet within the add-in but am not sure how to write to the add-in worksheet. Any example of how to do this would be a help. The worksheet in question is called: Logos (Sheet1)

 

[Scott Huish]

Just as an aside to this, the GoTo is not necessary, just check for False instead:

Private Sub Workbook_Open()
'If Checkbox1 not checked, show flash screen
If frmOptions.CheckBox1 = False Then FlashMe.Show
'Run rest of code now

 

[Jon Peltier]

You reference a worksheet within an add-in the same as any worksheet, except it's never an active sheet.

ThisWorkbook.Worksheets("Logos").Range("A1").Value = True

 

[Jak]

Hello Jon

Thanks for the follow ups, very useful. I wil try saving a value to the add-in worksheet and checking for it at startup.

Again, thanks for the help and examples.

 

[Jak]

Hi I just thought I would post my final solution to my splash screen problem.

I tried saving a value to a userinfo.ini file on the hard drive but if it is deleted then this would cause problems with the splash screen code. I opted to save the value to activate or deactivate the splash screen within the addin. Thanks to Jon Peltier I got this up and running. This however lead to a problem in that the Add-in size increased in size when saving the value. This would eventually slow the loading of the add-in overtime and therefore I opted to save the value to the registery. This works very well, here is the code;

Private Sub Workbook_Open() 

' reads information in the Registry from
' HKEY_CURRENT_USERSoftwareVB and VBA Program Settings NCFE CSV TOOL to see if the value stored in the registry is a 0 or 1.
'If the value is 0 then no splash screen gets loaded.

On Error Resume Next
    If GetSetting("NCFE CSV TOOL", "Intro", "Flash", "") <> 1 Then
    GoTo ByPassIntro: 'bypass flash screen
On Error GoTo 0
End If
frmExample.Show 'run splash screen at startup
ByPassIntro:

'run the rest of the start up code
End sub

'User Form Code. 2 checkboxes. click to show or hide splash screen at start up.

Private Sub CheckBox1_Click()
' saves information in the Registry to
' HKEY_CURRENT_USERSoftwareVB and VBA Program Settings NCFE CSV TOOL
On Error Resume Next
    SaveSetting "NCFE CSV TOOL", "Intro", "Flash", "0"
    On Error GoTo 0
 Unload Me
End Sub

Private Sub CheckBox2_Click()
On Error Resume Next
    SaveSetting "NCFE CSV TOOL", "Intro", "Flash", "1"
    On Error GoTo 0
 Unload Me
    End Sub

 

[Legacy 98055]

This works very well

As long as the workbook is running on the same machine that contains the registry entries...

 

[Jak]

Hi Right_Click

The splash screen will load each time the add-in loads unless there is a registry value present. If the user loads the addin on a different machine they will need to deactivate it so that it does not load thereafter.

Works for me.