Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro to save then clear form data

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to save then clear form data

    I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

    I'm new to macros and would appreciate any help this board can offer. Thanks...

  2. #2
    Board Regular Diablo II's Avatar
    Join Date
    Sep 2008
    Location
    Tampa Fl
    Posts
    538
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to save then clear form data

    from what i can see, that what you wont can be done, if you have a workbook that you open and you save it with a new name the other workbook you open would not have changed so no need to clear the
    Values.

    here is same code to use Cell A1 for the name of the workbook

    Code:
     
    Sub Saveworkbook()
    Application.DisplayAlerts = False
    Dim dName$, vName$
        dName = Range("A1") ' here you can set the cell with the name you wont 
        vName = ActiveWorkbook.FullName
        ActiveWorkbook.SaveAs "C:\AAA\" & dName ' here set the path, i used c:\aaa\ for a path 
    'here is just a note you may wont to allso set a date and time with the name, only 1 workbook with that name can be saved in that foulder or it will over right the workbook each time. 
        ActiveWorkbook.SaveAs vName
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
    End Sub
    here is the code with the date and time with space so it dont look like a buch on numbers put there.

    Code:
     
     ActiveWorkbook.SaveAs "C:\AAA\" & dName & "      " & Format(Date, "MM.DD.YY") & "     " & Format(Time, "hhmm")
    Surrender be on good terms with all persons. Speak your truth quietly & cearly; and listen to other, even the dull & ignorant, they to have their story

    TomB

  3. #3
    New Member
    Join Date
    Feb 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to save then clear form data

    Hi , I tried your code for saving the entire file in a Location with the following code.
    But its not working as expected. When i Click the Button the entire Excel goes blank , including Ms Visual Basic screen, only tool bars remain.
    I have to forcefully exit and reopen the file again
    Please see my code and help me this.

    Private Sub CommandButton1_Click()
    If Range("F6").Value = "" Then
    MsgBox ("Please fill in cell F6")
    ElseIf Range("F13").Value = "" Then
    MsgBox ("Please fill in cell F13")
    ElseIf Range("F15").Value = "" Then
    MsgBox ("Please fill in cell F15")
    ElseIf Range("F17").Value = "" Then
    MsgBox ("Please fill in cell F17")
    Else
    Application.DisplayAlerts = False
    Dim dName$, vName$
    dName = Range("F6")
    vName = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs "C:\" & dName & " " & Format(Date, "MM.DD.YY") & " " & Format(Time, "hhmm")
    ActiveWorkbook.SaveAs vName
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    End If
    End Sub

  4. #4
    New Member
    Join Date
    Aug 2016
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to save then clear form data

    Hi Guys,
    Just wondering if you got this to work. I have a form that populates three different sheets & want to do the same thing with the form. Save the whole workbook as a job name & clear the form for next use.
    M

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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