LinkBack URL
About LinkBacks
There are two attached files for this post.
1. MS Word .docx file describing the problem and the solution
2. MS Excel .xlsm workbook to demo the solution
Any suggestions leading to improvement gratefully accepted.
There are two attached files for this post.
1. MS Word .docx file describing the problem and the solution
2. MS Excel .xlsm workbook to demo the solution
Any suggestions leading to improvement gratefully accepted.
Probably being stupid here, but why don't you just use the Workbook_SheetSelectionChange Event handler?
https://msdn.microsoft.com/en-us/lib.../ff837368.aspx
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Question: do you really want a Selection Change Event handler, or do you need a Change Event handler? Or both?
Thanks for the replies guys. The workbook contains 30+ worksheets which are identical in function, but unique by an ID number. SelectionChange routine is used to log changes and handle a few control functions. When a new function is added or an old function is enhanced, the Selectionchange in each worksheet has to be changed manually. In addition, as more these worksheets are added to the workbook, the modification task gets longer. I tried to document this in the word document attached to the initial post to this thread and I attached a workbook example of the proposed solution (using Sheet1 as the example and Sheet2 for demonstration purposes). If there were more copies of Sheet1 (i.e., Sheet3, Sheet4,...Sheetnn), it would be come clearer that changes would only need to be made in the external Macro to support all the related worksheets. The worksheets are not changed only the external Macro (called by the Stub in the worksheet) need be modified.
If there is easier way to do this, I'd appreciate the info.
Thanks again for the support.
Guess I wasn't clear enough. In a worksheet class module, you have sheet related events, for example:
and:
Please Login or Register to view this content.
The Worksheet_SelectionChange will fire when you select a cell or range on the worksheet; the Worksheet_Change will fire when you change a cell or range on the worksheet. Given that you appear to be trying to create a change log, I would have thought that you would require the latter. That said, if you need to record the before status, you could pick that up when the cell is selected.Please Login or Register to view this content.
These events relate to the specific worksheet in which they are present.
You can, however, have equivalent event handlers in the workbook class module. For example:
Please Login or Register to view this content.The Workbook_SheetSelectionChange will fire when you select a cell or range on any worksheet; the Workbook_SheetChange will fire when you change a cell or range on any worksheet.Please Login or Register to view this content.
These events relate to every worksheet in the workbook.
Note the subtle difference is that the workbook event handlers have sh as a parameter; this is the worksheet being acted upon.
My point is that you can use a workbook event handler and, within that, refer to the sheet being acted upon. You do not need to have code behind every worksheet. This is especially true if you want to take the same action regardless of the active sheet. Much cleaner from a maintenance and support perspective.
You would use sh.Range("..."), for example, to refer to a cell or range on worksheet "sh" rather than ActiveSheet.Range("...")
Note that it is normal practice to define Public variables in a Standard Module. Then you would not need to prefix references with "ThisWorkbook", for example, ThisWorkbook.EventLogWs and ThisWorkbook.pass ... which, I must admit, I found quite confusing.
As you have no doubt concluded by now, my experience with Excel is not extensive and this lack of Excel specific experience is reflected in the implementation decisions that I've made. Thank you for your time and your patience in explaining stuff.
I started out trying to utilize the workbook level event, but there were two reasons that led me to the decision that I made with regard to the level of SelectionChange implementation. The first had to do with the fact that there was another worksheet "type" for which SelectionChange was needed and I felt that the supporting the two functions within the same stream of logic was a level of complexity with which I chose not to deal. The second was that the granular approach was logically cleaner and made expansion of the application more flexibly possible.
The thing about the Public variables also logical assessment and forced the coding discipline to avoid ambiguity (since I do not yet do EXPLICIT variable definition and reference).
And as for the "cleaner from a maintenance and support perspective", that is the goal of the tip that I submitted (which combines the efficiency of the workbook level within the environment of the worksheet level function. The stub does not change and the commonality of code is maintained in the macro. It does take two pieces of related code to implement, but does so relatively efficiently. Our real application is a bit more complicated than the Demo .xlsm; the demo is intended to only illustrate the basic idea.
Thanks again for the help and explanations; they are very helpful.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks