Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
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 On Apr 7, 11:27*pm, David P. <David wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
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 On Wed, 7 Apr 2010 15:36:11 -0700 (PDT), Pete_UK wrote: 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 On Apr 7, 11:27*pm, David P. <David wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
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 "Gord Dibben" wrote: 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 On Wed, 7 Apr 2010 15:36:11 -0700 (PDT), Pete_UK wrote: 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 On Apr 7, 11:27 pm, David P. <David wrote: 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. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
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. wrote: 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 "Gord Dibben" wrote: 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 On Wed, 7 Apr 2010 15:36:11 -0700 (PDT), Pete_UK wrote: 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 On Apr 7, 11:27 pm, David P. <David wrote: 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. . -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Run "Workbook Open" once
It appears to be working. You've helped me so many times. Thanks again Dave!
"Dave Peterson" wrote: 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. wrote: 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 "Gord Dibben" wrote: 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 On Wed, 7 Apr 2010 15:36:11 -0700 (PDT), Pete_UK wrote: 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 On Apr 7, 11:27 pm, David P. <David wrote: 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. . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Obsolete workbook names in "Open" | Setting up and Configuration of Excel | |||
conditional formula to show "open" or "closed" | Excel Worksheet Functions |