Macro unexpectedly jumps to other macros and functions

[Jeff Wright]

Greetings!

I've spent hours trying to figure out why the following subroutine
unexpectedly jumps to another subroutine [Sub ToggleButton1_Click()] when it
hits the line "Worksheets("Mirrors").ToggleButton1.Value = False" in the
macro below. What would automatically trigger this macro to jump into
another macro - especially when I have the line "Application.EnableEvents =
False" at the beginning of this macro?

Also, if I rem out the line "Worksheets("Mirrors").ToggleButton1.Value =
False", the macro will instead jump to a user function when it hits the line

Worksheets("Mirrors").Range("D8") = "Clear". (I do have a sheet module
subroutine in this workbook with the line Private Sub Worksheet_Change(ByVal
Target As Range), where range "D8" is the target.)

What am I doing wrong? Your help is much appreciated.

Thanks!

Jeff

Sub MIRROR()

Application.EnableEvents = False

Application.ScreenUpdating = False

ActiveSheet.Unprotect

Worksheets("Mirrors").ToggleButton1.Value = False

Worksheets("Mirrors").Range("D8") = "Clear"

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Range("D8").Select

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

[Dave Peterson]

You can try something like:

Option Explicit
Public BlkProc As Boolean

Sub MIRROR()

Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect

BlkProc = True

Worksheets("Mirrors").ToggleButton1.Value = False

BlkProc = False

Worksheets("Mirrors").Range("D8") = "Clear"

ActiveSheet.Protect DrawingObjects:=True, _

Contents:=True, Scenarios:=True
Range("D8").Select
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Then behind the worksheet:

Option Explicit
Private Sub ToggleButton1_Click()
If BlkProc = True Then Exit Sub
MsgBox "HI"
End Sub

Toggling that button isn't an event that application.enableevents worries
about. You have to keep track yourself.

--

Dave Peterson

[Don Guillett]

Probably best to post all of the code for comments

--
Don Guillett
SalesAid Software
don...@281.com
"Jeff Wright" <jeffw...@cox.net> wrote in message
news:RD9pd.127602$cJ3.13504@fed1read06...

[Jeff Wright]

Dave,

After I posted the question to this newsgroup, I realized that I may have
posted to the wrong newsgroup, which is why I reposted to the Excel
programming newsgroup. It wasn't until after this posting that I read
replies in the Excel group, one of which was yours, thanks!

I tried your solution, and it works! Thanks! Can you explain to me why it
works so I can learn something new? For instance, I don't understand the
three lines you added:

BlkProc = True

Worksheets("Mirrors").ToggleButton1.Value = False

BlkProc = False

And is there a relatively simple answer as to why a line in a macro could
trigger another macro?

Again, thanks for your solution!!

Jeff

"Jeff Wright" <jeffw...@cox.net> wrote in message
news:RD9pd.127602$cJ3.13504@fed1read06...

[Dave Peterson]

You've seen how making a change to a cell in a worksheet can trigger the
worksheet_change event--no matter how the cell is changed (manually or via
code).

Well your toggle button essentially got clicked. So the code associated with
that click got called.

By setting up a global variable (blkProc), you can set that variable, toggle the
button (the code still gets called, watch by setting a breakpoint and stepping
through it).

But the difference is that the first thing your togglebutton checks is that
global variable--so it just sees that it's true and gets the heck out.

It actually kind of makes sense, too <bg>.

--

Dave Peterson

[Jeff Wright]

Thanks, Dave, for your explanation. I had to think about it for awhile, but
now it makes logical sense.

Jeff

"Dave Peterson" <ec3...@netscapeXSPAM.com> wrote in message
news:41A5DA83...@netscapeXSPAM.com...