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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Set stmt in Workbook_SheetSelectionChange event
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. -- Regards, OssieMac "ExcelMonkey" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Set stmt in Workbook_SheetSelectionChange event
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" wrote: 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. -- Regards, OssieMac "ExcelMonkey" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Set stmt in Workbook_SheetSelectionChange event
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. -- Regards, OssieMac "Excel Monkey" wrote: 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" wrote: 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. -- Regards, OssieMac "ExcelMonkey" wrote: 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 |
Reply |
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 |