Using a macro to change a macro

[Matthew McManus]

Hi,

I have posted this question before, but didn't get any workable answer
so I'm trying again. I hope there is someone out there who can help.

I have a macro (SaveFiles), which saves various files as zzz1.xls
zzz2.xls, etc. I need to be able to let users save the files with thei
own prefix to the file names - effectively going into the "SaveFiles
macro code, finding the text, "zzz" and replacing it with "yyy", say.

As I don't want to (dare not!) let them access the macros themselves,
need to write a macro that will firstly display an input box for the
to enter the prefix they wish to give the files ("yyy", say), and the
find and replace "zzz" with "yyy" in "SaveFiles".

Does anyone know if this can be done, and if so, how?

Thanks in anticipation

Matthe

 

[Frank Kabel]

Hi Matthew
why don't you include an inputbox in your saveas macro? Wouldn't that
be the easiest way

 

[Bob Phillips]

Hi Matthew,

Modifying code on the fly can be done, and a page at Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm shows some techniques in this area.
But it is not trivial, and as Frank says, it doesn't seem necessary in this
case. This is exactly what variables are for, and if you build the filename
to SaveAs using variables, you can get the user input and plug that value
into the variables on the fly. Same result, much easier to achieve.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Matthew McManus]

Thanks very much for that information - I have worked out a solutio
using your ideas. I have a small associated problem which I will ask i
a new thread.

Thanks again
Matthe