Run "Workbook Open" once

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

 

[Pete_UK]

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

 

[Gord Dibben]

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

 

[David P.]

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

 

[Dave Peterson]

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

 

[David P.]

It appears to be working. You've helped me so many times. Thanks again Dave!