Results 1 to 2 of 2
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    St Louis, Missouri, USA
    Thanked 1 Time in 1 Post

    Save Issue (VBA/Excel/2003)

    We have an application we wrote in Excel and I added a quick fix to force a save to our naming convention using a form Button.

    <pre>Sub btnSave_Click()
    Application.DisplayAlerts = False

    ' On Error Resume Next
    If Len(Range("EmployeeName")) < 2 Then
    MsgBox "You should give this Employee a name before saving their file" _
    , vbInformation, "Not Saved"
    Application.StatusBar = "File _______ not saved"
    Exit Sub
    ThisWorkbook.BuiltinDocumentProperties("Author") _
    = Application.UserName
    ThisWorkbook.BuiltinDocumentProperties("Title") _
    = Range("EmployeeName")
    ThisWorkbook.SaveAs Filename:=Range("SaveMain") _
    & ThisWorkbook.Sheets("Report").Range("EmployeeName" ) & ".xls", FileFormat:=xlNormal
    ThisWorkbook.SaveAs Filename:=Range("SaveProt") _
    & ThisWorkbook.Sheets("Report").Range("EmployeeName" ) & ".xls", FileFormat:=xlNormal
    Application.DisplayAlerts = True
    Application.StatusBar = "Saved to H and I drives"
    End If
    End Sub</pre>

    This works great EXCEPT -- is there a way to disable the File | SaveAs so they have to go thru our procedure? <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Save Issue (VBA/Excel/2003)

    Create a BeforeSave event procedure in the ThisWorkbook module:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
    MsgBox "Not allowed!", vbExclamation
    Cancel = True
    End If
    End Sub

    This will disable File | Save As.

Posting Permissions

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