You may want to use current session's data in the next session of Excel VBA program. This requires to save the current session's data in your machine and retrieve the data for next session as a staring point rather than defining the new data. To do so, data can be stored in Windows Registry. Registry database is used to store settings to read values as new default for next operation.
Registry database is used to store settings and to read values as new default for next Excel session.
In order to write value to registry at specified location to maintain between sessions, SaveSetting method is used:
Sub SaveSetting(AppName As String, Section As String, Key As String, Setting As String)
GetSetting method is to retrieve the value stored in the registry.
GetSetting(AppName As String, Section As String, Key As String, [Default]) As String
It is apparent to get setting from Registry when new session starts and save setting when current session is finished.
Option Explicit
Option Compare Text
Private Pos As String, T1 As Long, T2 As Long, T3 As Long, T4 As Long
Private T5 As Long, T6 As Long, T7 As Long, T8 As Long
Private T9 As Long, T10 As Long, T11 As Long, T12 As Long, T13 As String, T14 As String
Sub Getting_Registry(Pos, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14)
Pos = GetSetting("Register_Demo", "PPSetting", "Pos", Pos)
T1 = GetSetting("Register_Demo", "PPSetting", "T1", T1)
T2 = GetSetting("Register_Demo", "PPSetting", "T2", T2)
T3 = GetSetting("Register_Demo", "PPSetting", "T3", T3)
T4 = GetSetting("Register_Demo", "PPSetting", "T4", T4)
T5 = GetSetting("Register_Demo", "PPSetting", "T5", T5)
T6 = GetSetting("Register_Demo", "PPSetting", "T6", T6)
T7 = GetSetting("Register_Demo", "PPSetting", "T7", T7)
T8 = GetSetting("Register_Demo", "PPSetting", "T8", T8)
T9 = GetSetting("Register_Demo", "PPSetting", "T9", T9)
T10 = GetSetting("Register_Demo", "PPSetting", "T10", T10)
T11 = GetSetting("Register_Demo", "PPSetting", "T11", T11)
T12 = GetSetting("Register_Demo", "PPSetting", "T12", T12)
T13 = GetSetting("Register_Demo", "PPSetting", "T13", T13)
T14 = GetSetting("Register_Demo", "PPSetting", "T14", T14)
End Sub
Sub Setting_Registry(Pos, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14)
SaveSetting "Register_Demo", "PPSetting", "Pos", Pos
SaveSetting "Register_Demo", "PPSetting", "T1", T1
SaveSetting "Register_Demo", "PPSetting", "T2", T2
SaveSetting "Register_Demo", "PPSetting", "T3", T3
SaveSetting "Register_Demo", "PPSetting", "T4", T4
SaveSetting "Register_Demo", "PPSetting", "T5", T5
SaveSetting "Register_Demo", "PPSetting", "T6", T6
SaveSetting "Register_Demo", "PPSetting", "T7", T7
SaveSetting "Register_Demo", "PPSetting", "T8", T8
SaveSetting "Register_Demo", "PPSetting", "T9", T9
SaveSetting "Register_Demo", "PPSetting", "T10", T10
SaveSetting "Register_Demo", "PPSetting", "T11", T11
SaveSetting "Register_Demo", "PPSetting", "T12", T12
SaveSetting "Register_Demo", "PPSetting", "T13", T13
SaveSetting "Register_Demo", "PPSetting", "T14", T14
End Sub
Go to TopThe procedure below get settings saved in the registry or initial setting are saved and get the setting for the very first program run. Then the settings are used as starting point.
Option Explicit
Option Compare Text
Private Pos As String, T1 As Long, T2 As Long, T3 As Long, T4 As Long
Private T5 As Long, T6 As Long, T7 As Long, T8 As Long
Private T9 As Long, T10 As Long, T11 As Long, T12 As Long, T13 As String, T14 As String
Sub uf_REgister_Init()
Call Getting_Registry(Pos, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14)
If T1 = 0 Then
Call Setting_Registry("Opt1", 15, 10, 690, 80, 15, 100, 390, 400, 415, 100, 290, 400, "", "")
Call Getting_Registry(Pos, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14)
End If
With uf_Register
Select Case Pos
Case "Opt1"
.OptionButton1.Value = True
Case "Opt2"
.OptionButton2.Value = True
Case "Opt3"
.OptionButton3.Value = True
Case "Opt4"
.OptionButton4.Value = True
End Select
.TextBox1.Text = T1: .TextBox2.Text = T2: .TextBox3.Text = T3: .TextBox4.Text = T4
.TextBox5.Text = T5: .TextBox6.Text = T6: .TextBox7.Text = T7: .TextBox8.Text = T8
.TextBox9.Text = T9: .TextBox10.Text = T10: .TextBox11.Text = T11: .TextBox12.Text = T12
.TextBox13.Text = T13: .TextBox14.Text = T14
.CheckBox1.Value = False
.CommandButton1.Enabled = False
.Show
End With
End Sub
Go to TopBefore current session is finished, it is necessary save current settings so that the new setting are used as starting point. The data of this page are related to Userform; therefore the current settings are to be saved before Userform is unloaded. In order to ensure to save setting before userform, Close (X) mark at the right top of the userform should be disabled by a procedure Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Option Explicit
Private Pos As String, T1 As Long, T2 As Long, T3 As Long, T4 As Long
Private T5 As Long, T6 As Long, T7 As Long, T8 As Long
Private T9 As Long, T10 As Long, T11 As Long, T12 As Long, T13 As String, T14 As String
Private Sub CommandButton1_Click()
'OK button
'Your code here
End Sub
Private Sub CommandButton3_Click()
'Close button
Select Case True
Case OptionButton1.Value
Pos = "Opt1"
Case OptionButton2.Value
Pos = "Opt2"
Case OptionButton3.Value
Pos = "Opt3"
Case OptionButton4.Value
Pos = "Opt41"
End Select
T1 = TextBox1.Text: T2 = TextBox2.Text: T3 = TextBox3.Text: T4 = TextBox4.Text
T5 = TextBox5.Text: T6 = TextBox6.Text: T7 = TextBox7.Text: T8 = TextBox8.Text
T9 = TextBox9.Text: T10 = TextBox10.Text: T11 = TextBox11.Text: T12 = TextBox12.Text
T13 = TextBox13.Text: T14 = TextBox14.Text
Call Setting_Registry(Pos, T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14)
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent from accidental unloading the userform.
If CloseMode = vbFormControlMenu Then
MsgBox "Click the ""Close"" button to close the dialog"
Cancel = True
End If
End Sub
Go to Top
The Windows Registry Editor (regedit) is a graphical tool in the Microsoft Windows operating system (OS) that enables authorized users to view the Windows registry and make changes.
Settings registered are viewed using Registry Editor.
modified on: 14-Apr-2023