Worksheet_Activate event problem

[ShaneDevenshire]

Hi Folks,

On the PC if you place the following code in the Sheet1 object

Private Sub Worksheet_Activate()
Msgbox "Hello World"
End Sub

everytime you move from another sheet to sheet1 you get a message box.

On my Mac the message box displays if and only if the VBA Sheet1 module is
open and then when you click the OK, Excel displays the module?

If on the otherhand the VBE is closed nothing happens? When you reopen the
VBE the message box is displayed?

This behavior is completely different than on the PC, how does one get this
to work properly?

 

[JE McGimpsey]

On the PC if you place the following code in the Sheet1 object

Private Sub Worksheet_Activate()
Msgbox "Hello World"
End Sub

everytime you move from another sheet to sheet1 you get a message box.

On my Mac the message box displays if and only if the VBA Sheet1 module is
open and then when you click the OK, Excel displays the module?

If on the otherhand the VBE is closed nothing happens? When you reopen the
VBE the message box is displayed?

Bizarre. It works for me in MacXL98 through MacXL04 the same way it does
in WinXL - i.e,. it fires when Sheet1 is activated, regardless of the
state of the VBE or modules within it.

What version (and update) of MacXL are you running?

 

[ShaneDevenshire]

Hi JE,

Sorry, took so long to respond. I am using Excel 2004 V 11.2 (050714)

I am running Mac OS X V 10.4.2.

If this helps let me know.

 

[JE McGimpsey]

Sorry, took so long to respond. I am using Excel 2004 V 11.2 (050714)

I am running Mac OS X V 10.4.2.

If this helps let me know.

Your code works for me with XL2004 v. 11.3.3 and Mac OS X 10.4.8. It
might be worth updating.

However, it has "always" worked for me for XL04 and Mac OS X 10.3+. I
use the Activate and Deactivate events all the time in my commercial
applications.

Do you have any other event code running in the workbook, or does it
happen with a new workbook too?

The only other thing I can think of is module corruption. That happens a
lot with MacOffice apps and VBA modules.

It doesn't always manifest itself in the run-time environment, but I've
seen it do so, especially event code that seems to get disconnected from
the module after it's compiled and run.

To combat that, I regularly (often daily) recreate my development
workbooks/documents by transferring the code to a new workbook. When it
comes to the inner workings of the VBE I'm a bit superstitious, so I
copy all but the last character in the module, paste the text into a
text editor document, add the final character, copy the text and paste
it into the new workbook. Probably a lot of extra work for nothing, but
it seems to work.

After the code is finalized (almost always in an add-in), I haven't had
any corruption in years, now, so it appears to me to be a problem
inherent in the VBE.

 

[ShaneDevenshire]

Hi JE,

I suspect that it is a corruption problem. I remember having something
similar happen in an earlier version of Excel PC. I tried the events in
another new file, and did not have the problem.

I had the problem with the Workbook_Open, WorkbookSheet_Activate,
Sheet_Activate events. But since its a corruption problem I think we can say
its solved.