Run "Workbook Open" once

  • Thread starter Thread starter David P.
  • Start date Start date
D

David P.

I have a template that when it opens it automatically runs a macro that
allows me to type in a text box what I want to save the file as and then
saves it in the directory where I want it saved. The problem is that once it
is saved I don't want it to run the macro again when I reopen that saved
file. Should I put in the code at the end some way to deactivate the macro so
that it no longer automatically runs when opening that newly saved file?
Thank you.

David P.
 
If, as part of your routine, you were to write the filename to a
specific cell in your workbook before saving, then you can test that
cell to see if it is empty - if so then run the rest of your macro, if
it is not empty then terminate the macro without asking for the
filename etc.

Hope this helps.

Pete
 
I would check to see if the workbook being opened has been saved once by
checking for a path.

If not, do the deed.

Private Sub Workbook_Open()
If ThisWorkbook.Path <> "" Then
Exit Sub
'do the deed
End If
End Sub

Placed in Thisworkbook module of the Template


Gord Dibben MS Excel MVP
 
Thank you Gord. I am a beginner with code. This is what I did below and it
didn't work the way intend at this point. Let's pretend that my file is
called "Calculation Template" and is saved as a template. When I open the
template the macro runs to save it as a customer's name and in a different
directory. So you'll see below the macro I have created and I inserted your
suggestion below where I thought it should be placed. It didn't work but
stopped at the beginning of the macro without allowing me to save it under a
customer's name. Hope that makes sense. Here's what I have right now:

Private Sub Workbook_Open()
If ThisWorkbook.Path <> "" Then
Exit Sub
l = InputBox("Type customer's name & scenario summary:")
Range("G1") = l
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\David &
Aprile\My Documents\Custom Decorators\Installation Detail Sheets\" &
Range("G1").Value _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
On Error GoTo 0
End If
End Sub
 
Maybe...

Option Explicit
Private Sub Workbook_Open()

Dim Resp As String

If ThisWorkbook.Path <> "" Then
Exit Sub
End If

Resp = InputBox("Type customer's name & scenario summary:")

If Trim(Resp) = "" Then
MsgBox "Not saved -- no input!"
Exit Sub
End If

Me.Worksheets(1).Range("G1").Value = Resp

On Error Resume Next
Me.SaveAs Filename:="C:\Documents and Settings\David & Aprile" _
& "\My Documents\Custom Decorators\Installation Detail Sheets\" _
& Resp & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If Err.Number <> 0 Then
Err.Clear
MsgBox "File not saved -- error on saving"
End If
On Error GoTo 0

End Sub

Remember, the test for the path really means that you're creating a new workbook
based on a template file (*.xlt), right???
 
Back
Top