Hi All......

Is it possible to write a macro that can be installed in a workbook and will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message ...
Hi All......

Is it possible to write a macro that can be installed in a workbook and will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

Cool, thanks John........

I "think" I understand what you said,........ I'll try to get in a very
quiet place over the weekend and try it out......these things drive me nuts
until I get them working........

Vaya con Dios,
Chuck, CABGx3


"John Green" wrote in message
...
Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode"
Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message
...
Hi All......

Is it possible to write a macro that can be installed in a workbook and
will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

eeeeeeeekkkkkkkkkkkkkkkkkkkkk

How about doing this instead (just cos I hate unstructured code)

Sub Auto_Open()
' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything
If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then
ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r
.... do stuff
End IF
Exit Sub


Chrissy.
PS - John, bet we beat you are the world cup ;-)


"John Green" wrote in message ...
Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode" Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message ...
Hi All......

Is it possible to write a macro that can be installed in a workbook and will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

Thanks Chrissy.......thats some clearer (I think)........I've got a long
ways to go here with this VBA stuff.......

What I want to do in the long run is combine this with another thread I
started called "Global macro insertion".......the end result is that I want
to be able to open an Excel Workbook, set up the parameters for a macro, run
it, and it will go to the specified directory and install a "Auto-run ONCE
macro" in each Excel file therein, (and hopefulle eventually into AutoCAD
files as well). Then when each file is opened, the "Auto-run ONCE macro"
will run and do it's thing and never run again unless the special "enable
cell" is enabled......like I said, I've got a long ways to go........

Vaya con Dios,
Chuck, CABGx3


"Chrissy" wrote in message
...
eeeeeeeekkkkkkkkkkkkkkkkkkkkk

How about doing this instead (just cos I hate unstructured code)

Sub Auto_Open()
' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do
anything
If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then
ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r
.... do stuff
End IF
Exit Sub


Chrissy.
PS - John, bet we beat you are the world cup ;-)


"John Green" wrote in message
...
Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode"
Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message
...
Hi All......

Is it possible to write a macro that can be installed in a workbook
and will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

I am almost totally sure that you are doing it all wrong.

A better way to approach it would be to have a worksheet
or AutoCAD file that runs the macro and does things to each
file IF you need it to. That way you have only one copy of
your macro for each applications.

VBA for AutoCAD is the same as VBA for Excel I believe. It
is for MapInfo or was that ArcInfo - anyway -they do things
the same way (almost).

For Excel I would add a macro to the Personal workbook and
run it when I wanted to. It would run for all the workbooks I
have listed in a range of the current worksheet or for the current
workbook depending on what exactly I wanted this macro to do.
It would not add a RunOnce macro to any workbook. If I
wanted some changes to be made to lots of workbooks at the
same time then I would list the workbook names (full path if
needed) in a range of cells on a worksheet then run the macro
which is in Personal.XLS and it would open each workbook
and make the changes to it, save it then close it.

Adding code to code is possible but not usually the "right" way
to do things. In Assembler code and Machine code we call it
self modifying code. It has a purpose and use and is something
that should be used when needed but I do not see any reason to
do it in a high level language. It is usually done for speed and
efficiency issues and if they are an issue then you should not be
using Excel.


Chrissy.





CLR wrote
Thanks Chrissy.......thats some clearer (I think)........I've got a long
ways to go here with this VBA stuff.......

What I want to do in the long run is combine this with another thread I
started called "Global macro insertion".......the end result is that I want
to be able to open an Excel Workbook, set up the parameters for a macro, run
it, and it will go to the specified directory and install a "Auto-run ONCE
macro" in each Excel file therein, (and hopefulle eventually into AutoCAD
files as well). Then when each file is opened, the "Auto-run ONCE macro"
will run and do it's thing and never run again unless the special "enable
cell" is enabled......like I said, I've got a long ways to go........

Vaya con Dios,
Chuck, CABGx3


"Chrissy" wrote in message
...
eeeeeeeekkkkkkkkkkkkkkkkkkkkk

How about doing this instead (just cos I hate unstructured code)

Sub Auto_Open()
' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do
anything
If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode" Then
ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r
.... do stuff
End IF
Exit Sub


Chrissy.
PS - John, bet we beat you are the world cup ;-)


"John Green" wrote in message
...
Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e < "MyCode"
Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message
...
Hi All......

