Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Save As stop running macro
I have a macro that changes the attributes of an Excel file when I open the
file, from read only to readwrite, then increment a cell (A1) by one, save the file then changes the attribute back to read only so that when I make changes to the file I should Save As and not save it and overwrite the original one. The problem is that when I save the file, if I decided to open the new file to review, the macro will run again and will increment A1. What shall I do so that when the user Save As the file, the macro wont be saved in the new file, or it wont run when the user reopen the file to review. Any help would be higly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Save As stop running macro
Hi M K W,
If I understand you right, you want to be able to save the workbook without updating cell(A1)? Try the following VBA-code and activate it by a Shortcut key: Sub close1() ThisWorkbook.Sheets(1).Range("A1") = Range("A1").Value - 1 ThisWorkbook.Close End Sub I hope this will do the trick! Ron -----Original Message----- I have a macro that changes the attributes of an Excel file when I open the file, from read only to readwrite, then increment a cell (A1) by one, save the file then changes the attribute back to read only so that when I make changes to the file I should Save As and not save it and overwrite the original one. The problem is that when I save the file, if I decided to open the new file to review, the macro will run again and will increment A1. What shall I do so that when the user Save As the file, the macro wont be saved in the new file, or it wont run when the user reopen the file to review. Any help would be higly appreciated. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Save As stop running macro
Thanx for your reply Ron.
It's not to save the work bookwithout updating A1, I'll explain again: When I open the original file, the macro will run and will do the following: change the file's attribute from readonly to readwrite, update A1 by 1, save the file (without closing it), changes its attribute again from readwrite to readonly so that when someone edits the file he can't Save it but Save As the file. When I re-open the new file (not the original, the second) the macro will run and do the previous steps. Here, I don't want the macro to run, when opening the second file I mean. In other words, I want the macro to run only when I open the original file and after I "Save As" it I dont want it to run when I open the new "Saved As" file. Hope I explained enough. Thanx for any other help "Ron" wrote in message ... Hi M K W, If I understand you right, you want to be able to save the workbook without updating cell(A1)? Try the following VBA-code and activate it by a Shortcut key: Sub close1() ThisWorkbook.Sheets(1).Range("A1") = Range("A1").Value - 1 ThisWorkbook.Close End Sub I hope this will do the trick! Ron -----Original Message----- I have a macro that changes the attributes of an Excel file when I open the file, from read only to readwrite, then increment a cell (A1) by one, save the file then changes the attribute back to read only so that when I make changes to the file I should Save As and not save it and overwrite the original one. The problem is that when I save the file, if I decided to open the new file to review, the macro will run again and will increment A1. What shall I do so that when the user Save As the file, the macro wont be saved in the new file, or it wont run when the user reopen the file to review. Any help would be higly appreciated. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
When Save As stop running macro
I think that this does what you want.
Option Explicit Sub auto_open() Dim oldAttr As Long Dim IsItACopy As Boolean With ThisWorkbook IsItACopy = False On Error Resume Next IsItACopy = Evaluate(.Names("copyofmaster").RefersTo) On Error GoTo 0 If IsItACopy = True Then Exit Sub End If oldAttr = GetAttr(.FullName) If (oldAttr And vbReadOnly) Then SetAttr .FullName, vbNormal .ChangeFileAccess xlReadWrite End If With .Worksheets("sheet1").Range("a1") If IsNumeric(.Value) Then .Value = .Value + 1 Else 'what to do if not numeric???? End If End With .Save .Names.Add Name:="copyofmaster", _ RefersTo:=True, Visible:=True .SaveCopyAs Filename:="book2.xls" 'whatever you're doing here .Names("copyofmaster").Delete .Save SetAttr .FullName, oldAttr .ChangeFileAccess xlReadOnly End With End Sub It sounds like you have all the code except for stopping the macro from running in the copy of the workbook. I defined a workbook name and just check that to see if it's been set. If it's been set, I know that I'm working on a copy and shouldn't continue. And I wasn't sure how you determine the name of the copy. (And I assumed you meant that the file was marked readonly via Windows (Like in windows explorer|Rightclick|properties stuff.) M K W wrote: I have a macro that changes the attributes of an Excel file when I open the file, from read only to readwrite, then increment a cell (A1) by one, save the file then changes the attribute back to read only so that when I make changes to the file I should Save As and not save it and overwrite the original one. The problem is that when I save the file, if I decided to open the new file to review, the macro will run again and will increment A1. What shall I do so that when the user Save As the file, the macro wont be saved in the new file, or it wont run when the user reopen the file to review. Any help would be higly appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Macro running when I save the workbook | Excel Worksheet Functions | |||
Save To Csv Running A Macro | Excel Discussion (Misc queries) | |||
How do I stop a Macro from running? | Excel Worksheet Functions | |||
How to stop getting the file save box when running a macro | Excel Discussion (Misc queries) | |||
How to Pause or Stop a running Macro | Excel Programming |