Tring to get macro to execute in background when specific cell in a specific worksheet changes

[rolando87]

Hey guys,

I have a macro that executes when a specific cell changes based on the data incoming from an RTD link. The code I below have almost works but instead of triggering the macro when cell F2 in sheet "15 Mins Bars" changes, it triggers when any cell in the entire workbook changes. Not sure how to specify this in the Dim and Set part of the code as I'm unfamiliar with that section.

Public Prev_Val As Long
Private Sub Worksheet_Calculate()
Dim rngF3 As Range
Set rngF3 = Range("F2")
      If rngF3 <> Prev_Val Then
         Application.EnableEvents = False
         Application.ScreenUpdating = False
 
 
 With Sheets("15 Min Bars").Rows("4:4")
 .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 End With
 With Sheets("15 Min Bars").Range("B2:E2")
        .Copy
  End With
 With Sheets("15 Min Bars").Range("A4")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  End With
    Application.CutCopyMode = False
          Application.ScreenUpdating = True
         Application.EnableEvents = True
       End If
End Sub

 

[AlphaFrog]

Public Prev_Val As Long

Private Sub Worksheet_Calculate()
    Dim rngF3 As Range
    
    With Sheets("15 Min Bars")
        Set rngF3 = .Range("F2")
        
        If rngF3.Value <> Prev_Val Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            
            .Rows("4:4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .Range("A4:D4").Value = .Range("B2:E2").Value
            
            Application.ScreenUpdating = True
            Application.EnableEvents = True
        End If
    End With
    
    Prev_Val = rngF3.Value
    
End Sub

 

[TotallyConfused]

Hello Rolando

I may be reading your post #1 wrong, and if so, I apologize. To me, it sounds as if you want the code to automatically activate the moment cell 'F2' in the '15 Min Bars' sheet changes from its previous value. If this is correct, then try the following code. I took the liberty of getting rid of some of your 'WITH' statements, which I hope will make the code easier to read. Test this on a COPY of your data.

This code MUST be put in a special place. To do so
1 ) Right click on the '15 Min Bars' tab. (You MUST right click)
2 ) Left click on VIEW CODE
3 ) Paste my code into the blank screen that pops up
4 ) Close this screen by clicking on the '15 Min Bars' worksheet
5 ) Type something into cell 'F2' to test the code

Public Prev_Val As Long
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F2")) Is Nothing Then
    Dim rngF2 As Worksheet: Set rngF2 = Sheets("15 Min Bars")
    
    If rngF2.Range("F2") <> Prev_Val Then
          Application.EnableEvents = False
          Application.ScreenUpdating = False
 
           With rngF2
              .Rows("4:4").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
              .Range("B2:E2").Copy
              .Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
              SkipBlanks:=False, Transpose:=False
              Prev_Val = rngF2.Range("F2").Value
           End With
       Application.CutCopyMode = False
       Application.ScreenUpdating = True
       Application.EnableEvents = True
    End If
End If
End Sub

I hope this is what you wanted.

TotallyConfused

 

[AlphaFrog]

@TotallyConfused
The RTD Link in F2 is a worksheet function. It doesn't trigger the Worksheet_Change event procedure.

@rolando87
According to Chip Pearson, you can try using Application.OnData instead of the Worksheet_Calculate event procedure though I have not tried this myself.

Worksheet_Change Not Detecting DDE Updates [SOLVED]

Hello yet again... I'm full of questions today. Thanks to all for the help! At any rate I have a ... Private Sub Worksheet_Change(ByVal Target As Range) routine which works properly when someone interacts with the sheet yet is unable to detect when an embedded DDE data feed updates...

www.excelforum.com

 

[rolando87]

Thanks alpha frog the code you provided works perfectly, didnt need to edit anything

 

[rolando87]

@AlphaFrog Sorry quick question as I'm running into some issues with the RTD link values. I think it has to do with the "Public Prev_Val As Long" part. Sometimes the RTD link is blank or not a number and I get the "runtime 13 error type mismatch". Am I getting this error because it is not a number? Or is it because I am sometimes pasting in a new RTD link so it somehow conflicts with the previous value, if so do you know how to adjust the code so that if I were to paste in a new RTD link in F2 it would not trigger the runtime 13 error?

Thanks a lot for your help

 

[AlphaFrog]

Am I getting this error because it is not a number?

Probably yes. Try this...

Public Prev_Val As Variant

 

[rolando87]

Thank you that worked! appreciate your help