CODEWITHSUNDEEP
excelvba
Evan West
Evan West

Reputation: 53

VBA User Form, how to store previously entered values for next open

So I have this user form that a user enters data in, some in combo boxes and some in text boxes. Then this data is filled to a sheet. What is the best way to get the user from to display this data again when it is opened next? So if a user enters "2014" in the Model/Launch year field and then hits submit and closes the from, next time they open it i want that field to say "2014" still. Code is below and works, it is probably messy and inefficient as im a newbie to coding and vba, im more concerned with getting this functionality to work here and not optimizing my code. Thanks!

enter image description here

'=========================================================Populating User Form Drop Downs============================================================
Private Sub UserForm_Initialize()

'populate OEM drop down
With Me.ComboBox1
    .AddItem "GM"
    .AddItem "Ford"
    .AddItem "FCA"
    .AddItem "Toyota"
    .AddItem "Nissan"
    .AddItem "Isuzu"
    .AddItem "Other"
End With

'populate Program stage dropdown
With Me.ComboBox4
    .AddItem "Screen Approval"
    .AddItem "Quote Approval"
    .AddItem "Program Start"
    .AddItem "Design Verification Release"
    .AddItem "Production Validation Release"
    .AddItem "Production Part Approval Process"
    .AddItem "Start of Production"
    .AddItem "Program Closure"
End With

'Populate Milestones

Dim M1 As Variant
M1 = Sheets("Monthly Status").Range("G23")
M13 = Sheets("Monthly Status").Range("G35")
M14 = Sheets("Monthly Status").Range("G36")
M15 = Sheets("Monthly Status").Range("G37")
M16 = Sheets("Monthly Status").Range("G38")
M17 = Sheets("Monthly Status").Range("G39")
M24 = Sheets("Monthly Status").Range("G46")
M25 = Sheets("Monthly Status").Range("G47")
M26 = Sheets("Monthly Status").Range("G48")
M37 = Sheets("Monthly Status").Range("G59")

'Populate OP drop down

With Me.ComboBox2
    .AddItem M1
    .AddItem M13
    .AddItem M14
    .AddItem M15
    .AddItem M16
    .AddItem M17
    .AddItem M24
    .AddItem M25
    .AddItem M26
    .AddItem M37
End With

End Sub
Private Sub Submit_Click()

'======================================================Filling Header Feilds From User Form==========================================================

Dim OEM As String
Dim Var As String
Dim Year As String
Dim Veh As String
Dim VehC As String
Dim DRE As String
Dim ID As String
Dim Unit As String
Dim AAE As String
Dim DTE As String
Dim Stage As String

'Fill OEM box
    OEM = ComboBox1.value
    Sheets("Monthly Status").Range("E7:G7") = OEM

'Fill Variant(s) box
    Var = TextBox2.value
    Sheets("Monthly Status").Range("E8:G8") = Var

'Fill Model / Launch Year
    Year = TextBox3.value
    Sheets("Monthly Status").Range("I7") = Year

'Fill Vehicle(s) Box
    Veh = TextBox4.value
    Sheets("Monthly Status").Range("I8:L8") = Veh

'Fill Vehicle Code(s) Box
    VehC = TextBox5.value
    Sheets("Monthly Status").Range("L7:O7") = VehC

'Fill Acoustics DRE Box
    DRE = TextBox6.value
    Sheets("Monthly Status").Range("R7:S7") = DRE

'Fill Program ID Box
    ID = TextBox7.value
    Sheets("Monthly Status").Range("R8:S8") = ID

'Fill Reporting Region/Business Unit
    Unit = TextBox8.value
    Sheets("Monthly Status").Range("R12") = Unit

'Fill Assigned AAE
    AAE = TextBox9.value
    Sheets("Monthly Status").Range("R13") = AAE

'Fill Assigned DTE
    DTE = TextBox10.value
    Sheets("Monthly Status").Range("R14") = DTE

'Full Current TenPLUS Program Stage
    Stage = ComboBox4.value
    Sheets("Monthly Status").Range("R16") = Stage

End Sub

Upvotes: 0

Views: 1389

Answers (2)

simple-solution
simple-solution

Reputation: 1139

During initalization you load the values you have saved earlier e.g.

TextBox1.Text = "Selected Vehicle"
...
Combobox1.ListIndex = ComboBox1_SelectionInt
Combobox2.ListIndex = ComboBox2_SelectionInt
...

You might want to use the Me.hide proposal stated above.

During me.unload or when you close the workbook you save the form values which you want to save e.g. to a hidden worksheet (e.g. with named ranges)

With Workheets("HiddenSheetForUserform")
    .Range("Textbox1") := "Textbox1 value"
    .Range("Textbox2") := "Textbox2 value"
    .Range("Textbox3") := "Textbox3 value"
    '...

    .Range("Combobox1") := ComboBox1_SelectionInt
    .Range("Combobox2") := ComboBox2_SelectionInt
End With

Upvotes: 1

Chronocidal
Chronocidal

Reputation: 7951

Use Me.Hide instead of Unload Me, and next time you .Show the UserForm it will still contain the same values.

However: These values will be lost when you close and reopen the workbook

Upvotes: 1

Related Questions