Saving change in Property (Help)

[dave8]

This is very simple, but I can't get it to work. I have a userform that contains two checkboxes. If the user clicks on one checkbox, I want to save this property so that the next time the workbook launches, it will not display the form. For example, when checkbox1 value is true, I save the workbook using ThisWorkbook.save. This doesn't work. So, how do I save the workbook when the property value of the checkbox changes?

 

[diddi]

you will need to save your preferences in a prefs file. you can open a file with an unusual extension to stop it looking like a text file if you want, and add an special icon too.

if you need more info, just ask

 

[mvptomlinson]

Another option is to store a value in the registry using RegRead and RegWrite commands as shown here:

http://www.excelforum.com/excel-pro...ing-specific-system-registry.html#post2372186

The next time you open the workbook, in the Open event check that value and act accordingly.

 

[dave8]

you will need to save your preferences in a prefs file. you can open a file with an unusual extension to stop it looking like a text file if you want, and add an special icon too.

if you need more info, just ask

I just want to be able to save the checkbox value, so that the next time I open the workbook the form will not get displayed again. When the form first gets displayed, there is a 'Yes' / 'No' checkbox. If 'Yes' is checked, the value of checkbox becomes True. I want to save the change in property value. So, when the workbook is opened again, I have a test condition to see if this checkbox is set to True.

 

[mvptomlinson]

Your options:
1. Save the checkbox value in a worksheet cell (make it hidden if you want)
2. Save the checkbox value in a text file
3. Save the checkbox value in the registry

You can't save the value directly on the form or in a variable, as those are unloaded when Excel closes. You may also be able to create a hidden object (label, textbox, etc) on the form that stores the value, e.g. setting the hidden label's caption to a specific value when the checkbox is checked.

 

[dave8]

Another option is to store a value in the registry using RegRead and RegWrite commands as shown here:

http://www.excelforum.com/excel-pro...ing-specific-system-registry.html#post2372186

The next time you open the workbook, in the Open event check that value and act accordingly.

I don't think the RegRead and RegWrite is going to work in my case. If I provide a new workbook, I want the form to be displayed again. The RegRead and RegWrite will prevent form to display on all subsequent workbooks.

 

[diddi]

using a worksheet cell however requires that you save the workbook, which you might not want to have to do. textfile or registry is neater choice, i think. but if you are saving the workbook all the time, then make sure that you protect the cell with the preferences in it.

 

[dave8]

I'm not saving the workbook everytime time. The code that saves the workbook is only when the checkbox value is set to True. But I can't get this to work.

 

[diddi]

in your userform call "MyWindow"...

Private Sub CheckBox2_Click()
    Dim res As String
    
    If checkbo2.Value = True Then
        res = MsgBox("This window will not appear again.  Is that what you want to do?", vbYesNo)
        If res = vbYes Then
            Open "C:\MyPrefs.txt" For Output As #1        'could put workbook name here
                Print #1, "Hidden"
            Close
        End If
    End If
End Sub

In your module...

Sub ShowWindow()
    Dim WindShow As String
    Open "C:\MyPrefs.txt" For Input As #1
        Input #1, WindShow
    Close
    If WindShow <> "Hidden" Then MyWindow.Show
End Sub

 

[mikerickson]

You might put this code in the userform's module. It has the feature of allowing one to change the Custom Document Property "ShowForm" to Yes if one wants the userform to be seen again.

Private Sub CheckBox1_Click()
    ShowDocProperty.Value = Not CheckBox1.Value
End Sub

Private Sub UserForm_Initialize()
    With CheckBox1
        .Value = Not ShowDocProperty.Value
        If .Value Then
            With .Parent
                .StartUpPosition = 0
                .Top = 0: .Left = 0
                .Height = 0: .Width = 0
            End With
        End If
    End With
End Sub

Private Sub UserForm_Activate()
   If CheckBox1.Value Then Me.Hide
End Sub

Function ShowDocProperty() As DocumentProperty
    Const PropertyName As String = "ShowForm"
    
    Rem retrieve custom document property as an object
    On Error GoTo MakeProperty
    Set ShowDocProperty = ThisWorkbook.CustomDocumentProperties(PropertyName)
    On Error GoTo 0

Exit Function
MakeProperty:
    If Err = 5 Then
        Rem if property does not exist, make one
        With ThisWorkbook.CustomDocumentProperties
            Set ShowDocProperty = .Add(Name:=PropertyName, _
                LinkToContent:=False, Type:=msoPropertyTypeBoolean, Value:=True)
        End With
    Else
        MsgBox Err & vbCr & Error
        End
    End If
End Function