Disable Macros after one has been run

[wyeah]

I have a spreadsheet that has several macros. Is it possible to disable these macros once one of them has been run???

This is an Excel 2002 file running on Windows XP.

 

[Scott Huish]

Try this:

In the General Declarations section of the module put this:

Public flag As Integer

At the beginning of each of your macros, put this:

If flag = 1 Then Exit Sub

At the end of each of your macros, put this:

flag=1

 

[wyeah]

This works great and answers my first question. Now, when I save this file as a new file name, close this file, and open it again all the macros are enabled again. Is there a way to permenantly disable the macros after 1 use??? I know I could tell people to not enable macros and not run any macros when they open the file again but they know this already and it still happens.

What I am doing is filling in a weekly work schedule with a list of people based on the week of the year. I then enter in vacations and absences and such. But if someone runs one of the macros with a different list of people, or the same macro, then it overwrites all the absences information that I put in. Thanks

 

[Scott Huish]

Keeping what we did before, try adding these two things:

To the Workbook Before Save Event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If flag = 1 Then
Range("IV65536").Font.Color = vbWhite
Range("IV65536").Value = 1
End If
End Sub

To the Worbook Open Event:

Private Sub Workbook_Open()
If Range("IV65536").Value = 1 Then flag = 1
End Sub

 

[HalfAce]

HOTPEPPER'S solution works great. The only drawback I can see is what you've mentioned.
You could solve this with a very similar method by changing the flag reference to an unused (hidden if you want) cell. Example:

At the beginning of each of your macros, put this:
If [A1] = 1 Then Exit Sub

At the end of each of your macros, put this:
[A1]=1

This help?
Dan

Edit: I see while I went for a cup of coffee, HOTPEPPER got you squared away.

 

[dlott]

HOTPEPPER,

I'm trying your code above, but can't get it to work. Can you please help?

I'm using a macro to pull the username of the person logged in. However, when the spreadsheet is opened again at a later time, it updates with that person's username and we need it to keep the original.

Here's my code:

Public flag As Integer
If flag = 1 Then Exit Sub
Sub Autpen()
Range("G8").Value = Environ("UserName")
flag = 1
End Sub

 

[kgardner]

I'm probably bringing this post back from the dead - but thank you for this solution! It works perfectly!