Allow user to set and save a default option button.

[Jeff Showalter]

Win 2000 Prof. SP4
Office 2000 Prof.

I have an excel worksheet with 3 option buttons on it.
The Excel file will be read-only and distributed to several users
using
stand alone laptops.
By default the first option button is selected on open. That's how the
file will be saved.
I want different users to be able to change which option button will
be
selected when the Excel file opens so they don't have to click on a
different option button every time if they always want an option
selected other than option button 1.
I want the Excel file to hold a user selected default for a startup
option button so the next time the file is opened, their option button
is selected.

Thanks

 

[Dave Peterson]

First, you have a few choices on where to store this information.

You could stick it in the windows registry. Look at VBA's help for GetSetting
and SaveSetting if you want to use that.

I chose to use a text file in the same folder as the workbook.

I put 3 optionbuttons from the Forms toolbar on worksheet1.

I put this code into a general module in that workbook:

Option Explicit
Sub auto_open()

Dim myTextFileName As String
Dim myTextFileNum As Long
Dim myLine As String

myTextFileName = ThisWorkbook.Path & "\myOptions.txt"

If Dir(myTextFileName) = "" Then
'first time opened
Worksheets("sheet1").OptionButtons(1).Value = xlOn
Else
myTextFileNum = FreeFile
Close myTextFileNum
Open myTextFileName For Input As #myTextFileNum
Line Input #myTextFileNum, myLine
Close myTextFileNum

On Error Resume Next
ThisWorkbook.Worksheets("sheet1").OptionButtons(myLine).Value = xlOn
If Err.Number <> 0 Then
Debug.Print Err.Number & "--" & Err.Description & vbLf _
& myLine
Err.Clear
End If
On Error GoTo 0
End If

End Sub
Sub optClick()

Dim myTextFileName As String
Dim myTextFileNum As Long
Dim myLine As String

myTextFileName = ThisWorkbook.Path & "\myOptions.txt"
myTextFileNum = FreeFile

Close myTextFileNum
Open myTextFileName For Output As #myTextFileNum
Print #myTextFileNum, Application.Caller
Close myTextFileNum

End Sub

I rightclicked on each optionbutton and chose Assign macro and assigned it to
the OptClick macro.

It seemed to work ok for me when I tested it.

 

[BrianB]

I feel it makes things a lot simpler to save the setting in a hidden
worksheet in the same workbook using the Change event of the control.

 

[Dave Peterson]

Except in this case, the OP said the workbook was readonly.