Solved

VBA-Excel Worksheet_calculate targeting a specific cell

Posted on 2007-11-21
8
8,309 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Accepted Solution

by:
spattewar earned 125 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

22 Experts available now in Live!

Get 1:1 Help Now