[AccessD] OT: Excel Worksheet_Change event

Thanks Mark - Calculate event with a constant for the cells to check did it!
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Mark Simms
Sendt: 11. oktober 2010 16:39
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] OT: Excel Worksheet_Change event

Use the worksheet Calculate event handler:
Here is a simple 1 cell example...

Private Sub Worksheet_Calculate()

Const CELLTOCHECK as String = "E14" ' this can also be a global variable or
constant
Static vPrev as Variant
Dim vCurr as Variant

vCurr = Activesheet.Range(CELLTOCHECK).Value
If vCurr <> vPrev Then
  DoSomething
  vPrev = vCurr ' persist the current value
End If

End Sub

Private Sub DoSomething()
' logic goes here to perform some function when a cell changes
End Sub

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: Sunday, October 10, 2010 7:11 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] OT: Excel Worksheet_Change event
>
> Hi group
> Say I have an Excel worksheet linked to an Access table or to
> another Excel workbook. When data in the Access table or the
> other Excel workbook changes and then updates my worksheet I
> want code to automatically perform some action. How to?
> A first guess would be to use the Worksheet_Change event for
> the worksheet.
> But problem is that this event only triggers when new data
> are manually *entered* in a cell, not when the cell *updates*
> being linked to an external source by reference, i.e. by a
> formula or a table link.
> So calling any Excel genius on this list...
> Asger


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com