Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable Save as (2003)

    I have written a macro which creates a project budget workbook for managers to update, and saves this workbook to a directory with a meaningful workbook name.

    We would like to disable the "save as" functionality within the workbook created by the macro, to try to ensure that managers stick to the workbook naming constraints which we have set up.

    We are aware that we cannot disable the copy and paste functionality within windows explorer for the relevant directory. However Is there a way to disable the "save as" functionality from within the workbook so as to safeguard the name as far as possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Disable Save as (2003)

    You can put the following code in the ThisWorkbook module:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = SaveAsUI And (InStr(ThisWorkbook.FullName, ".") > 0)
    End Sub

    SaveAsUI is True if the Save As dialog will be displayed. The InStr function tests if the workbook has ever been saved before; if not, it allows a Save As.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Save as (2003)

    Hans

    Thank you for the code extract.

    I have tested the code and as I understand it the functionality allows the save as function to occur once and thereafter disables the menu option, even though the latter still remains visible.

    However the macro which I have written uses a worksheet form a master document to create the project files. More precisely, the macro creates a new workbook and then copies the master-sheet from the master document into that workbook.

    Given the above, how do I get the code which you have provided into the project file when the macro creates these?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Disable Save as (2003)

    Create a blank workbook with only the code I posted in the ThisWorkbook module.
    Save it in a location accessible to all users, say as F:ExcelCustom.xls
    Instead of creating a standard blank new workbook in your macro, create one based on the workbook you just saved. The code will be in the new workbook too.

    Workbooks.Add Template:="F:ExcelCustom.xls"

    or

    Set wbk = Workbooks.Add(Template:="F:ExcelCustom.xls")

    depending on how you set up your code. Replace: "F:ExcelCustom.xls" with the correct path and file name.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Save as (2003)

    Ah

    it all becomes clear now

    Thanks

Posting Permissions

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