cosmarchy
08-19-2010, 11:43 AM
Hello all,
What I want is an event that is triggered when a worksheet is copied. Looking through the events available in the IDE, I am pretty sure there isn't one so this got me thinking how to get one.
The following code 'redefines' existing buttons to call a custom function when called:
Sub ChangeCopy()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl
For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=19, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = "CustomCopy"
Next CmdBar
Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub
So clearly this got me thinking. Can I get the system to call my own function, passing the sheet name as an argument?
What, if any events are called when a user copies a sheet? Are there any buttons I can 'hijack' and redefine my own action as in the code above?
Does anyone have any suggestions?
Thanks
What I want is an event that is triggered when a worksheet is copied. Looking through the events available in the IDE, I am pretty sure there isn't one so this got me thinking how to get one.
The following code 'redefines' existing buttons to call a custom function when called:
Sub ChangeCopy()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl
For Each CmdBar In CommandBars
Set CmdCtl = CmdBar.FindControl(ID:=19, recursive:=True)
If Not CmdCtl Is Nothing Then CmdCtl.OnAction = "CustomCopy"
Next CmdBar
Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub
So clearly this got me thinking. Can I get the system to call my own function, passing the sheet name as an argument?
What, if any events are called when a user copies a sheet? Are there any buttons I can 'hijack' and redefine my own action as in the code above?
Does anyone have any suggestions?
Thanks