Quick question. I have moved some of this code into my personal.xls
project. I am now using a class module. Where do I put the RenableEvents
sub that was in my Thisworkbook module in the first example. I tried putting
it in the class module but I cannot run it from there. If I put in in the
Thisworbook module of my Personal.xls project, nothing happens when I run it.

'Orignal Code
Sub ReEnableEvents()
Application.EnableEvents = True
End Sub


"OssieMac" wrote:

I tested your code and tried to find a reason. The eternal loop only occurs
when there is a formula in the range. However, it does not call a
Worksheet_Calculate() event so I am at a loss as to why it occurs.

The work around is to use
Application.EnableEvents = False at the start of the sub and
Application.EnableEvents = True at the end of the sub.

It might be superfluous to tell you this but you should keep a copy of the
following code in your module and run it if for any reason the code fails
during your testing before it turns events back on otherwise they remain off
until Excel is restarted. You can run the code from within VBA.

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"ExcelMonkey" wrote:

I am trying to use a Set stmt in a Workbook_SheetSelectionChange event. When
I get to the second Set stmt, the Workbook_SheetSelectionChange event
triggers again and continues to do so in a loop.

Why is this? Why does it only fail on the second one?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

vOldUsedRange = Worksheets(Target.Parent.Name).UsedRange.Address
Set rngTest = Range(vOldUsedRange)
Set myNumConst = rngTest.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
End Sub