Results 1 to 3 of 3

Thread: before save issue

  1. #1

    before save issue

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to create a beforesave code within my template. The template has certain fields that are blank and when the end user enters data, I want them to be able to click on save or the blue disk and it saves it to a new location, based on their user and some of the cells within the form. I do not want the template to be saved with the data they input. The data they input is saved only when they click the blue disk and it saves to another location, with their username and certain cells in the form. Then the end user can continue using that same template for the next entry and so on. I have a beforeclose that asks them if they really want to close or not. The only time they would close, or hit the big red X is when they are done with all their entries for the day. Here is my code for my beforesave:
    ****someone else suggested putting in a read only, but not sure where to put that in the beforesave and the below code does not work. it gives me an error of run-time 1004 method saveas of object workbook failed.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & _
    "\Documents\Audits\Excels\" & Range("B9").Text & Chr(32) & Range("B7").Text & _
    Chr(32) & Format(Range("B10").Value, "MM-DD-YYYY") & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

  2. #2
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Excel Version
    2016 32bit

  3. #3
    got my answer. Thanks for the help
    'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Dim sfilename As String
    'Dim spath As String
    'Dim ws As Worksheet
    'Set ws = ActiveSheet
    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False
    'spath = "C:\Users\" & Environ$("username") & "\Documents\Audits\Excels\"
    'sfilename = ws.Range("B9").Text & Chr(32) & Range("B7").Text & Chr(32) _
    '        & Format(Range("B10").Value, "MM-DD-YYYY") & ".xlsx"
    ''save copy of template
    'ActiveWorkbook.SaveCopyAs spath & sfilename
    ''clear fields
    ''tell op job done
    'MsgBox "Your data has been saved.", 48, "Data Saved."
    ''cancel normal save
    'Cancel = True
    'Application.ScreenUpdating = True
    'Application.DisplayAlerts = True
    'End Sub
    Posted my answer to help out 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