Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
Hello, I'm a beginner at using Excel macros, and after much cursing I was
finally able to get two macros to run on the same workbook. The one I'm having trouble with takes the data from a particular cell and uses it to rename the file before saving it. The problem is, both macros are still active when opening that new file. This is for invoices. The initial file is a blank invoice where I want the macros to run. The secondary file is saved as the customer's name. I do not want the macros to run on the secondary file. What can be done to auto-disable the macros on the new file? (These are both auto-run macros.) Thanks, Duane 'invoice number advance Private Sub Workbook_Open() Range("m3").Value = Range("m3").Value + 0.1 End Sub 'auto filename change Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisFile = Range("D13").Value ActiveWorkbook.SaveAs Filename:=ThisFile End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
You have to find some way for the macro to distinguish between a workbook
you want to save and one you don't. I don't know what that might be. You might put a "x" in a way out cell like IV1 to mark an 'old' workbook: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Range("IV1").Value = "" then ''New Range("IV1").Value = "x" ''Mark as Old Me.SaveAs Filename:=Range("D13").Value End if End Sub -- Jim "DDub207" <u36243@uwe wrote in message news:75f48f54b5e08@uwe... | Hello, I'm a beginner at using Excel macros, and after much cursing I was | finally able to get two macros to run on the same workbook. The one I'm | having trouble with takes the data from a particular cell and uses it to | rename the file before saving it. The problem is, both macros are still | active when opening that new file. This is for invoices. The initial file | is a blank invoice where I want the macros to run. The secondary file is | saved as the customer's name. I do not want the macros to run on the | secondary file. What can be done to auto-disable the macros on the new file? | (These are both auto-run macros.) | | Thanks, | Duane | | 'invoice number advance | Private Sub Workbook_Open() | Range("m3").Value = Range("m3").Value + 0.1 | End Sub | | 'auto filename change | Private Sub Workbook_BeforeClose(Cancel As Boolean) | ThisFile = Range("D13").Value | ActiveWorkbook.SaveAs Filename:=ThisFile | End Sub | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
if you are basically trying to copy the invoice to a new workbook without the
macros then copy the worksheet to a new workbook and save only the sheet. Private Sub Workbook_BeforeClose(Cancel As Boolean) dim ThisFile, ShName as string ShName = Activesheet.Name Sheets(ShName).Select Sheets(ShName).Copy ThisFile = Range("D13").Value ActiveWorkbook.SaveAs Filename:= _ ThisFile, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ' if you want to change the sheet name to the person's name also or create a new name Windows(ThisFile).Activate ActiveSheet.Name = ThisFile End Sub I didn't try running this from a BeforeClose routine. You may need to create a button on your original worksheet and run the routine from there. "DDub207" wrote: Hello, I'm a beginner at using Excel macros, and after much cursing I was finally able to get two macros to run on the same workbook. The one I'm having trouble with takes the data from a particular cell and uses it to rename the file before saving it. The problem is, both macros are still active when opening that new file. This is for invoices. The initial file is a blank invoice where I want the macros to run. The secondary file is saved as the customer's name. I do not want the macros to run on the secondary file. What can be done to auto-disable the macros on the new file? (These are both auto-run macros.) Thanks, Duane 'invoice number advance Private Sub Workbook_Open() Range("m3").Value = Range("m3").Value + 0.1 End Sub 'auto filename change Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisFile = Range("D13").Value ActiveWorkbook.SaveAs Filename:=ThisFile End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
Thanks for your help. It's basically working except for two things. It's
asking if I want to save changes to "new invoice". I would like the blank invoice, "new invoice" to close automatically without saving changes, otherwise it won't be blank anymore. Also, it's opening the "customer name (D13)" file. I would like the new file to save but remain closed. Is there a way to do this? Also, the last 3 lines of the macro created an error. Thanks again. Rich J wrote: if you are basically trying to copy the invoice to a new workbook without the macros then copy the worksheet to a new workbook and save only the sheet. Private Sub Workbook_BeforeClose(Cancel As Boolean) dim ThisFile, ShName as string ShName = Activesheet.Name Sheets(ShName).Select Sheets(ShName).Copy ThisFile = Range("D13").Value ActiveWorkbook.SaveAs Filename:= _ ThisFile, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ' if you want to change the sheet name to the person's name also or create a new name Windows(ThisFile).Activate ActiveSheet.Name = ThisFile End Sub I didn't try running this from a BeforeClose routine. You may need to create a button on your original worksheet and run the routine from there. Hello, I'm a beginner at using Excel macros, and after much cursing I was finally able to get two macros to run on the same workbook. The one I'm [quoted text clipped - 19 lines] ActiveWorkbook.SaveAs Filename:=ThisFile End Sub -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
Try this -
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ThisFile, ShName As String ShName = ActiveSheet.Name ThisFile = Range("D13").Value Sheets(ShName).Select Sheets(ShName).Copy ActiveWorkbook.SaveAs Filename:= _ ThisFile, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close ' closes the new single sheet workbook Application.DisplayAlerts = False ' inhibits the Save message ActiveWindow.Close ' continues to close the blank invoice End Sub "DDub207 via OfficeKB.com" wrote: Thanks for your help. It's basically working except for two things. It's asking if I want to save changes to "new invoice". I would like the blank invoice, "new invoice" to close automatically without saving changes, otherwise it won't be blank anymore. Also, it's opening the "customer name (D13)" file. I would like the new file to save but remain closed. Is there a way to do this? Also, the last 3 lines of the macro created an error. Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Disable Macros on new sheet
Everything works great. Thanks.
Rich J wrote: Try this - Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ThisFile, ShName As String ShName = ActiveSheet.Name ThisFile = Range("D13").Value Sheets(ShName).Select Sheets(ShName).Copy ActiveWorkbook.SaveAs Filename:= _ ThisFile, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close ' closes the new single sheet workbook Application.DisplayAlerts = False ' inhibits the Save message ActiveWindow.Close ' continues to close the blank invoice End Sub Thanks for your help. It's basically working except for two things. It's asking if I want to save changes to "new invoice". I would like the blank [quoted text clipped - 4 lines] Thanks again. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200707/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable macros? | Excel Programming | |||
Disable macros | Excel Programming | |||
How to disable macros? | Excel Programming | |||
Suppress the Disable Macros / Enable Macros Dialog | Excel Programming | |||
en-/disable macros pop-up | Excel Programming |