Can a UserForm retain info?

[leopardhawk]

Hello forum friends, I have a fairly complex workbook that has two UserForms activated by Command Buttons where users enter Personal and Financial info respectively. I will use the Personal Info UserForm as an example:

The user clicks the button and the UserForm opens, and at a minimum they enter their first and last name, date of birth and gender in separate fields. When they click the OK button in the form, the data they entered is transferred to the appropriate worksheet(s) and the UserForm is cleared. I am wondering if there is a way to code it so that the UserForm will 'retain' the data that was entered the first time so that if the user launches it again, it is still visible to them.

Of course the same applies to the Financial Info UserForm.

Appreciate any help!

 

[dmt32]

Hi,
give this a try & see if it does what you want

Place following code In a STANDARD module

Sub ControlSettings(ByVal Form As Object, ByVal Action As Integer)
    Dim ws          As Worksheet
    Dim r           As Long
    Dim m           As Variant
    Dim ctrl        As Control
  
    'create sheet to store userform control values
    If Not Evaluate("ISREF('" & Form.Name & "'!A1)") Then
      
        Set ws = Worksheets.Add(After:=Worksheets(Sheets.Count))
            ws.Name = Form.Name
         
    Else
        'sheet exists
        Set ws = Worksheets(Form.Name)
    End If
  
    'hide sheet
     ws.Visible = xlSheetVeryHidden
   
     'loop controls
    For Each ctrl In Form.Controls
        Select Case TypeName(ctrl)
            'check only these controls only
            Case "TextBox", "ComboBox", "ListBox", "OptionButton", "CheckBox", "ToggleButton"
                'see if control name exists in list
                m = Application.Match(ctrl.Name, ws.Columns(1), 0)
                'get the row for control name
                r = IIf(IsError(m), ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1, CLng(m))
                With ws.Cells(r, 1)
                    'if missing, add name to list
                    If IsError(m) Then .Value = ctrl.Name
                    If Action = xlOpen Then
                        'return control value
                        ctrl.Value = IIf(VarType(ctrl) = vbBoolean And Len(.Offset(, 1).Value) = 0, False, .Offset(, 1).Value)
                    Else
                        'save control value
                        .Offset(, 1).Value = ctrl.Value
                    End If
                End With
        End Select
    Next
  
End Sub

and to use it from your userform(s) you need to include the lines of code shown in these two events

Private Sub UserForm_Initialize()
    ControlSettings Me, xlOpen
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ControlSettings Me, xlClosed
  'optional
   'ThisWorkBook.Save
End Sub

Solution should work for any number of userforms in your project BUT will only store / return the last users settings & the workbook is saved before closing.

Dave

 

[dmt32]

@dmt32 thank you so much for your suggestion, It works really well...!!

Most welcome & glad it does what you want

Solution can if needed, be updated if multiple users access the workbook on your network, to record their individual settings

Appreciate your feedback

Dave

 

[My Aswer Is This]

If you close the Userform using Hide the data is retained as long as the workbook remains opened.
But if you close the Userform by using Unload the data in the userform is cleared.

 

[leopardhawk]

@My Aswer Is This

My mistake, I should have said "even if the workbook is closed". Basically making it permanent unless typed over by the user. Sorry about that.

 

[My Aswer Is This]

@MY Aswer Is This

My mistake, I should have said "even if the workbook is closed". Basically making it permanent unless typed over by the user. Sorry about that.

I do not believe that can be done. Unless all the values in the userform were written to a sheet when userform is closed and then when userform is opened next time those same values are written back into the userform. But that could be tricky telling each control to be set back to previous setting. Which would be beyond my knowledge.

 

[Rick Rothstein]

You might be able to use the technique I described in my mini-blog article here to save the data (which you would have to read back in and reload to the UserForm controls next time the workbook and/or UserForm was opened)...

A Method To Store Workbook Settings...

 

[wsnyder]

You could use a FileSystemObject (FSO) textstream to write the data out to a text file then read the data into the Userform if the file exists in Environ("Homepath").
FSO is part of Microsoft Scripting Run-time (scrrun.dll)

-w

 

[leopardhawk]

@Rick Rothstein @wsnyder -Thanks to both of you for your suggestions but they seem way beyond my basic capabilities when it comes to writing code. I really would have no idea where to even begin. I was kinda hoping that there was a simpler solution. Fingers crossed...

Thanks again!

 

[wsnyder]

Some sample code here : VBA/M_TextStream_Write.bas at master · winstonsnyder/VBA

-w