Solved

VBA-Excel Worksheet_calculate targeting a specific cell

Posted on 2007-11-21
8
8,232 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:Mcnubbin
8 Comments
 
LVL 2

Expert Comment

by:geekspeaknow
Comment Utility
Couldn't you just put something in your own code instead of using Worksheet_Calculate?  Something like:

Range("A1:B18").Select

0
 
LVL 22

Expert Comment

by:spattewar
Comment Utility
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.

$wapnil
0
 

Author Comment

by:Mcnubbin
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Accepted Solution

by:
spattewar earned 125 total points
Comment Utility
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.
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 125 total points
Comment Utility
Mcnubbin,
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:
=TrapCalculationEvent(H2)

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.

Brad
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now