Run a SaveAs formula then delete SaveAs formula

[dharv3y78]

Hi Mr Excel Experts,

Im looking at a way that will enable me to run the following VBA Sub which works and does the SaveAs and opens in the new document but what i want is for the next step is to either Delete or disable the Sub in the new document and leave the orignal document alone. Is this something that can be done????

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

 

[Joe4]

You cannot disable specific Sub procedures - it is a matter of disabling ALL VBA code or not in the file as you open the file.
You cannot control that by VBA. Think about it, if that were possible, people could "force" you to run malicious VBA code.

I think it would be better instead of doing a SaveAs, just copy your data over to a new workbook with VBA code, and then save that.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Run a SaveAs VBA formula then disable the SaveAs formula in Workbook [SOLVED]

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...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[dharv3y78]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Run a SaveAs VBA formula then disable the SaveAs formula in Workbook [SOLVED]

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...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi RoryA,

Sorry for not following the rules and coming back to say that this post was cross-posted. My applogies.

Donald

 

[dharv3y78]

I think it would be better instead of doing a SaveAs, just copy your data over to a new workbook with VBA code, and then save that.

Hi MrExcel Experts

I have a follow to Joe4 suggestion where i am running a SaveAs VBA formula in Macro1 and i keep getting errors in runnging i'm not sure why?? I have code that already runs from ThisWorkbook which works exactly how i want it. And while the code are nearly identical they are different in what they do.

In ThisWorkbook i have the VBA code run just Userform2 then bring up the SaveAs diglog box if A2 equals the ComputerName (This Code Works)

Private Sub Workbook_Open()
'Application.Visible = True
    Dim FileSaveName As Variant
    Dim intchoice As Integer
If Sheet1.Range("A2") = Environ$("Computername") Then
    Userform2.Show
    Unload Userform2
    If UCase(Me.Name) = UCase("Book1.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 If
End Sub

In Macro1 i have the VBA code run bothUserForm1 and Userform2 if A1 and A2 equals blank then i want to bring up the SaveAs diglog box after this but this code is bringing up a Compile error in hidden module1 is there somthing that that i am missing and i can fix this to do what i need it to do. (This Code has a Compile Error)

Option Explicit
Private Sub Auto_Open()
    Dim FileSaveName As Variant
    Dim intchoice As Integer
With Sheet1
    .Visible = xlSheetVeryHidden
    If .Range("A2") = "" Then
        .Range("A1") = ""
        .Range("A2") = Environ$("ComputerName")
        Application.Visible = False
        Userform1.Show
        Userform2.Show
    If UCase(Me.Name) = UCase("Book1.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 If
    ElseIf .Range("A2") <> Environ$("Computername") Then
        MsgBox "THIS FILE IS NOT LICENCED FOR THIS PC", vbInformation, ""
        If Application.Workbooks.Count > 1 Then
            Application.DisplayAlerts = False
            ThisWorkbook.Close
            Application.DisplayAlerts = True
        Else
            ThisWorkbook.Save
            Application.Quit
        End If
    End If
End With
End Sub

Thanks
Donald

 

[Joe4]

You cannot have an ELSEIF after you have closed ALL IFs when END IFs.
So I think you need to remove the "End If" before the "ElseIf".
This would have been easier to see if your code was lined up properly.

This does not return any Complile Errors:

Private Sub Auto_Open()

Dim FileSaveName As Variant
Dim intchoice As Integer

With Sheet1
    .Visible = xlSheetVeryHidden
    If .Range("A2") = "" Then
        .Range("A1") = ""
        .Range("A2") = Environ$("ComputerName")
        Application.Visible = False
        Userform1.Show
        Userform2.Show
        If UCase(Me.Name) = UCase("Book1.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
    ElseIf .Range("A2") <> Environ$("Computername") Then
        MsgBox "THIS FILE IS NOT LICENCED FOR THIS PC", vbInformation, ""
        If Application.Workbooks.Count > 1 Then
            Application.DisplayAlerts = False
            ThisWorkbook.Close
            Application.DisplayAlerts = True
        Else
            ThisWorkbook.Save
            Application.Quit
        End If
    End If
End With

End Sub

 

[dharv3y78]

This does not return any Complile Errors:

Hi Joe4

I'm now getting a Complie Error: Invaild use of Me Keyword in the Bold Section and all i did was change the Sheet from sheet1 to sheet3 and changed the UCase("Book1.xlsm") to my file name i can't see how you didn't get any Compile Errors and i'm getting this one:

Private Sub Auto_Open()

Dim FileSaveName As Variant
Dim intchoice As Integer

With Sheet1
    .Visible = xlSheetVeryHidden
    If .Range("A2") = "" Then
        .Range("A1") = ""
        .Range("A2") = Environ$("ComputerName")
        Application.Visible = False
        Userform1.Show
        Userform2.Show
        If UCase(Me.Name) = UCase("Book1.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
    ElseIf .Range("A2") <> Environ$("Computername") Then
        MsgBox "THIS FILE IS NOT LICENCED FOR THIS PC", vbInformation, ""
        If Application.Workbooks.Count > 1 Then
            Application.DisplayAlerts = False
            ThisWorkbook.Close
            Application.DisplayAlerts = True
        Else
            ThisWorkbook.Save
            Application.Quit
        End If
    End If
End With

End Sub

Thanks
Donald

 

[Joe4]

I just tried compiling the code, not running it, as I do not have your data files, forms, and other VBA code in order to run it.

What exactly are you trying to check with "Me.Name"?
"Me" is not commonly used with VBA code, except maybe in Form code.

If you are trying to confirm the file name, try either this:

ActiveWorkbook.Name

to get the name of the active workbook, or this:

ThisWorkbook.Name

to get the name of the workbook that this code is running from.

 

[dharv3y78]

What exactly are you trying to check with "Me.Name"?

I was trying that If UCase(Workbook Name) = UCase("Book1.xlsm") then show the SaveAs diglog Box

I have got it work by changing the Me.Name to ActiveWorkbook.Name

Thanks for your assistance

Donald

 

[Joe4]

You are welcome.
Glad we were able to help!