Code Stopped Working

[Minitman]

Greetings,

I recently broke a worksheet off of a large workbook and made it into
a single sheet workbook. I kept the sheet code for the clearing
process. This code is located in the sheet code area. After clearing
all entries the code changed the color of a button and two cells (to
announce that it has finished clearing. Big sheet - took time).

This part is working. It is the second part that stopped working
after the break off!

This second part is set to trigger when the sheet is activated. It
changes the color of the button and the same two cells back to their
original color. It is not triggering.

Here are the subs:

======================================================

Private Sub ButtonClearAll_Click()
If MsgBox("Do you want to Clear ALL data from " & _
"this sheet?", vbYesNo + vbDefaultButton1) = _
vbYes Then ClearSheet
End Sub
__________________________________________________________

Sub ClearSheet()
Range("rInv").Value = vbNullString
ButtonClearAll.BackColor = &HFF00&
Range("C1:C2").Interior.ColorIndex = 4
End Sub
__________________________________________________________

Private Sub Worksheet_Activate()
ButtonClearAll.BackColor = &HFFFF&
Range("C1:C2").Interior.ColorIndex = 6
Calculate
End Sub
====================================================

Range("rInv") is the range cleared. ButtonClearAll is the button that
starts the procedure and Range("C1:C2") are the two cells that have
the color change to signal procedure completion.

Did I miss something?

Anybody see any reason why the Worksheet_Activate event is not
working?

Any help will be appreciated.

-Minitman

 

[John Bundy]

It works fine for me as written. If it is only the activation piece you might
want to copy the code, delete the activation sub, then create a new one by
selecting worksheet in the left dropdown and activate in the right one and
pasting the code in there.

 

[Barb Reinhardt]

In the Immediate window, type this

Application.EnableEvents = True

And try it again.

 

[Charlie]

You said you copied the sheet into a one-sheet workbook? My guess is that
the Worksheet_Activate event never gets fired because for that event to fire
you must come to that sheet from another sheet... something that you don't
have!

 

[Minitman]

Hey Charlie,

Thanks for the reply.

Good catch, I broke that big workbook into 9 workbooks. This
particular workbook has 2 sheets (Enter and Input) all of the others
have only one, my mistake. It is when I go between sheet 1 (Enter) to
sheet 2 (Input) that the activate event is supposed to fire.

I have not had a chance to try out the solutions in the other two
replies yet, I will do that now.

-Minitman

 

[Minitman]

Hey John,

Thanks for the reply.

That worked!!!

Thanks.

-Minitman

 

[Minitman]

Hey Barb,

Thanks for the reply.

Since John's solution worked, it seems to have jarred something loose
in my excel program - now I can't seem to break it!!! <G> I even
tried older versions of the app that caused the problem, no they are
working when they were not before.

Which means I have no way of testing your solution.

Also, I have not used the intermediate window and do not know how it
works or what it does. All I know is that it is there and when I go
into it, I don't understand what it is doing or how it will help me.
So I leave it alone.

-Minitman