Saving Toggle Button Changes After Exiting Excel

[siamadu]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

How do you revise the above code to keep the current changes after exiting or closing Excel.

This is what is currently happening with this code and my toggle buttons

1. I push the toggle buttons "in"
2. I save my changes
3. I close the spreadsheet
4. I reopen the spreadsheet
5. The toggle buttons are in the "out" position
6. It does not recognize my previous change.

I do I fix this problem. I really need this to save(remember) my toggle buttons in the position that I "set" each time I go in and out of the spreadsheet.

 

[Gary McMaster]

You will have to manage the state of the buttons yourself.

If you want to save them in the registry you can use "SaveSetting" & "GetSetting" see VBA help.

If you want to save them with the workbook you can use "CustomDocumentProperties"

Gary

 

[siamadu]

I am new to programming with VBA. How would I use this CustomDocumentProperties to revise my original code.

Please help??

 

[Gary McMaster]

It goes something like the sample below.

I hope it helps you.

Gary

Public Sub Test()
 
Dim oActBook As Workbook
Dim sButton1 As String
Dim sButton2 As String
 
Set oActBook = ThisWorkbook
 
On Error Resume Next
 
'Try to read the custom property value "Button1", if error then
'create it and set its default value to "Off"
sButton1 = oActBook.CustomDocumentProperties("Button1").Value
If Err Then
    Err.Clear
    With oActBook.CustomDocumentProperties
        .Add Name:="Button1", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
    'Set the associated variable
    sButton1 = "Off"
End If

'Try to read the custom property value "Button2", if error then
'create it and set its default value to "Off"
sButton2 = oActBook.CustomDocumentProperties("Button2").Value
If Err Then
    Err.Clear
    With oActBook.CustomDocumentProperties
        .Add Name:="Button2", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
    'Set the associated variable
    sButton2 = "Off"
End If
 
'Set status of custom properties as buttons are switched
'This will likely be in one of the button events (eg "Click")
oActBook.CustomDocumentProperties("Button1").Value = "On"
oActBook.CustomDocumentProperties("Button2").Value = "On"

'Not really needed here but good habit
On Error GoTo 0
 
End Sub

 

[Gary McMaster]

Sorry, forgot the closing code tag. Hope this is easier to read.

Public Sub Test()
Dim oActBook As Workbook
Dim sButton1 As String
Dim sButton2 As String
Set oActBook = ThisWorkbook
On Error Resume Next
'Try to read the custom property value "Button1", if error then
'create it and set its default value to "Off"
sButton1 = oActBook.CustomDocumentProperties("Button1").Value
If Err Then
    Err.Clear
    With oActBook.CustomDocumentProperties
        .Add Name:="Button1", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
    'Set the associated variable
    sButton1 = "Off"
End If

'Try to read the custom property value "Button2", if error then
'create it and set its default value to "Off"
sButton2 = oActBook.CustomDocumentProperties("Button2").Value
If Err Then
    Err.Clear
    With oActBook.CustomDocumentProperties
        .Add Name:="Button2", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
    'Set the associated variable
    sButton2 = "Off"
End If
'Set status of custom properties as buttons are switched
'This will likely be in one of the button events (eg "Click")
oActBook.CustomDocumentProperties("Button1").Value = "On"
oActBook.CustomDocumentProperties("Button2").Value = "On"

'Not really needed here but good habit
On Error GoTo 0
End Sub

 

[siamadu]

1. I have copied and pasted the code below to Module1.

2. I named and saved the workbook as "oActBook"

3. I created a userform with two toggle buttons. The Userform is named Userform1

4. I named the two toggle buttons "Button1" and "Button2"

How do I get the code to work with the toggle buttons on the user form?

 

[Gary McMaster]

#1 - My first choice would be to place the code in the "Workbook_Open" event. The part down to the "end if" statement just before 'Set status of custom properties as buttons are switched

#2 You don't have to name the workbook oActive. It can be anything.

#4 The toggle buttons don't have to be named "Button1" & "Button2". It can be anything.

Add two variables "Public sButton1 as string" & "Public sButton2 as string" to the "Declarations" section of your module.


When you show (Activate) the form you will set your buttons to whatever is contained in sButton1 & sButton2 (On or Off). Use the contents of the two variables to set your buttons in the form "Activate" event. If you don't want to use the variables you can read the values directly from the custom properties.

