Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Set stmt in Workbook_SheetSelectionChange event
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_SheetSelectionChange problem in my add in | Excel Programming | |||
Workbook_SheetSelectionChange in an Addin | Excel Programming | |||
Workbook_SheetSelectionChange in going infinite | Excel Programming | |||
Improving Workbook_SheetSelectionChange for enhanced Autofiltering | Excel Programming | |||
Workbook_SheetSelectionChange | Excel Programming |