[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VBA-Excel Worksheet_calculate targeting a specific cell

Posted on 2007-11-21
8
Medium Priority
?
8,838 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Expert Comment

by:geekspeaknow
ID: 20329674
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
ID: 20329731
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
ID: 20329941
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
spattewar earned 500 total points
ID: 20329966
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 81

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 20330302
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
ID: 20333489
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
ID: 21000177
Forced accept.

Computer101
EE Admin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

656 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