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.

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.


.

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.


.

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