change trigger from Worksheet_Change to Woorksheet_Calaculate

williamu

New Member
Joined
Mar 19, 2019
Messages
16
I'm trying to change the trigger from Worksheet_Change to worksheet_calculate cannot get it to work.


Private Sub Worksheet_Change(ByVal target As Range)

If target.Cells.Count <> 1 Then Exit Sub
If target.Address <> "F13" Then Exit Sub
If (target.Value >= 0) And (target.Value < 0) ThenExit Sub

Pictures("Picture 38").Visible = (target.Value>= 0)
Pictures("Picture 40").Visible = (target.Value< 0)
End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,761
Office Version
365
Platform
Windows
The Calculate event does not have a "target", it simply triggers whenever any cell on the sheet re-calculates.

Assuming F13 is a formula, do all the cells it relies on, exist in the same sheet?
 

williamu

New Member
Joined
Mar 19, 2019
Messages
16
yes F13 is a formula, yes all the cells it relies on are in the same worksheet, I need F13 to be the target, is there another way to have F13 trigger it besides calculate.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,761
Office Version
365
Platform
Windows
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCell As Range, KyCells As Range

   Set KyCell = Range("F13")
   If Target.Cells.Count <> 1 Then Exit Sub
   On Error Resume Next
   Set KyCells = Union(KyCell, KyCell.Precedents)
   If Not Intersect(Target, KyCells) Is Nothing Then
      Pictures("Picture 38").Visible = (KyCell.Value >= 0)
      Pictures("Picture 40").Visible = (KyCell.Value < 0)
   End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,302
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top