Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9019
  • 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!
0
Mcnubbin
Asked:
Mcnubbin
2 Solutions
 
geekspeaknowCommented:
Couldn't you just put something in your own code instead of using Worksheet_Calculate?  Something like:

Range("A1:B18").Select

0
 
spattewarCommented:
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
 
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
spattewarCommented:
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
 
byundtCommented:
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
 
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.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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