Save user form for later editing

Topic

New Reply

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

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 :cheers:

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.

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Phil,

Could you post your update file? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Sure

Reply | Quote

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.

Reply | Quote

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 .

Reply | Quote

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()
[COLOR=”#008000″]’————————————-
‘SAVE AND WRITE TO HIDDEN SHHET[/COLOR]
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()
[COLOR=”#008000″]’————————————-
‘WHEN FORM OPENS: LOAD VALUES FROM HIDDEN SHEET[/COLOR]
    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/showthread//174654-Trying-to-calculate-income-spread-across-months?p=1056834&viewfull=1#post1056834

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

HTH,
Maud

Reply | Quote

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

Reply | Quote

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.
44763-Finance-Commercial-Sales-Monthly-Report

Your file naming pattern seems flawed. Saving to C:UsersUsername 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.

Reply | Quote

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
44764-error91
and then when I click “Debug” I get the following
44765-91Debug

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

Reply | Quote

Phil

It works on my machine by double clicking to open so the issue might be local to your machine. Perhaps you have multiple versions of this running.

Interestingly, it appears creating a new workbook triggers the Auto_Open macro so it appears that Excel behaves differently to Word and you probably don’t need the Auto_New macro at all.

I didn’t change anything about the email macro but perhaps that issue is caused because the form was closed in the last line of the Save macro. Try removing that to keep the form open while the email macro runs

Reply | Quote

Bonriki,

Yes, you are right. As written, the code will work only for combo boxes. The method of writing data from an objects to cells then cells to objects can be easily modified. The point I am trying to demonstrate is the simplicity, consistency, and integrity of saving settings to a sheet then back to a form.

If you have multiple types of objects, the code can be modified to something like:

Code:

Private Sub CommandButton2_Click()
[COLOR=”#008000″]’————————————- 
‘SAVE AND WRITE TO HIDDEN SHEET[/COLOR]
With Worksheets(“Hidden”)
    For I = 1 To 5  [COLOR=”#008000″]’5 COMBOBOX CONTROLS[/COLOR]
        .Cells(I, 1) = Me.Controls(“ComboBox” & I).Value ‘WRITE TO HIDDEN SHEET
    Next I
    For I = 1 To 6  [COLOR=”#008000″]’6 TEXTBOX CONTROLS[/COLOR]
        .Cells(I+5, 1) = Me.Controls(“TextBox” & I).Value ‘WRITE TO HIDDEN SHEET
    Next I
    For I = 1 To 3  [COLOR=”#008000″]’3 CHECKBOX CONTROLS[/COLOR]
        .Cells(I+11, 1) = Me.Controls(“CheckBox” & I).Value ‘WRITE TO HIDDEN SHEET
    Next I
End With
End Sub

This is provided you use a naming convention that you can loop through objects then vice versa.

HTH,
Maud

Reply | Quote

Phil

Maud’s code is actually independent of the ‘type’ for each control. It just so happens to coincide with default names given to controls added to a userform. You have already renamed all your text and comboboxes so it is not going to give you a useful result. However, it is illustrating a good principle of trying to reduce the amount of coding required to handle updates to your form.

A more robust way would be to loop through every control on the userform and find attribute(s) that indicate the control should be worked on. For instance, if you used the Tag property of each field to store the cell address the field corresponds with then you don’t have to explicitly handle that repetitively in handcrafted vba.

Code:

  Dim aCtl As Control
  For Each aCtl In Me.Controls
    Select Case TypeName(aCtl)
      Case "ComboBox", "TextBox"
        ActiveWorkbook.Sheets("Monthly Report").Range(aCtl.Tag) = aCtl.Value
    End Select
  Next aCtl

This method allows you to add/delete/change the userform quickly without actually needing to make vba changes at all (let alone in multiple places). See attached file for this methodology applied to your file 44772-Finance-Commercial-Sales-Monthly-Report2

Reply | Quote

sorry guys
Had a problem with a variable but then realised the sub where the variable was defined was set to Option Explicit!!

Reply | Quote

Andrew,

Very Cool!
44299-Joe-Cool

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks Andrew and Maud for all your help
Make sense

Reply | Quote

Andrew,

A bit more work up front but big benefits in the backend. I like it!

Reply | Quote

Just messing around with this further but using the ControlTipText property instead.

Code:

With TextBox1
    Range(.ControlTipText) = TextBox1.value
End With

It gives the added benefit of displaying where the value will be stored when hovering over the textbox. Real handy in the development stage of testing.

Hover over the control
44782-bonriki

Reply | Quote

Hey guys
My users now decide that wish to copy up to 3 columns of mixed text, column 1 and data columns 2 & 3, into the P&L and GIT combo boxes. I have tried with column properties set but all I get are the headers

Reply | Quote

Phil

IMHO, your userform is an answer in search of a problem. The actual worksheet your form reads/writes to should be where the users put all their data. You can reformat it a bit to make is sexier but it is always going to be FAR simpler to just let them edit it directly. The userform adds a level of complexity without any real payoff in terms of useability.

If you need to do a 3 column dataset where the users can edit all three fields with multiple records, this is very simple to do directly on a worksheet. It is much more complex in a userform and most people would actually use a second userform just to add/edit a multiple column list field. Do you really need that level of complexity?

Reply | Quote

While Andrew has a point with simplicity, forms as well as sheets themselves are as complex as you make them. In their defense, opening a form moves the user’s attention away from the clutter of sheet data and emphasizes the field(s) where the input is required. They add additional real estate on demand than takes it out of site when not needed. Forms are everywhere. They offer structure, design, creativity, and interest and are used extensively on all types of applications and websites.

They may be a bit more challenging in design mode but there are big payoffs at run time from the users point of view. After all, that’s why designers program applications. IMHO, forms offer one of the biggest bangs for the buck in programming.

Maud

Reply | Quote