Macro unexpectedly jumps to other macros and functions

  • Thread starter Thread starter Jeff Wright
  • Start date Start date
J

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
 
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.



Jeff said:
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
Click to expand...
 
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
 
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>.

Jeff said:
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 said:
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
Click to expand...
Click to expand...
 
Thanks, Dave, for your explanation. I had to think about it for awhile, but
now it makes logical sense.

Jeff

Dave Peterson said:
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>.

Jeff said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top