Worksheet_Calculate code in same module as Worksheet Change eventgoes astray

[Harold Good]

Hi, I'm trying to get some code to fire, based on a formula in cell E2
changing value to equal 1.

Private Sub Worksheet_Calculate()
If Cells(5, 2) = 1 Then MsgBox "Fires ok"
'Application.EnableEvents = False
End Sub

I can get it to work properly on a blank spreadsheet. However I need to
use it on a worksheet that already has a Worksheet Change code. Since it
seems like each worksheet can only have one module (page? not sure of
the right name) for code, the above code is in the same module as the
Change code.

Thus, when the entry is made that changes E2 to equal 1, it starts in
the above code, then jumps to the Change code and begins running some of
that code. If I EnableEvents=False it stops the straying, but still
doesn't bring up the MsgBox.

Any help would be appreciated.
Harold

 

[Rick Rothstein]

Try your code this way...

If Cells(5, 2) = 1 Then
Application.EnableEvents = False
MsgBox "Fires ok"
Application.EnableEvents = True
End If

 

[Harold Good]

For some reason this did not work. It works fine on an independent
worksheet with standalone code, but not when entered as a separate
procedure below the Worksheet_Change procedure. It still jumps up into
that other code, and does not bring up the msgbox.

Thanks for any further thoughts you may have.
Harold

 

[Rick Rothstein]

What do you mean "entered as a separate procedure below the Worksheet_Change
procedure"? You have to incorporate my posted code into your existing
Worksheet_Change procedure's code... where might depend on when you need it
to execute relative to the other code you have. Without seeing the rest of
your code, it is kind of hard to give you any more direction than this.

 

[Harold Good]

You're right, I don't really know what proper terms to call things. I'll
insert the code here.

On the Project Explorer both the procedures below (Worksheet_Change, and
Worksheet_Calculate) are in the Sheet 5 (Categories) Object. The bottom
one (Worksheet_Calculate) is the problem one. I've set a trap to stop
it on the /"If Cells(5, 2) = 1 Then MsgBox "Fires ok"/" line, then I F8
step thru it. When it finishes the End Sub, it jumps to the top of this
Worksheet_Change code and starts going thru it. So I'm trying to get it
to work properly, then build code around it, ultimately so if it equals
1, then unhide some rows. Thanks again for your kind help. Harold

Private Sub Worksheet_Change(ByVal Target As Range)
'Code below catches any changes made to the green
'table on the Categories page. When any change is made
'the code is triggered and it hides the unused rows of the
'budget on the Budget page.
Dim r As Range, cell As Range
Set t = Target
ActiveSheet.Unprotect Password:="budg"
Range("E10:N29").Interior.Color = RGB(213, 255, 215)
Range("F10").Interior.Color = RGB(255, 255, 189)
Range("E10:N29").Locked = False
Range("F10").Locked = True
If Not Intersect(t, Range("E10:N29")) Is Nothing Then
Dim rngEval As Range
Dim rngHide As Range
Dim rngCell As Range
Set rngEval = Sheets("Budget").Range("BudgetRowsForHiding")
Application.ScreenUpdating = False
For Each rngCell In rngEval.Cells
If rngCell.Value = "" Then
If rngHide Is Nothing Then
Set rngHide = rngCell
Debug.Print rngHide.Address

Else
Set rngHide = Union(rngHide, rngCell)
Debug.Print rngHide.Address

End If
End If
Next rngCell

'Debug.Print rngHide.Address
rngEval.RowHeight = 12.75
Sheets("Budget").Outline.ShowLevels RowLevels:=1
'Because Hidden rows do not remain hidden on the Budget page when I
'expand the Outline, the only other way to make unused rows remain
'hidden is to use a rowheight of .6. This keeps these unused rows
'out of sight while keeping them also out of sight when expanding
'the Outline on Budget page.
Sheets("Budget").Unprotect Password:="budg"

'If there are 30 Account Categories, then don't do the rngHide below.
If rngHide Is Nothing Then
Else
rngHide.RowHeight = 0.6
End If

End If
Sheets("Budget").Shapes("Group Charts").Visible = True
Sheet1.Select
Sheet1.Range("NotesRows").Select
Selection.EntireRow.Hidden = False
Sheets("Budget").Range("H7").Select
Sheet5.Select
Sheets("Budget").Protect Password:="budg"
Application.ScreenUpdating = True
Application.EnableEvents = True
'ActiveSheet.Protect Password:="budg"
End Sub

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Cells(5, 2) = 1 Then MsgBox "Fires ok"
Application.EnableEvents = True
End Sub