Macros will not run with two Excel applications open

[RJQMAN]

After 10 months of writing, my program finally is finished! I have an
issue, though (I guess a program is never really finished...) I send
my Excel program out to various unskilled people to use to keep score
at school events. If they have a second Exel application open, the
Macros seem to not know which program they are attached to, and my
program crashes every time.

Is there any way to tie the macros in my program to my program? I
cannot use the program name in the Macro lines, even though the
program name is constantly changed by the various users. Even if the
user makes a copy of the program (as they are instructed to do as a
backup), and both the copy and the original are active at the same
time, crash! I think there is probably a simple solution, but I have
no idea what it is. Perhaps there is some way to have the program
read its name and tie the macros to the name when it is opened? I do
not know how to find this information without help. Could someone
guide me on this one? Your help on this group is always greatly
appreciated.

 

[Patrick Molloy]

thats no surprise. Its impossible to build "idiot" proof applications

usually though, this cab be avoide through correct use of worksheets etc

so not
(1)
Selection.Clear

but
(2) Thisworkbook.worksheets("sheet1").range("B5:G5").ClearContents


with (1) whatever was actually selectyed would be cleared while with (2)
onlt one specific range would clear

so if yuor code asks the user to open a workbook, set it to a workbook oblect


eg

Dim wb As Workbook
Set wb = Workbooks.Open(Application.GetOpenFilename())

now you have a handle, wb , on their workbook it ehey open another, your
code referencing wb whould not break
thats teh idea...if its taken 9 months, then wow, you may have a lot to change

 

[RJQMAN]

thats no surprise. Its impossible to build "idiot" proof applications

usually though, this cab be avoide through correct use of worksheets etc

so not
(1)
Selection.Clear

but
(2) Thisworkbook.worksheets("sheet1").range("B5:G5").ClearContents

with (1) whatever was actually selectyed would be cleared while with (2)
onlt one specific range would clear

so if yuor code asks the user to open a workbook, set it to a workbook oblect

eg

Dim wb As Workbook
Set wb = Workbooks.Open(Application.GetOpenFilename())

now you have a handle, wb , on their workbook it ehey open another, your
code referencing wb whould not break
thats teh idea...if its taken 9 months, then wow, you may have a lot to change






- Show quoted text -

In other words, I just need to insert "ThisWorkbook." in front of the
word "Sheets" or "Worksheets" in each line of code in VBA, and that
should make it work. It did a quick test, and it seemed to do it -
but I wanted to be sure. That can be done with a find-replace, and
should be pretty simple to implement. If that is correct, I will do
it today! And I can take out those warning messages that I had in
there too. Many thanks.

 

[Jim Thomlinson]

Thisworkbook always refers to the workbook that is running the code. The
parent object of thisworkbook is Application which is the instance of XL that
is running the code.

If you do not explicitly refer to thisworkbook then the default parent is
activeworkbook (depending where the code resides). While I avoid multiple
instances of XL so I can not really comment I suspect that your issue
revolves from here.

In short... Definintly use thisworkbook...

 

[RJQMAN]

Thisworkbook always refers to the workbook that is running the code. The
parent object of thisworkbook is Application which is the instance of XL that
is running the code.

If you do not explicitly refer to thisworkbook then the default parent is
activeworkbook (depending where the code resides). While I avoid multiple
instances of XL so I can not really comment I suspect that your issue
revolves from here.

In short... Definintly use thisworkbook...
--
HTH...

Jim Thomlinson






- Show quoted text -

Many thanks to you both.