UserForm remembering Settings

[jason]

Is it possible for a checkbox on a userform to be saved in the last
setting that the user left it in, when the user closes and saves the
workbook - without using any worksheet cells.

 

[Serge]

I am no MVP, but I'd say no. I would create
hidden "settings" worksheet and store user settings there.

 

[Chip Pearson]

Jason,

The setting would have to be stored somewhere, such as in a worksheet cell,
a defined name, or in the system registry. You can use SaveSetting to store
the check box state in the registry when the form is closed, and then use
GetSetting to retrieve the setting when the form is initialized. For
example,

Private Sub UserForm_Initialize()
Me.CheckBox1.Value = CBool(GetSetting("AppName", "Setup", _
"CheckBox1State", "False"))
End Sub

Private Sub UserForm_Terminate()
SaveSetting "AppName", "Setup", "CheckBox1State", _
Format(Me.CheckBox1.Value)
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[keepitcool]

you'll have to use the registry using SaveSettings/GetSettings
or else you can easily store an array of values in a hidden name object

Sub Store()
Dim v
v = Array(1, "john", True)
ActiveWorkbook.Names.Add "myhiddensettings", v, False
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[jason]

Cheers KIC

like your style - hidden named range

J

 

[jason]

KIC

If I use this code:

Sub Store()

Dim v

v = Array(True, False, True)
ActiveWorkbook.Names.Add "XXX", v, False

End Sub

then what code do I use to extract say the second entry in the array
(the False entry) ?

Jason

 

[keepitcool]

Jason, try:

Sub ReStore()
Dim v
v = Evaluate(ActiveWorkbook.Names("XXX").RefersTo)
msgbox "second entry is:" & v(2)
End Sub

pls note that Evaluate returns a 1based array!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >