Events

[cosmarchy]

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

[Artik]

By submitting parameters to the called procedure, you must use apostrophes characters. You can transfer only text and numbers. You can not pass the object. The text must be in quotation marks. [vba]Sub ChangeCopyTest()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl
Dim sSheet As String

'redefine Copy from menu only
Set CmdBar = Application.CommandBars(1) '"Worksheet Menu Bar"
Set CmdCtl = CmdBar.FindControl(ID:=19, Recursive:=True)

sSheet = ActiveSheet.Name

CmdCtl.OnAction = "'CustomCopy """ & sSheet & """'"

Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub

Sub CustomCopy(strSheet As String)
MsgBox "You copy data from >>" & strSheet & "<< sheet"
End Sub

Sub ResetMyChangeCopy()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

Set CmdBar = Application.CommandBars(1) '"Worksheet Menu Bar"
Set CmdCtl = CmdBar.FindControl(ID:=19, Recursive:=True)

CmdCtl.OnAction = ""

Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub[/vba] The list of parameters separated by commas, eg.[vba]CmdCtl.OnAction = "'CustomCopy ""My Sheet"", 5, 2.45'"[/vba]
Artik

[cosmarchy]

By submitting parameters to the called procedure, you must use apostrophes characters. You can transfer only text and numbers. You can not pass the object. The text must be in quotation marks. [vba]Sub ChangeCopyTest()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl
Dim sSheet As String

'redefine Copy from menu only
Set CmdBar = Application.CommandBars(1) '"Worksheet Menu Bar"
Set CmdCtl = CmdBar.FindControl(ID:=19, Recursive:=True)

sSheet = ActiveSheet.Name

CmdCtl.OnAction = "'CustomCopy """ & sSheet & """'"

Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub

Sub CustomCopy(strSheet As String)
MsgBox "You copy data from >>" & strSheet & "<< sheet"
End Sub

Sub ResetMyChangeCopy()
Dim CmdBar As CommandBar
Dim CmdCtl As CommandBarControl

Set CmdBar = Application.CommandBars(1) '"Worksheet Menu Bar"
Set CmdCtl = CmdBar.FindControl(ID:=19, Recursive:=True)

CmdCtl.OnAction = ""

Set CmdBar = Nothing
Set CmdCtl = Nothing
End Sub[/vba] The list of parameters separated by commas, eg.[vba]CmdCtl.OnAction = "'CustomCopy ""My Sheet"", 5, 2.45'"[/vba]
Artik

Thanks for your response Artik. Do you know of any way to either stop the user double clicking on the sheet tab or reassign this action to a custom function?

Thanks

[Artik]

I understand that you want to protect the names of the sheets.
I do not have the English version of Excel. Something like this: from menu Tools / Protection / Protect Workbook / check Structure.

Artik