User Indicating the Arguments while code is running

[Hari]

Hi,

1. I have a macro in workbook A with which I activate an already open
workbook called workbook B using

Windows("B.xls").Activate

Problem is Name of the workbook B may change with time or somebody might
change it inadvertently so please guide me on how to take care of the same.
(Actually somebody else will be using/working on this code/workbook and I
wanted to make it robust so that they are not held up due to changed names
etc.)

Is it possible for the user to somehow indicate the workbook while running
the code or rather even better would it be possible that only when the code
gets hung due to name of the workbook being changed user is prompted for
actual workbook name.

2. Also the above code runs in a worksheet called "Raw Data" and similar to
aboev situation it might happen that the name of the worksheet is changed.
Hence would it be possible for the user to be prompted with the correct name
of the worksheet when code execution fails at that point.

3. A query - when we write formulas in excel cells by linking them to other
worksheets cells then when the name of the other worksheet is changed the
formula also gets updated automatically with the new name. Now, if I have
code in which I refer to worksheets then it becomes a problem as I have to
use Ctrl+H to replace with the new names. Is there a more efficient solution
or do I have to live with that?

Regards,
Hari
India

 

[Frank Kabel]

Hi
1. You could use inputbox to ask the user for information

2. use activesheet for referencing the current worksheet

3. Formulas do this automatically. Just test it

 

[Claus Zimmermann]

Interesting Question, I wonder if there is a better way than mine?

The way I currently handle changing of workbook names is in
ThisWorkBook.Workbook_SheetCalculate(ByVal Sh As Object)

Only downside is that application.Calculation must be on for this to work.

Claus