Is it possible to write a macro that can be installed in a workbook
and will
Auto-run the next time the workbook starts up and then never run again
until/unless a special code number is re-inserted in a specific cell?

TIA
Vaya con Dios,
Chuck, CABGx3

Thanks Chrissy........that makes sense to me, I think you have a pretty good
grasp of what I am wanting to do, even beyond my poor explanation. I am the
curator of about 1100 AutoCAD drawings and numerous Excel files, and I want
to be able to make Global changes to these files at will. One thing for
example, I have an Excel sheet inserted into each AutoCAD drawing that
assigns a lot number to each Batch-print job ordered from my Batch-print
Excel utility. This number is thereby printed out on each drawing as it's
plotted. That works fine, but I would like to be able to change the path
and filename reference to this inserted Excel sheet in all of the Autocad
files, or as you suggest, select ones, by running this
"Master-macro".......and other changes like that

I thought the insertion of Autorun-ONCE macros would be the more efficient
way to accomplish things because each file would not have to be open so
long..........and, the insertion need not be functional until the file is
next opened..........."but" it would be disasterous if I "thought" the file
had been fixed and in fact it wasn't, so I guess maybe your way would be
better after all.

Another question, can this master macro open all these files and yet when it
encounters one that is already open it will NOT stop, but will continue on
opening the ones it can and then report back a list of the ones it could not
open......(I could have a code number inserted in each file that could be
read when it's open).......

Thanks for your time and information........ I know it will surely be a
project, and I'm just trying to see if you learned ones think it's a sort of
doable thing before I get too deep into it.......and from what you've told
me so far, it looks like it will all work out........thanks again

Vaya con Dios,
Chuck, CABGx3


"Chrissy" wrote in message
...
I am almost totally sure that you are doing it all wrong.

A better way to approach it would be to have a worksheet
or AutoCAD file that runs the macro and does things to each
file IF you need it to. That way you have only one copy of
your macro for each applications.

VBA for AutoCAD is the same as VBA for Excel I believe. It
is for MapInfo or was that ArcInfo - anyway -they do things
the same way (almost).

For Excel I would add a macro to the Personal workbook and
run it when I wanted to. It would run for all the workbooks I
have listed in a range of the current worksheet or for the current
workbook depending on what exactly I wanted this macro to do.
It would not add a RunOnce macro to any workbook. If I
wanted some changes to be made to lots of workbooks at the
same time then I would list the workbook names (full path if
needed) in a range of cells on a worksheet then run the macro
which is in Personal.XLS and it would open each workbook
and make the changes to it, save it then close it.

Adding code to code is possible but not usually the "right" way
to do things. In Assembler code and Machine code we call it
self modifying code. It has a purpose and use and is something
that should be used when needed but I do not see any reason to
do it in a high level language. It is usually done for speed and
efficiency issues and if they are an issue then you should not be
using Excel.


Chrissy.





CLR wrote
Thanks Chrissy.......thats some clearer (I think)........I've got a long
ways to go here with this VBA stuff.......

What I want to do in the long run is combine this with another thread I
started called "Global macro insertion".......the end result is that I
want
to be able to open an Excel Workbook, set up the parameters for a macro,
run
it, and it will go to the specified directory and install a "Auto-run
ONCE
macro" in each Excel file therein, (and hopefulle eventually into
AutoCAD
files as well). Then when each file is opened, the "Auto-run ONCE
macro"
will run and do it's thing and never run again unless the special
"enable
cell" is enabled......like I said, I've got a long ways to go........

Vaya con Dios,
Chuck, CABGx3


"Chrissy" wrote in message
...
eeeeeeeekkkkkkkkkkkkkkkkkkkkk

How about doing this instead (just cos I hate unstructured code)

Sub Auto_Open()
' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do
anything
If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e = "MyCode"
Then
ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r
.... do stuff
End IF
Exit Sub


Chrissy.
PS - John, bet we beat you are the world cup ;-)


"John Green" wrote in message
...
Chuck,

Place a test at the beginning of the auto execute macro:

Sub Auto_Open()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Valu e <
"MyCode"
Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clea r

.... do stuff


--

John Green - Excel MVP
Sydney
Australia


"CLR" wrote in message
...
Hi All......

Is it possible to write a macro that can be installed in a
workbook
and will
Auto-run the next time the workbook starts up and then never run
again
until/unless a special code number is re-inserted in a specific
cell?

TIA
Vaya con Dios,
Chuck, CABGx3