VBA Addin to Monitor Active Sheet Changes

[sean262123]

Hello,

I need to figure out how to write a VBA addin to provide continual updates to the Active Worksheet as the user goes about his business inputting values.

The workbook doesn't have (or at least doesn't start) with any macros in it...it all has to be run out of the addin.

There are two lines of thought I've been thinking of:

1) Allow user to start addin as a menu item, and put addin's vba code in a loop. Problem is I haven't found a way to let the worksheet be updated while a macro is running. I tried application.wait - blocked user events. I tried DoEvents but the loop seemed to self terminate whenever the worksheet changed.


2) If there was a way to tie the Addin to worksheet change events. Haven't written a .com Addin yet but I read that may do it, would this be a successful route? Is it the only route?

[Paul_Hossler]

continual updates to the Active Worksheet

1. What kind of continual updates?

2. I assume that the "user goes about his business inputting values" also on the active sheet??

Paul

[sean262123]

Ex. Monitoring sheet for when cells of a certain column are filled in, analyzing the contents, and filling in another cell on the same sheet based on the value. Very basic macro.

I want a user to be able to use such a macro on their workbook, even pre-existing ones. If I were writing a macro for a specific workbook this would be very simple - a few lines of code in the worksheet change event. But I need something I can distribute to users who already have workbooks.

I'm thinking a com Excel addin, but I'm not sure how far back those are compatible.

Perhaps there is a way to link a regular VBA style Addin with a worksheet's events I'm unaware of. Or Perhaps a way to time a macro to run intermittently (every second or so) that doesn't prevent the user from inputting values on the worksheet.

[Paul_Hossler]

Maybe this would give you some ideas

Add a Class Module called clsExcelApp

[vba]
Option Explicit
Private WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rCell As Range, rData As Range
On Error GoTo NiceExit
With Sh
'signature in case not all sheets need monitoring
If .Cells(1, 1).Value <> "MONITOR" Then Exit Sub
' only checking col B for test/demo
Set rData = Intersect(.Columns(2), Target)
For Each rCell In rData.Cells
If Len(rCell.Value) = 0 Then
rCell.Offset(0, 1).Clear
ElseIf rCell.Value < 0# Then
rCell.Offset(0, 1).Value = "Less than zero"
ElseIf rCell.Value > 0# Then
rCell.Offset(0, 1).Value = "More than zero"
Else
rCell.Offset(0, 1).Value = "Equal to zero"
End If
Next
End With
NiceExit:
End Sub
Private Sub Class_Initialize()
Set App = Application
End Sub
[/vba]

Add this to the ThisWorkbook module

[vba]
Option Explicit
Private XLApp As clsExcelApp
Private Sub Workbook_AddinInstall()
Set XLApp = New clsExcelApp
End Sub

Private Sub Workbook_Open()
Set XLApp = New clsExcelApp
End Sub
[/vba]

And save as an AddIn

It needs (a lot) of customizing, and probaly more error handling, but the basics seem to work

Paul