How do i let the user change the start-up value of a control?

[Pastor]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a form named “fOptions”.

On fOptions I have a frame control named “frResolution”.

In frResolution are two option buttons named “obYes” and “obNo”.
<o> </o>
When the form was created, I set the obNo,value = FALSE and the obYes.value = TRUE which causes the obYes control to appear selected on the form.
<o> </o>
When the form is displayed the user may choose to click on the obNo control which will change its value to TRUE and the obYes control’s value to FALSE.
<o> </o>
Is there a way to save the obYes and obNo values in the controls so that the next time the control is started the new value appears?
<o> </o>
I am guessing that the code would look something like:
<o> </o>
{/CODE}
<o> </o>
‘Unload the form
Unload fOptions
<o> </o>
‘Select the form via the VBA environment

With Application.VBE .ActiveVBProject .VBComponents("fOptions")

‘Activate the form
.Activate

'Store the obYes value
.frResolution.obYes.Properties("Value") = fOptions.obYes.Value
<o> </o>
'Store the obNo value
.frResolution.obNo.Properties("Value") = fOptions.obNo.Value

End With
<o> </o>
Thisworkbook.Save
Thisworkbook.Quit
{/End Code}

 

[Gary McMaster]

I haven't seen your suggested approach before.

One way that is often suggested is to save the status of various controls on a hidden worksheet.

Another is to save the default(s) in the registry. If you search this forum for SaveSetting and/or GetSetting you'll find lots of examples.

Another is to save the defaults as custom document properties. Here's an example:
http://visualbasic.about.com/od/quicktips/qt/CustDocProps.htm

and more ...

The above methods rely on the Workbook_Open or Workbook_Activate event to retrieve/restore the saved setting(s) rather than trying to change the code via VBE.

Gary

 

[Pastor]

Hi Gary!

Thank you so much for leading me to the DOCUMENT PROPERTIES feature.

Sorry that it took me so long to respond to you but I only get about one day a week to work on my coding projects. Then it took me about four days to figure out how I could use the feature. And then another few days to code and example.

I'll pray that God continues to bless you with a generous heart to help poor souls like me. Thanks again.

For those who may follow I submit the following example in the hopes that it will help someone. I pray that it is complete; it worked for me...

God Bless,
Pastor

 [/FONT]
[FONT=Arial]'***********************************************************
    'THIS IS AN EXAMPLE OF HOW TO ALLOW THE USER TO CHANGE THE
    'STARTUP VALUES OF TWO OPTION BUTTIONS IN A FRAME ON A FORM
    'USING TWO CUSTOM DOCUMENT PROPERTIES.
    '***********************************************************
    'Create a form named: fOptions.
    '
    'On fOptions create a frame named frTest.
    '
    'In the frame named frTest create two option buttons;
    'one is named obYes and the other is named obNo.
    '
    'On foptions creat a command button name cbSave with a
    'caption of: SAVE.
    '
    '***********************************************************
    'In the fOptions form's code segment code the following
    'three lines...
    '
    'Public Sub cbSave_Click()
    '    cbSave_Click_Code
    'End Sub
    '
    '***********************************************************
    'In ThisWorkbook's code module code the following...
    '
    'Private Sub Workbook_Open()
    '
    '    'Verify that the Custom Document Property named
    '    'cdpYES exists
    '    Verify_That_cdpYes_Exists
    '
    '    'Verify that the Custom Document Property named
    '    'cdpNo exist
    '    Verify_That_cdpNo_Exists
    '
    '    'Display the form named fOptions
    '    Display_fOptions
    '
    'End Sub[/FONT]
[FONT=Arial]    '***********************************************************
    'In a code module code the following four subroutines...
    '
    'This routine is called to verify that a custom document
    'property exists for the option button named obYes; if one
    'does not exist then one is created.
    Public Sub Verify_That_cdpYes_Exists()
        
        'Define a PUBLIC Custom Document Property variable
        Dim dpTemp As DocumentProperty[/FONT]
[FONT=Arial]        'After the SET statement, if there is an error then
        'resume prosessing with the "If ERR.Number..." statement
        On Error Resume Next
        
        'Try to retrieve the custom document property value
        'into a public variable
        Set dpTemp = _
            ThisWorkbook.CustomDocumentProperties("cdpYes")
    
        'Check to see if an error has occurred
        If Err.Number > 0 Then[/FONT]
[FONT=Arial]           'If the the previous SET command caused an error,
           'then create the custome document property variable
           ThisWorkbook.CustomDocumentProperties.Add _
                Name:="cdpYes", _
                LinkToContent:=False, _
                Type:=msoPropertyTypeBoolean, _
                Value:=True
            
        End If
    End Sub
    
    'This routine is called to verify that a custom document
    'property exists for the option button named obNo; if one
    'does not exist then one is created.
    Public Sub Verify_That_cdpNo_Exists()
        
        'Define a PUBLIC Custom Document Property variable
        Dim dpTemp As DocumentProperty[/FONT]
[FONT=Arial]        'After the SET statement, if there is an error then
        'resume prosessing with the "If ERR.Number..." statement
        On Error Resume Next
        
        'Try to retrieve the custom document property value
        'into a public variable
        Set dpTemp = _
            ThisWorkbook.CustomDocumentProperties("cdpNo")
    
        'Check to see if an error has occurred
        If Err.Number > 0 Then[/FONT]
[FONT=Arial]           'If the the previous SET command caused an error,
           'then create the custome document property variable
           ThisWorkbook.CustomDocumentProperties.Add _
                Name:="cdpNo", _
                LinkToContent:=False, _
                Type:=msoPropertyTypeBoolean, _
                Value:=False
            
        End If
    End Sub
    
    'This routine loads the fOption form, initializes the Yes
    'and no option buttons and then shows the form to the user.
    Public Sub Display_fOptions()
        
        'Load the form named fOptions
        Load foptions
        
        'Initialize the obYes control from the Custom Document
        'Property named cdpYes
        foptions.obYes = _
            ThisWorkbook.CustomDocumentProperties("cdpYes")
        
        'Initialize the obNo control from the Custom Document
        'Property named cdpNo
        foptions.obNo = _
            ThisWorkbook.CustomDocumentProperties("cdpNo")
            
        'Display the form
        foptions.Show
        
    End Sub
    
    'When the Uer pressed the SAVE button this routine is run
    'to store the Yes and No option button values in the
    'corresponding custom document properties.
    Public Sub cbSave_Click_Code()
           
        'Save the obYes value
        ThisWorkbook.CustomDocumentProperties("cdpYes") = _
            foptions.obYes
            
        'Save the obNo value
        ThisWorkbook.CustomDocumentProperties("cdpNo") = _
            foptions.obNo
            
        'Issue a message
        MsgBox "The Yes and No option button values have " & _
               "been saved."
            
    End Sub
    
[/FONT]
[FONT=Arial]