Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run a sub only when one cell changes
I know i can run private subs based on something like this
Private Sub Worksheet_Change(ByVal Target As Range) if target.address = "c20" then ......... But in the name of efficiency is there a way to name a sub so that it is not even activated unless a specific cell is changed. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run a sub only when one cell changes
NDBC, But in the name of efficiency is there a way to name a sub so that it is not even activated unless a specific cell is changed. I do not think so. Is cell C20 being changed manually or by a formula? If it is being changed manually, then see below "*Manually:"*. Is cell C20 being change by a formula? If so, then we would have to use the Worksheet_Calculate Event and a memory variable. Please post your workbook - scroll down and see "Manage Attachments". MANUALLY: Right click the sheet tab you want the code in, and click on View Code. Paste the below code there (on the right pane). Code: -------------------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C20")) Is Nothing Then Exit Sub If Target.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub Application.ScreenUpdating = False Application.EnableEvents = False 'The rest of your code goes here Application.EnableEvents = True Application.ScreenUpdating = True End Sub -------------------- Have a great day, Stan -- stanleydgromjr ------------------------------------------------------------------------ stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119573 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run a sub only when one cell changes
Is cell C20 being change by a formula? If so, then we would have
to use the Worksheet_Calculate Event and a memory variable. That is not necessarily true. Consider this Change event code to monitor if the value from the formula in C20 has changed... Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range For Each C In Target If Not Intersect(C.Dependents, Range("C20")) Is Nothing Then MsgBox "C20 just changed!" Exit For End If Next End Sub To test it, put some numbers in, say, A1:A10 and some more numbers in B1:B10, then put this formula in C20... =SUM(A1:A10,C1:C10) Now, change some values in the range A1:A10 or C1:C10 -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Programming | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |