Interaction between macros

[OldSoldier]

Hi out there,
I have a problem that I have spent hours on without success:
I have 2 worksheets, amongst many, for a user to complete, they are both identical in all respects, including macros that unprotect and hide rows dependent on cell content. The different names being "Hotel 1" & "Hotel 2" (the information input into each of these worksheets is different such as dates, hotel names etc.)
The problem is that the codes work perfectly on either of these worksheets until the second is open and used. The codes appear to interact somehow and conflict with each other (it is as if the codes/worksheets are linked in some way).
Any suggestions will be gratefully received.
Regards,
OldSoldier

[shg]

The codes appear to interact somehow

In what way, what happens?

[Richard Buttrey]

Hi out there,
I have a problem that I have spent hours on without success:
I have 2 worksheets, amongst many, for a user to complete, they are both identical in all respects, including macros that unprotect and hide rows dependent on cell content. The different names being "Hotel 1" & "Hotel 2" (the information input into each of these worksheets is different such as dates, hotel names etc.)
The problem is that the codes work perfectly on either of these worksheets until the second is open and used. The codes appear to interact somehow and conflict with each other (it is as if the codes/worksheets are linked in some way).
Any suggestions will be gratefully received.
Regards,
OldSoldier

Hi,

Is it possible that you are manually changing the active workbook? This sometimes happens when you are de-bugging code and if you happen to leave the wrong workbook as active, and step through code, you could for instance be using variables already declared in one book in the other book.

Do the macros themselves have any code that activates other books?

You'll probably need to attach them here and describe in what circumstances they interact and how you know - i.e. what's changing, you know your data better than we do.

Rgds

[OldSoldier]

Hi Richard,
Thank you for the promt response.
No there is no reference to outside workbooks within the code, although the code itself has been copied through various workbooks during development and testing, there also no outside links through the Excel side of the business.
I'd be happy to send the codes for you to look at but I need advice on how to do so.... I got told off last time for sending code incorrectly (something about code tabs). If you can advise, I will then send.
Regards,
OldSoldier

[shg]

got told off last time for sending code incorrectly (something about code tabs).

Code tags.

Push the button with the # sign, and paste your code between the tags. Or select the code and push the button. Or type [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] after it.

[OldSoldier]

Hi Richard.
Thanks for the advice, here goes:

Please Login or Register  to view this content.

As I indicated this is duplicated on an identical second sheet
there is no confliction with the first secion that sets the date but the second section somehow interacts. I was wondering if the code included the worksheet number as well as the cell if that would solve the problem but I'm not sure where or how to do this. The trouble with being a pensioner, the brain is no longer flexible!!
Regards,
OldSoldier

[shg]

You might try explicitly referencing the sheet in the Calculate event:

Please Login or Register  to view this content.

You might also replace the hard-coded ranges with named ranges so worksheet changes don't affect the code.

[Richard Buttrey]

Hi Richard.
Thanks for the advice, here goes:

Please Login or Register  to view this content.

As I indicated this is duplicated on an identical second sheet
there is no confliction with the first secion that sets the date but the second section somehow interacts. I was wondering if the code included the worksheet number as well as the cell if that would solve the problem but I'm not sure where or how to do this. The trouble with being a pensioner, the brain is no longer flexible!!
Regards,
OldSoldier

Hi,

Yes there is a conflict, not between the sheets, but within a sheet.
You have a Worksheet_Selection Change event and a Worksheet_Calculate event.

In the Calculate event you have some code which compares E15 and E36, and if they are the same, the code hides some rows. When rows are hidden, Excel has to perform a calculation because there are some functions, like SUBTOTAL() which when it sums ignores hidden rows. So as soon as the rows are hidden, the Excel detects another potential recalculation and jumps back to the start of the Worksheet_Calculation event. Hence you end up in a continuous loop.

That appears to be behind your problem.

HTH

Rgds

[OldSoldier]

Hi SGH,
An excellent solution, all now works perfectly, much gratitude from the aged !!
Regards,
OldSoldier