User Indicating the Arguments while code is running
 

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

User Indicating the Arguments while code is running
 

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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Hari" schrieb im Newsbeitrag
...
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

User Indicating the Arguments while code is running
 

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



"Hari" wrote in message
...
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