0

I have an existing macro that performs a screen capture of the excel sheet if a particular cell is greater than -0.03. Currently this cell is a formula that is based on real time data from external sources. I am able to run this macro manually, but I want it to be run continuously, i.e. automatically take screenshots of the worksheet every time that cell is greater than -0.03.

Here is my code so far;

Private Sub Worksheet_Calculate()

If range("d81")>-0.03 Then
    Application.EnableEvents = False
    Call ScreenCapture
    Application.EnableEvents = True
End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

Please can someone help?

shA.t
  • 16,580
  • 5
  • 54
  • 111
AG6
  • 1
  • 3

2 Answers2

0

Try using this in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If range("d81")>-0.03 Then
        With Application
            .EnableEvents = False
            Call ScreenCapture
            .EnableEvents = True
           .ScreenUpdating = False
           .DisplayAlerts=True
        End With
    End If

End Sub

This will run the macro every time there is a change on the worksheet.

However how is the -0.03 cell generated. If it is user entry then the cell is deactivated (clicked out of, moved away from etc)

If you want it when the value is changed via user entry or vba use the Worksheet_Change Event (Replace Worksheet_SelectionChange in the above with that)

And then if it is a calculated cell use the Worksheet_Calculate() event (Replace Worksheet_SelectionChange(ByVal Target As Range)) to run the macro when the value changes via calculation.

Tom
  • 9,725
  • 3
  • 31
  • 48
  • `This will run the macro every time there is a change on the worksheet.` No that is not true. – Siddharth Rout May 26 '15 at 11:01
  • Thanks for your response. Note that it is a calculated cell. I used the Worksheet_Calculate() event and your code above, however I received the error message 'End if without block if'. – AG6 May 26 '15 at 11:01
  • @AG6: See the link that I posted below your answer. – Siddharth Rout May 26 '15 at 11:02
  • @SiddharthRout sorry I updated the code and not the text- you're correct – Tom May 26 '15 at 11:05
  • @AG6 which line does it say this on? – Tom May 26 '15 at 11:06
  • @Tom Your `end if` is in the wrong Place, it should be the last line. Your one statement should read,"the code will activate whenever a new selection is made in the worksheet" – Davesexcel May 26 '15 at 11:50
  • @Tom - Thanks. This only results in instance of the screen capture if the cell becomes larger than -0.03. In the next instance when it is >-0.03 it does not perform the capture again. I would like the cell check (to see if it > -0.03) to be continuous. Do I need to insert a 'loop' ? – AG6 May 26 '15 at 12:10
  • @AG6 No this should run everytime the cell value is changed. Which Event did you use? – Tom May 26 '15 at 12:13
  • @Tom - It works! Thank you. I copied the code into 'Sheet1' of the VBA Project window and that did the trick. The screen is now captured every time the cell is greater than > 0.03. – AG6 May 26 '15 at 12:22
0

You can do this by using the Worksheet_Change(ByVal Target As Range) on the Worksheet that contains the Cell you are refrencing.

rohrl77
  • 3,277
  • 11
  • 47
  • 73