Events Macros - Available on a macro generated sheet?
Hello!
Is it possible to have an event macro available on a sheet that is generated by another macro without the user having to put the code on that said sheet? Like maybe using a macro that handles the event macro coding and places it on the sheet? Or making the event macro work on the said sheet without having to place it's code on it, like maybe placing it on the workbook coding instead, and only executing it when the event happens on the needed sheet?
Thanks to all in advance
Last edited by Pichingualas; 02-08-2012 at 01:09 PM.
Yes, what are you actually trying to do?
There are a number of approaches, from putting the code in the thisWorkbook module to a dedicated events class
Yes it is - see Chip Pearson's site here. Note the caution about needing trusted access to the VBProject.
Good luck.
If you use the same event code on a whole raft of sheets, why not put it in the workbook? For example,
"Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)"
Then you can just check the sheet name and exit the event if you don't need it.
That way, you don't have to worry about duplicating code.
Regards, TMS
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
Awesome TMS, that's exactly what I'm looking for, since I need to run it for three sheets and do exactly the same thing. The diference is that I need it to run for an event like double click or such. What I need is that upon the user selecting a cell that contains either a client or a product or a salesman name depending on which sheet is currently in use, the rest of the sheets info will be filtered to show only what refers to it. The whole list of users, clients and products appear on the same column on the corresponding sheet, since they are all generated by a previous macro. The only thing changing is the sheets actual name, but the coding should be the same. Is this doable?
Would a macro activated by the event
"Event SheetBeforeDoubleClick(Sh As Object, Target As Range, Cancel As Boolean)"
be able to do what I need?
Why not try it?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Why does this result in Excel crashing?
Please Login or Register to view this content.
Maybe
Please Login or Register to view this content.
With the change you suggest it causes. Once I close the error window Excel crashes again.Run-Time error '438': Object Doesn't support this property or method vba
I don't know if this might be of use for anyone else but the following code works fine:
The code I posted first worked until the message box was prompted, but after closing it Excel crashed. With the instructionPlease Login or Register to view this content.the double click on the cell is stopped. Maybe without it the code gets into a loop that results in the upper mentioned program crash, I'm not sure, but with it it works perfectly. Now I only have to paste the macro that does what I need instead of theCancel = Trueline. Thanks for all the helpMsgBox "This works"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks