Problem with Set stmt in Workbook_SheetSelectionChange event

[ExcelMonkey]

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

 

[OssieMac]

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

 

[ExcelMonkey]

Excellent point abou the ReEnable Sub. I acutally did not know this and was
going to make that my next post!

Thanks

EM

 

[ExcelMonkey]

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]

You won't see anything happen when it runs; it just runs and sets
EnableEvents to True.

If you want to see something to confirm it has run then use a MsgBox to
return the value of EnableEvents like the following:-

Sub ReEnableEvents()
Application.EnableEvents = True
MsgBox "Enable Events is now set to " & Application.EnableEvents
End Sub

The sub doesn't run from a Class module but it will run from ThisWorkbook or
any of the Sheet modules or insert a standard module (menu item Insert ->
Module).

Methods of running the code from within the VBA Editor:-

Click anywhere within the sub and then click the Run icon (Like a right
pointing triangle) on the Tool Bar.

Click anywhere within the sub and then press F5.

Click anywhere within the sub and then select menu item Run -> Run
Sub/Userform.

If you want to run the sub from the Class module, just click somewhere
outside of any subs and use any of the above methods to run and you should
get a dialog box with a list of subs that can be run so just select the
required sub.

 

[Excel Monkey]

So I am still having problems with this. As I am currently trying to debug
my code I am invariably coming across errors. When this happens, I cannot
seem to enable the events again.

My main event code is in a Class Module. I have put the code you have
provided in Thisworkbook. When the code fails, all events stop working. I
then go to Thisworkbook and run the following:

Sub ReEnableEvents()
Application.EnableEvents = True
MsgBox "Enable Events is now set to " & Application.EnableEvents
End Sub

However this does not enable the events. I have to restart Excel.

What am I doing wrong? I am assuming as well that I will have to eventually
put error handling in to call the sub autoamatically (i.e. On Error Goto...)

Thanks

EM

 

[OssieMac]

In Help a class module is defined as:-
Class module
A module that contains the definition of a class, including its property and
method definitions.

What code do you have in a Class Module that you need to disable Events?

If you really need to disable events in a Class module then best that you
post a new question setting out why you are disabling events in the Class
module and how to re-enable them if the code fails during testing. Perhaps
someone else can answer your question.