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

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Minitman" wrote:

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

In the Immediate window, type this

Application.EnableEvents = True

And try it again.
--
HTH,
Barb Reinhardt



"Minitman" wrote:

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

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" wrote:

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

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

On Wed, 27 Aug 2008 10:40:00 -0700, Charlie
wrote:

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" wrote:

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

Hey John,

Thanks for the reply.

That worked!!!

Thanks.

-Minitman

On Wed, 27 Aug 2008 10:15:01 -0700, John Bundy
(remove) wrote:

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.

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



On Wed, 27 Aug 2008 10:24:01 -0700, Barb Reinhardt
wrote:

In the Immediate window, type this

Application.EnableEvents = True

And try it again.