Run a SaveAs VBA formula then disable the SaveAs formula in Workbook

[dharvey1978]

Hi Excel Forum Experts,

I have found a VBA code that works but i need it altered so that when it runs, the code in the new Macro Workbook is disabled so when the new workbook is reopen next time the code does not run. So If you have filled out data in the new macro workbook i just want it to open on next open as it would get very messy if you have to keep clicking save as and override the the file you are working on and you might end up overriding the wrong file.

How do i alter this code to make sure it is disabled on new file after save.

The VBA Code i am using is the followingwhich is stored in the ThisWorkbook section

Private Sub Workbook_Open()
Dim FileSaveAsName As Variant
Dim intchoice As Integer
Static saveProcess As Boolean
Application.EnableEvents = False
ActiveWindow.ScrollRow = 1
        Set FileSaveName = Application.fileDialog(msoFileDialogSaveAs)

        FileSaveName.InitialFileName = ThisWorkbook.Name
        FileSaveName.FilterIndex = 2   'select to save with a ".xlsm" extension
        intchoice = FileSaveName.Show
        If intchoice <> 0 Then
            FileSaveName.Execute
        End If

Application.EnableEvents = True
End Sub

TIA for any assistance

Thanks
Donald

[Artik]

You can make the code work dependent on the name of the workbook. If the name is different from the defined one, the inside of the condition will not execute:

Private Sub Workbook_Open()
    Dim FileSaveName As Variant
    Dim intchoice As Integer
    
    If UCase(Me.Name) = UCase("My book.xlsm") Then
        Application.EnableEvents = False
        
        Set FileSaveName = Application.FileDialog(msoFileDialogSaveAs)

        FileSaveName.InitialFileName = Me.Name
        FileSaveName.FilterIndex = 2   'select to save with a ".xlsm" extension
        intchoice = FileSaveName.Show

        If intchoice <> 0 Then
            FileSaveName.Execute
        End If

        Application.EnableEvents = True
    End If
End Sub

Artik

[dharvey1978]

Thanks for your assistance Artik this has worked how i wanted it to.

Thanks
Donald

[dharvey1978]

This post is also cross-posted over at MrExcel at the following link sorry for not making it as a cross-post.

https://www.mrexcel.com/board/thread.../#post-6193269

Donald