VBA EventChange doesn't continue updating

[wirobins]

Hi guys,

I'm having an issue with some VBA code in a work project.

Basically I created a Module (SumColor) which selectively sums the row based on defined cell fill colors. This works nicely, but Excel unfortunately does not include an event to recognize formatting changes, so my user defined formula (SumColor) does not update the totals when cell colors change. To get around this I used the code below to simulate a color change event and recalculate the formulas appropriately.

It works great, except that after closing and reopening the file, the below VBA does not run, and the rows are not recalculated. However, just making some inconsequential changes to the code (extra spacing, etc) will activate it again.

I've also tried adding "Application.EnableEvents = True" to the Workbook_BeforeClose event, but it hasn't had an impact.

I am running Excel 2007 with all Macros enabled.

Any ideas?

Thanks!


Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Excel.Range
If OldCell Is Nothing Then
Set OldCell = ActiveCell
End If
' movement within ColorCells:
If Not Application.Intersect(Target(1, 1), Range("F7", "AZ16")) Is Nothing Then
Me.Calculate
' movement out
ElseIf Application.Intersect(Target(1, 1), Range("F7", "AZ16")) Is Nothing Then
If Not Application.Intersect(OldCell, Range("F7", "AZ16")) Is Nothing Then
Me.Calculate
End If
End If
Set OldCell = Target(1, 1)

End Sub

 

[doofusboy]

You do realize your code is in Worksheet_SelectionChange and not Worksheet_Change, right?

 

[wirobins]

I was aware, but I thought this was correct given what I was attempting to recalculate (ie. just a range within the sheet). Could that be a source of my problem?

I actually copied the code from:

Simulating A Color Change Event
http://www.cpearson.com/excel/colors.aspx

Thanks!

 

[doofusboy]

All depends on WHAT you want to trigger your code.

Worksheet_SelectionChange, gets triggered when user selects a cell.

Worksheet_Change, gets triggered when a cell changes.

 

[wirobins]

Right, I believe I MUST use SelectionChange because Excel does not reconize cell color change as an "Event".

My apologies for the incorrect post title, but the code accurately reflects what is currently in my workbook. It works great the first time, but not after closing and reopening...very strange.

I would have assumed it was an issue with Macro security, but I've verified that is not the case.

 

[filled2bursting]

I have encounterd a similar issue today, where the selectionChange event is completely being ignored.

I simply wish to display the cell contents of a column within the row I am parsing at the top of the sheet, while I enter information about that cell elsewhere in the row.

This worked in this application and others as recently as yesterday and for months prior.

application.enableevents= true has had no effect on this issue
Macros have been enabled
Calculations are automatic and F9 has no effect
Changing the name of the named range, has had no effect

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("Display9").Value = "O" & ActiveCell.Row
End Sub

Other subs and functions in this worksheet are still functioning as per normal, but none of them are event driven.

This is Excel 2007

Any assistance would be much appreciated

 

[filled2bursting]

Sorry All,
I believe I found the source of the problem.

There was another excel program database that I had used earlier that day.
While the program was closed it was still present in the VBA application.
Closing out all excel programs reset this, and it now works fine again.