none
Resume where left off after save in VB UserForm in Excel RRS feed

  • Question

  • 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

     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

    Monday, September 28, 2015 9:46 AM

All replies

  • Set the ControlSource property of each of the controls to cells on a worksheet. Specify the Sheet name and cell address  in the ControlSource for each control like the following (but use a separate cell for each control)

    Sheet2!A1

    The worksheet with the ControlSource can be a hidden worksheet. (See comments below re hidden worksheets)

    The worksheet retains the values and provided the workbook is saved before closing then when the workbook is re-opened and the Userform is Shown again then the controls inherit the values from the ControlSource.

    Clearing the values of the controls on the Userform also clears the values in the ControlSource cells.

    If you use VBA code to hide the worksheet then the users cannot see from the interactive mode that a worksheet has been hidden and the worksheet cannot be unhidden without VBA code. The following code sets a Worksheet to Very Hidden.

    Sub HideWorksheet()
        Worksheets("Sheet2").Visible = xlVeryHidden
    End Sub

    The following code example will make the worksheet Visible again.

    Sub UnhideWorksheet()
        Worksheets("Sheet2").Visible = True
    End Sub


    Regards, OssieMac

    Monday, September 28, 2015 10:45 AM