Auto-run ONCE macro

[CLR]

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

 

[John Green]

Chuck,

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

Sub Autpen()

If ThisWorkbook.Worksheets("Sheet1").Range("A1").Value <> "MyCode" Then Exit Sub

ThisWorkbook.Worksheets("Sheet1").Range("A1").Clear

.... do stuff

 

[CLR]

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

 

[Chrissy]

eeeeeeeekkkkkkkkkkkkkkkkkkkkk

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

Sub Autpen()
' Enter "MyCode" into cell A1 of Sheet1 if you want this code to do anything
If ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = "MyCode" Then
ThisWorkbook.Worksheets("Sheet1").Range("A1").Clear
.... do stuff
End IF
Exit Sub


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

 

[CLR]

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]

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

 

[CLR]

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]

The way I would do this is to have a master excel workbook that
lists all the files you have in your system. It would have the
directory in which they are stored and the file name in two
different cells in one row. Each row would be used for one of
the files. You could also have one column for each change you
make and another one for the category of file - maybe Excel or
AutoCAD - you could add columns for any other categories of
information you want to add.

You could even have one column for each item you put on the
embedded Excel sheet in each AutoCAD document.

Each time you wanted to run your code you would make a new
procedure that would do the work. This would have to be done
each time you needed to do something to all the files as you cannot
write all the procedures now because you do not know what work
you want to do in two years time.

I would keep a list of these procedures on another worksheet in
this document and have the parameters needed for that procedure
in columns beside the procedure name. This would be a named range
and that range would be used within the code to display a list box of
all the procedures I have used. When I ran the code it would display
this list box and I would select which procedure I wanted to run this
time. The next thing it would do would be to display another screen
which used the other info on the line for that procedure and showed
all the parameters that the procedure needs and what values I used
for that parameter last time I used that procedure. There would be
a place to change these values and a "run" button.

The program would then go down the list of files and open each one
and perform the listed procedure on it. If the file was open already
then it could either add that file name to another list of "not done"
files to do later or it could make the changes to the file anyway - if
it is opened and locked by someone else then it cannot change it but
if it is opened by you then you can always just link to that opened
file and change it.

The program would, once each file is changed and saved and closed,
record a "Y" in the column for this change - that column would be on
the file list worksheet and would be headed with a "name" and a date/time
stamp of the time that the macro was run. Better still, it would have a
"name" on the column and the value in the cell on the row for each file
would be the date/time stamp using NOW(). The "name" for the column
heading would come from the parameters form that the user fills in with
parameter values after selecting the procedure. This would be an explanation
of what the change was for.

The program would decide if you want to run the selected procedure for all
files or only specific files based on your selected criteria. One of these criteria
would be that the file was(was not) changed with a specific run. This way you
can no reprocess all the files that you added something to and made a mistake
and you can add something to all the missed files from the last run.

To get the list of file I would have a procedure that searched the computer
system for any files with XLS or what ever the extension of the AutoCAD
files is. It would store these in the worksheet for me and I would then go
through this list adding a comment to the "comment" column and marking
any file that I did not want in the system. I would then sort this list on that
column and delete all the ones marked for deletion - or better still, I would
leave the in the list and just not process them because they had an "X" in the
"Include this file" column. That way I could rescan the computer system
every once in a while and pick up any new files and make sure that they were
added to the system - of course, if this system is not going to have anything
added to it then that would not be necessary - but "never" is a rather permanent
thing and better to cover all bases.

Anyway - that is how I would write this system and it should only take a few hours
to write and test with that outline ;-) It is important to get your plan sorted out
before you code - it makes for a more robust system - I know this is very hard if
you do not know the programming language well or are not a programmer but it
does sort the women from the girls ;-) The process you appear to be using is
one of working out one thing you need to do and get that working then working out
the next thing. The problem is that the second thing may not be possible so you
have to redo the first thing.

To check if what I have here is correct (after all, systems analysis usually requires
more info that one gets in a couple of news group posts) I suggest that you actually
write down all the steps you do if you make the changes to these files manually. I
suggest that these steps are

- open file
- change file
- save file
- close file

The problem with news groups like this are that the question poster asks a question
based on what he/she thinks they need to know to get something done. They usually
do not know EVERYTHING that is possible and are just asking for clarification on
one small point. They usually have little idea of what all the issues are and only ask
what they think they have identified as the problem. The people who answer questions
tend to answer specific questions and not suggest other ways to do things. This is
totally understandable and these people are not mind readers. If someone asks
"how do I add an auto run once macro to code" then maybe that is what they want
to do - maybe it is what they think they need to do to get the results they need.
For this reason, it can pay to say what you are trying to achieve then suggest how you
think you can achieve it. You may get answers that save you hours and hours in the
end. With some posts it is very easy to see where the question asked is not the
right way to do things but most of the time it is not apparent what the person is trying
to do in the whole worksheet - only the little bit they asked about).
Good luck.

Chrissy.



CLR wrote

 

[CLR]

EGAD!!!!......methinks you are inside my head!!!.........when you write it
all out like that it seems to be exactly what I am thinking but do not know
how to express........you are AMAZING!!!

You have given me much more than I can think about at one time, but it
appears to be written plain enough so that my little mind can eventually
grasp all of it, and I do appreciate the time and effort you've taken to do
this. This is an exciting project to me, and you've given me a solid
direction to go in, and the confidence that it can be
accomplished.......more than I had hoped for.

Your insight, patience, and of course, Technical Expertise is most sincerely
appreciated.

Thanks once again,
Vaya con Dios,
Chuck, CABGx3

 

[Chrissy]

CLR wrote

EGAD!!!!......methinks you are inside my head!!!.........when you write it
all out like that it seems to be exactly what I am thinking

That is EXACTLY what my daughter used to say when she was 3 years old
and upset - then she would add "it is like you are me so I don't exist". She
also said it was like there was a pipe from her head to my head.

but do not know how to express........you are AMAZING!!!
Thanks.


You have given me much more than I can think about at one time, but it
appears to be written plain enough so that my little mind can eventually
grasp all of it, and I do appreciate the time and effort you've taken to do
this. This is an exciting project to me, and you've given me a solid
direction to go in, and the confidence that it can be
accomplished.......more than I had hoped for.

Your insight, patience, and of course, Technical Expertise is most sincerely
appreciated.

Take it one step at a time - and try to work out what you want to happen
from an operational point of view - not from an Excel point of view. After
you know that, you are in a better position to translate it to Excel.


Chrissy.