Save UserForm and reopen later semi-complete and resume

[samuelle]

Hi,

I have quite a long form that the users have stated they want to pause halfway and resume.

I currently have the following code to save and close:
End With
ActiveWorkbook.SaveAs ([txtStudentLast])
ActiveWorkbook.Close
End Sub

but when I reopen it the data is written to the background sheet but the UserForm is empty ready for another entry. Can anyone help with code that would open the form where it left off? Ideally I'd want one button to "Save and Resume Later" and one button to "Save and Finish" - I suppose what I currently have is Save and Finish.

Thank you

 

[dmt32]

Hi,
Give this solution a try & see if it does what you want.

In a NEW standard module place ALL the following code:

 Enum XLUserActionType
xlStoreFormValues
xlReturnFormValues
xlClearStoredValues
End Enum


Sub GetFormControlValues(ByVal Form As Object, ByVal Action As XLUserActionType)


    Dim wsFormSettings As Worksheet
    Dim Foundcell As Range
    Dim r As Long
    
    On Error Resume Next
    Set wsFormSettings = Worksheets(Environ("USERNAME"))
    If Err.Number = 9 Then Set wsFormSettings = WorkSheetAdd(Environ("USERNAME"))
    On Error GoTo 0


    With wsFormSettings
        Set Foundcell = .Columns(1).Find(Form.Name, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
        r = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With


    If Not Foundcell Is Nothing Then r = Foundcell.Row


    ControlSettings Form:=Form, ws:=wsFormSettings, RecordRow:=r, Action:=Action


End Sub


Sub ControlSettings(ByVal Form As Object, ByVal ws As Object, ByVal RecordRow As Long, ByVal Action As XLUserActionType)
    Dim ctl As Control
    Dim CtrlType As String


    For Each ctl In Form.Controls
        CtrlType = TypeName(ctl)


        Select Case Action
        
        Case xlStoreFormValues
            Select Case CtrlType
            Case "OptionButton", "CheckBox", "TextBox", "ComboBox", "ListBox", "ToggleButton"
                With ws
                    .Cells(RecordRow, 1).Value = Form.Name
                    .Cells(RecordRow, 2).Value = ctl.Name
                    .Cells(RecordRow, 3).Value = CStr(ctl.Value)
                End With
                RecordRow = RecordRow + 1
            End Select
            
        Case xlReturnFormValues
            Select Case CtrlType
            Case "OptionButton", "CheckBox", "ToggleButton"
                With ws.Cells(RecordRow, 3)
                    ctl.Value = IIf(.Value = "", False, .Value)
                End With
                RecordRow = RecordRow + 1
            Case "TextBox", "ComboBox", "ListBox"
                ctl.Value = ws.Cells(RecordRow, 3).Value
                RecordRow = RecordRow + 1
            End Select
            
        Case xlClearStoredValues
            If ws.Cells(RecordRow, 1).Value = Form.Name Then
                ws.Cells(RecordRow, 3).Value = ""
                RecordRow = RecordRow + 1
            End If
        End Select
    Next ctl
End Sub


Function WorkSheetAdd(ByVal SheetName As String) As Worksheet
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Set ws = Worksheets.Add
    With ws
        .Range("A1:C1").Value = Array("UserForm Name", "Control", "Value")
        .Name = SheetName
        .Visible = xlSheetVeryHidden
    End With
    Set WorkSheetAdd = ws
    Application.ScreenUpdating = True
End Function

In each form you require to save entry values place following code:

In the forms initialize Event:

Private Sub UserForm_Initialize()
    GetFormControlValues Form:=Me, Action:=xlReturnFormValues
End Sub

In the forms Save and Resume Later button code:

Private Sub Save_and_Resume_Later_Click()
    GetFormControlValues Form:=Me, Action:=xlStoreFormValues
End Sub

In the Forms Save and Finish Button code:

Private Sub Save_And_Finish_Click()
    GetFormControlValues Form:=Me, Action:=xlClearStoredValues
End Sub

Solution is designed to create a holding place for controls (TextBox,CheckBox,OptionButton,ComboBox,ListBox,ToggleButton) values which can be applied to any number of forms in your project as required.

In addition, a table is created for each user so their settings should not be overridden by another logging in to use your application.

Hope Helpful

Dave