Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Save user form for later editing

    Hi
    MS 365 on Win10
    I am wanting to be able to save data entered in a userform for later additions or editing before posting to final spreadsheet
    cheers

    Phil Carter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Phil,

    A little more information please.

    • I take it this is a partial record?
    • Do you want the record saved automatically (if it is a partial and user exits the form)?
    • Do you want a button on the userform to allow the user to select if the record should be saved for later?
    • Do you want to save the record in the workbook or to an external file?
    • Is this a multiple user workbook (this would probably necessitate saving to an external file or at least tagging the record with the responsible user's ID.
    • Could you post an example workbook with the userform and some sample data?



    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    RG hi again
    The file is set as an xltm so the save function would be xlsx but of course that will not allow further editing. The intention is that staff generate the report monthly and send to Commercial Manager for compilation in to consolidated report.
    • Yes a partial record
    • Yes save automatically
    • Have a Save for Edit button
    • In the workbook is OK or maybe an external file
    • Single user workbook

    Attached Files Attached Files
    cheers

    Phil Carter

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Phil,

    You already have the button you just need to program it. You even have most of the code in the CmdBtnOK_Click code it just needs to be copied to the CmdBtnSave_Click (the part where you save the data from the form) event and modified to save to a temp sheet (I'd have it create a new sheet for this function).

    Then in the Workbook_Open event I'd check for this temp sheet and if it exists automatically display the form and load it with the values from the temp worksheet and then delete the temp sheet. This should keep things nice and tidy.

    If you want to make your code easier to maintain you could make a subroutine out of the part that saves the data and then just call the subroutine with the sheet to which the data should be saved! One block of code to maintain then if you add or delete fields from the form.

    If you have problems implementing this post back but from the looks of it you should be able to handle it now that you know what to do.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Phil,

    Then in the Workbook_Open event I'd check for this temp sheet and if it exists automatically display the form and load it with the values from the temp worksheet and then delete the temp sheet. This should keep things nice and tidy.

    If you want to make your code easier to maintain you could make a subroutine out of the part that saves the data and then just call the subroutine with the sheet to which the data should be saved! One block of code to maintain then if you add or delete fields from the form.

    HTH
    RG
    I am having trouble with this bit, checking the sheet exists and then populating the form with the existing data.

    Your idea of a separate module looks good too.
    cheers

    Phil Carter

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Phil,

    Here's the skeleton for the the procedure:
    Code:
    Option Explicit
    
    Sub Workbook_Open()
    
       Dim wksTemp As Worksheet
       
       On Error Resume Next
       Set wksTemp = ActiveWorkbook.Worksheets("PartRecord")
       On Error GoTo 0
       
       If (wksTemp Is Nothing) Then  '*** No temp record found ***
         
         MsgBox "No Temp Record was found", _
                vbOKOnly + vbInformation, "Testing"
                
       Else  '*** Load Temp Record into form and display ***
         
         MsgBox "Temp Record was found", _
                vbOKOnly + vbInformation, "Testing"
    
        '*** Code to load & display form goes here ***
       
         Sheet1.Select    '*** Substitute your main sheet for Sheet1!
         Application.DisplayAlerts = False   '*** Hide deletion confirmation dialog ***
         wksTemp.Delete
         Application.DisplayAlerts = True    '*** Restore confirmation dialogs ***
    
       End If
       
    End Sub 'Workbook_Open
    This code gets placed in the ThisWorkbook module.

    The code is based on you creating a temporary worksheet called PartRecord to store the form's data.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    RG Thanks for that
    Did a compile and doesn't like line "Sheet1.Select" which I changed to read "Monthly Report.Select" and the userform is not populating with the saved data
    cheers

    Phil Carter

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Phil,

    Could you post your update file?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Sure
    Attached Files Attached Files
    cheers

    Phil Carter

  10. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You need to refer to the worksheet in the same way it was done earlier in the same macro
    ActiveWorkbook.Worksheets("Monthly Report")

    And I'm not sure why you want to .Select the sheet. I would expect that .Activate might be more useful.

    But why do you need a temp sheet to hold this info? Why can the data not go onto the "Monthly Report" until a certain point in time. Surely it doesn't become 'final' until the person submits the sheet. I would be putting the info straight onto the report and not be bothered with a temp sheet to hold the same information until it needs to be transferred across.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. #11
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    But why do you need a temp sheet to hold this info? Why can the data not go onto the "Monthly Report" until a certain point in time. Surely it doesn't become 'final' until the person submits the sheet. I would be putting the info straight onto the report and not be bothered with a temp sheet to hold the same information until it needs to be transferred across.
    Andrew
    Not sure I understand. My users wish to enter data, save it and then come back to it later by opening the workbook again. I didn't believe rerunning the userform retained the previously entered data, which is what my users wish to do, edit the data .
    cheers

    Phil Carter

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Bonriki,

    In a recent project for another member, there was a form where data was entered and stored to a hidden sheet. A save button sent the forms data to the sheet to be stored. When the form was activated, the data was pulled back from the sheet to have all fields populated on the form when it reopened. This method work for reopening the form in the same excel session or opening the project at a later date

    Save Button: Stores data on hidden sheet
    Code:
    Private Sub CommandButton2_Click()
    '-------------------------------------
    'SAVE AND WRITE TO HIDDEN SHHET
    With Worksheets("Hidden")
        For I = 1 To 10
            .Cells(I, 1) = Me.Controls("ComboBox" & I).Value 'WRITE TO HIDDEN SHEET
        Next I
    End With
    End Sub
    Open Userform: Retrieves data from hidden sheet
    Code:
    Private Sub UserForm_Activate()
    '-------------------------------------
    'WHEN FORM OPENS: LOAD VALUES FROM HIDDEN SHEET
        For I = 1 To 10
            With Me.Controls("ComboBox" & I)
                .Value = Worksheets("Hidden").Cells(I, 1)
            End With
        Next I
    End Sub
    Here is the link for the thread:
    http://windowssecrets.com/forums/sho...=1#post1056834

    I like to keep the naming convention of the form objects as <object type><number> (ex. ComboBox4) so I can loop through them.

    HTH,
    Maud

  13. #13
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Maud
    Thanks for that. I have used a combination of Text Boxes and Combination Boxes on the UserForm.
    I assume your code will only work for Combination Boxes
    cheers

    Phil Carter

  14. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    The userform doesn't retain the data but it can load it from the same sheet that is populated by the userform anytime.
    See the attached version of your workbook. You have lookup lists already setup on a sheet and it seemed pointless to recreate those lists in vba - so I changed that too.
    Finance-Commercial Sales Monthly Report.zip

    Your file naming pattern seems flawed. Saving to C:\Users\Username won't work unless they ONLY choose themselves from the list and this matches their userid on that machine. Better to not even bother asking the user for the name and use the username property or the environment variable to arrive at the user's name and/or documents folder location.
    Last edited by Andrew Lockton; 2016-06-15 at 23:11.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  15. #15
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Thanks Andrew
    The workbook doesn't seem to want to behave. When I double click to open the file opens with Protected view warning with "Enable Editing" highlighted then when clicked it gives VBA error 91
    error91.JPG
    and then when I click "Debug" I get the following
    91Debug.JPG

    However, if I open the file from Excel it all works fine as expected. The preference is for the users to have the file on their desktop, double click to open and fill in the data.

    Reviewing the code, other the initialise sub, I cannot see what you have changed plus the email function doesn't work
    cheers

    Phil Carter

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •