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

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,399
VBA Code:
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
 
Solution

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,399
@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.
 

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
@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
 

rolando87

New Member
Joined
Mar 13, 2017
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
Thank you that worked! appreciate your help
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top