The very first time you run it they will both be off. After that they should be the same as the custom properties which will be whatever you set them to each time the button is clicked.

Somewhere in your "button handling" code, if the button is supposed to be on you will use:

oActBook.CustomDocumentProperties("Button1").Value = "On"
sButton1 = "On"

If it is supposed to be off:

oActBook.CustomDocumentProperties("Button1").Value = "Off"
sButton1 = "Off"

If the button is on then shut it off. If it is off then turn it on.

When you first open the workbook the variables "sButton1" & "sButton2" will be set to "On" or "Off" depending on where they were when the workbook was saved (the code in the workbook_open event). If they don't exist (first run) they will be created and both set to "Off".

 

[Traines]

I am by no means an Excel guru but I have had a need to remember and store ActiveX control values in my work so this is what I do.

I create a 'named range' to act as a permanent static storage for a value. I would initialize the named range with a statement like:

Worksheets("Sheet1").Names.Add Name:="ToggleButton", RefersTo:="True"

This would only need to be done once so put in a macro called 'Init' or something and run it. Now the name 'ToggleButton' can be found in the Name Manager of MSExcel2007 (not sure that 2003 version has a Name Manager) along with the associated value of "True".

Then in your code for the togglebutton I would put a test statement like this:

If (Evaluate("ToggleButton") = True Then
  Do something
End If

Then at the end of the function I would set the named range to the new value of the togglebutton like this:

Names("ToggleButton").RefersTo = ToggleButton1.Value

This is based on the use of ToggleButton1, of course.

This technique is discussed in MrExcel's book "VBA and Macros in Microsoft Office Excel 2007", page 149

I hope this helps.

 

[Gary McMaster]

Okay, here is the whole thing. Sorry if I have confused you. I took out variables that were not needed. I originally included them thinking it would clarify things for you.

Notice that there are 4 procedures. They are all events and should already exist in your project. You should be able to paste the code right into the indicated events and get it working.

A better choice for these "Toggle buttons" would probably be a boolean property using True/False rather than the string On/Off that I chose. I had never used a toggle button before and didn't realize that it's value property was True/False. Nonetheless, it should work as is.

Remember to save, close, and reopen the workbook before you try and run it. Alternatively, you could create the two custom properties via the Excel GUI and skip the save, close, reopen.

Hope it helps.

Gary

Private Sub Workbook_Open()
 
Dim sTemp As String
 
On Error Resume Next

'Try to read the custom property value "Button1", if error then
'create it and set its default value to "Off"
sTemp = ThisWorkbook.CustomDocumentProperties("Button1").Value
If Err Then
    Err.Clear
    With ThisWorkbook.CustomDocumentProperties
        .Add Name:="Button1", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
End If
 
'Try to read the custom property value "Button2", if error then
'create it and set its default value to "Off"
sTemp = ThisWorkbook.CustomDocumentProperties("Button2").Value
If Err Then
    Err.Clear
    With ThisWorkbook.CustomDocumentProperties
        .Add Name:="Button2", _
        LinkToContent:=False, _
        Type:=msoPropertyTypeString, _
        Value:="Off"
    End With
End If
 
End Sub

 
Private Sub UserForm_Activate()
 
'Read the custom property "Button1" and set the button accordingly
If ThisWorkbook.CustomDocumentProperties("Button1").Value = "On" Then
    Me.Button1.Value = True
Else
    Me.Button1.Value = False
End If
 
'Read the custom property "Button2" and set the button accordingly
If ThisWorkbook.CustomDocumentProperties("Button2").Value = "On" Then
    Me.Button2.Value = True
Else
    Me.Button2.Value = False
End If
 
End Sub

 
Private Sub Button1_AfterUpdate()
 
If Button1.Value = True Then
    'Set the custom property value to "On"
    ThisWorkbook.CustomDocumentProperties("Button1").Value = "On"
Else
    'Set the custom property value to "Off"
    ThisWorkbook.CustomDocumentProperties("Button1").Value = "Off"
End If
 
End Sub

 
Private Sub Button2_AfterUpdate()
 
If Button2.Value = True Then
    'Set the custom property value to "On"
    ThisWorkbook.CustomDocumentProperties("Button2").Value = "On"
Else
    'Set the custom property value to "Off"
    ThisWorkbook.CustomDocumentProperties("Button2").Value = "Off"
End If
 
End Sub