• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 91
  • Last Modified:

Resetting user form in Excel when 'Save' button is clicked

I have created a user form called Time Tracker. The date in the user form is autopopulated with the current date when the form is opened. When the Save button is clicked, the data from the user form populates the next available row in columns A to G in a sheet called 'TrackingSheet'. All fields in the user form must be completed for the form to save, otherwise an error message pops up.

I would like the user form to reset once the Save button is clicked so users can add additional records in the same session. When reset, all fields except the date field, which is autopopulated, would be blank.

I have included the spreadsheet to assist with this question.

Thanks,
Andrea
ProdMainTimeTracker_EE.xlsm
0
Andreamary
Asked:
Andreamary
  • 3
  • 3
  • 2
2 Solutions
 
Ryan ChongCommented:
try update the Sub cmdSave_Click to:

Private Sub cmdSave_Click()
Dim NextRow As Long

    With Me

        If .cboIDList.Value = "" _
           Or .txtDesc.Value = "" _
           Or .txtPA_Unit.Value = "" _
           Or .txtDate = "" _
           Or .cboInitials = "" _
           Or txtMinutes = "" _
           Or cboStatus = "" Then

            MsgBox "Form not completed!", vbCritical, "Incomplete"
            Exit Sub
        End If

        NextRow = Sheets("TrackingSheet").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("TrackingSheet").Range("A" & NextRow).Value = .cboIDList.Value
        Sheets("TrackingSheet").Range("B" & NextRow).Value = .txtPA_Unit.Value
        Sheets("TrackingSheet").Range("C" & NextRow).Value = .txtDesc.Value
        Sheets("TrackingSheet").Range("D" & NextRow).Value = .txtDate.Value
        Sheets("TrackingSheet").Range("E" & NextRow).Value = .txtMinutes.Value
        Sheets("TrackingSheet").Range("F" & NextRow).Value = .cboInitials.Value
        Sheets("TrackingSheet").Range("G" & NextRow).Value = .cboStatus.Value
        
        .cboIDList.Value = ""
        .txtPA_Unit.Value = ""
        .txtDesc.Value = ""
        .txtDate.Value = ""
        .txtMinutes.Value = ""
        .cboInitials.Value = ""
        .cboStatus.Value = ""
    End With

End Sub

Open in new window


remove line:

.txtDate.Value = ""

Open in new window


in case you want to retain Date value after the Save button was clicked.
0
 
Roy CoxGroup Finance ManagerCommented:
I use UserForms in many applications. To save repeating lines of code I wrote a UDF to reset controls. Place it in a Standard module



'---------------------------------------------------------------------------------------
' DateTime  : 09/05/2007 08:43
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : [URL="http://www.excel-it.com"]click here for more examples and Excel Consulting[/URL]
' Purpose   : Loops through controls of UserForm & clears setting
'
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Public Function ClearAll(frm As MSForms.UserForm) As Boolean
    ' clear out all controls
    Dim Octrl    As Control
    ' if any error occurs, just exit
    On Error GoTo ExitFunc

    ' loop through controls, figure out type and
    ' use appropriate method to clear it
    For Each Octrl In frm.Controls
        Select Case TypeName(Octrl)
            Case "TextBox":  Octrl = Empty
            Case "CheckBox", "OptionButton": Octrl.Value = False
            Case "ComboBox", "ListBox": Octrl.ListIndex = -1
            Case Else:
        End Select
    Next Octrl

ExitFunc:advan
    Set Octrl = Nothing
End Function

Open in new window


At the end of the save code just add, this will work on any userform in the workbook

ClearAll Me

Open in new window


Private Sub cmdSave_Click()
    Dim NextRow As Long

    With Me

        If .cboIDList.Value = "" _
           Or .txtDesc.Value = "" _
           Or .txtPA_Unit.Value = "" _
           Or .txtDate = "" _
           Or .cboInitials = "" _
           Or txtMinutes = "" _
           Or cboStatus = "" Then

            MsgBox "Form not completed!", vbCritical, "Incomplete"
            Exit Sub
        End If

        NextRow = Sheets("TrackingSheet").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("TrackingSheet").Range("A" & NextRow).Value = .cboIDList.Value
        Sheets("TrackingSheet").Range("B" & NextRow).Value = .txtPA_Unit.Value
        Sheets("TrackingSheet").Range("C" & NextRow).Value = .txtDesc.Value
        Sheets("TrackingSheet").Range("D" & NextRow).Value = .txtDate.Value
        Sheets("TrackingSheet").Range("E" & NextRow).Value = .txtMinutes.Value
        Sheets("TrackingSheet").Range("F" & NextRow).Value = .cboInitials.Value
        Sheets("TrackingSheet").Range("G" & NextRow).Value = .cboStatus.Value

    End With
''/// reset controls
    ClearAll Me
End Sub

Open in new window

0
 
AndreamaryAuthor Commented:
Thanks to you both for providing solutions. I am testing both of them.

Roy, I am getting an error message as follows:
Compile error: Sub or Function not defined

Module: Public Function ClearAll(frm As MSForms.UserForm) As Boolean
ExitFunc: advan

I've attached the updated spreadsheet for your review in case I've applied your code incorrectly.

Thanks,
Andrea
ProdMainTimeTracker_CoxSolution.xlsm
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ryan ChongCommented:
in your module, change:

ExitFunc: advan

Open in new window


to:

ExitFunc:

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Sorry, I don't know how that typo happened.

See attached
ProdMainTimeTracker_CoxSolution.xlsm
0
 
AndreamaryAuthor Commented:
Thanks to you both. Ryan, I appreciated your timely response with a working solution, and assistance on another solution!

Roy, thanks for your solution which is now working with that minor tweak. I did want the date field to be retained, which did not seem to be the case with your code, so if you are able to let me know how to address that, that would be great.

I've tried to award the points as fairly as possible. :-)

Cheers,
Andrea
0
 
Roy CoxGroup Finance ManagerCommented:
It's quite simple and the Function remains dynamic. Just use the controls' Tag Property/ Any control that is not to be cleared simply put a character, e.g. an asterisk, in the control's Tag property at design time. The amended code will ignore any controls that are marked that way.

Option Explicit



'---------------------------------------------------------------------------------------
' DateTime  : 09/05/2007 08:43
' DateTime  : 09/05/2007 08:43
' Author    : Roy Cox (royUK)
' Website   : [URL="http://www.excel-it.com"]click here for more examples and Excel Consulting[/URL]
' Purpose   : Loops through controls of UserForm & clears setting
'
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Public Function ClearAll(frm As MSForms.UserForm) As Boolean
''/// clear out all controls
    Dim Octrl As Control
    ''/// if any error occurs, just exit
    On Error GoTo ExitFunc

    ''/// loop through controls, figure out type and
    ''/// use appropriate method to clear it
    For Each Octrl In frm.Controls
    ''/// check the control's Tag Property
        If octl.Tag = "" Then
            Select Case TypeName(Octrl)
            Case "TextBox": Octrl = Empty
            Case "CheckBox", "OptionButton": Octrl.Value = False
            Case "ComboBox", "ListBox": Octrl.ListIndex = -1
            Case Else:
            End Select
        Next Octrl
    End If
ExitFunc:
    Set Octrl = Nothing
End Function

Open in new window

0
 
AndreamaryAuthor Commented:
Thanks, Roy, for your instructions on this!

Cheers,
Andrea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now