• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9162
  • Last Modified:

VBA-Excel Worksheet_calculate targeting a specific cell

Hello, I was wondering how i can use the Worksheet_Calculate function to target a specific cell or range as opposed to the entire sheet.  My VBA experience is nill so detailed help would be greatly appreciated. Thanks!
2 Solutions
Couldn't you just put something in your own code instead of using Worksheet_Calculate?  Something like:


This event occurs only after the sheet calculation happens and hence if you want to just calculate a specific range of cells or only one sheet you can use the following options in your code and not in this event.

<worksheet>.calculate to calculate only a specific sheet
Range.calculate to calculate a specific range of ccells or cell
Application.Calculate to calculate the entire workbook.

McnubbinAuthor Commented:
I'm sorry, I don't quite understand, I'm trying to trigger a macro whenever a specific cell changes. This cell imports data from an RTD Formula and so updates live.  The problem is that there are several other cells also importing data, so each second, the cell I am looking at may not update however the other cells likely will, thus triggering my macro and the worksheet_calculate function permanently, causing enormous CPU usage.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Then you can use the Intersect method in worksheet_SheetChange event instead of the Worksheet_Calculate event and checkkwhether your cell has got updated and then call the required function.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, <yourRange>) IS NOTHING then
   'your cell has changed
  'call you function
End Sub

Let me know.
You'll need to decide if the cure to your problem is worth it, but you can use VBA code to trap the change to your cell, then schedule a macro to be run immediately thereafter. The required code appears in this thread: http:/Q_22095686.html

What I envision is a simple user-defined function that traps changes to the cell (or cells) of interest. For example, if cell H2 contains your RTD call, you might have a worksheet formula like:

Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long, _
    ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)

' The procedure is called by Windows. Put your timer-related code here. _
        'Replace the two lines below with your own code
MyMacro       'Run sub MyMacro
'Range("C1") = Second(Now())          'For test purposes, put the current second from the system clock  in cell C1

On Error Resume Next
KillTimer 0&, TimerID
End Sub

Function TrapCalculationEvent(cel As Range)
TimerID = SetTimer(0&, 0&, 0&, AddressOf TimerProc)
End Function

You'll find Kevin's code (in the link) to be a little more robust. Mine will fail (crash Excel) if you happen to be editing a cell when the timer pops.

Rory ArchibaldCommented:
If you were only interested in one cell, an easy cheat would be to have a hidden sheet with one cell linked to your cell of interest and use that sheet's calculate event.
Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now