Tab Click event

[Foo_Man_Chu]

I'm trying to get an event to fire when the current tab is clicked at the bottom of the workbook. I know there's the event that fires when a sheet that is not the active sheet is clicked and thus becomes the active sheet, but I need to have my event fire when you click the tab of the currently active sheet. Any help?

 

[mikerickson]

AFAIK, there is no event that triggers when a sheet's tab is clicked.

What do you want to happen when the user clicks the tab of their current worksheet?

 

[JoeMo]

I'm trying to get an event to fire when the current tab is clicked at the bottom of the workbook. I know there's the event that fires when a sheet that is not the active sheet is clicked and thus becomes the active sheet, but I need to have my event fire when you click the tab of the currently active sheet. Any help?

That's not a covered event. What do you want to happen when you click the tab of the active sheet?

 

[Foo_Man_Chu]

It's more of a want than a need. I've got it set up now so when the template sheet becomes the active sheet it makes a copy, renames it and puts it before the template sheet. I just wanted to be able to be on the template sheet and click on the tab and have the same thing happen. Oh well.

 

[JoeMo]

You could put a simple sub in your personal workbook, put a button on the Quick Access Toolbar and click it to copy the active sheet.

Sub MakeCopy()
With ActiveSheet
       .Copy before:=ActiveSheet
       .Name = "Your Name"   'you might want to add an error trap if sheet with this name already exists
End With
End Sub

 

[mikerickson]

I'm also thinking that in order to keep a template sheet pure, to keep users from accidentally entering something on it, its common for a template sheet to be VeryHidden, with no tab.

A slight change to JoMoe's code will accommodate that set-up

With ThisWorkbook.Sheets("Template")
       .Copy before:=ThisWorkbook.Sheets("Template")
       .Name = "Your Name"   'you might want to add an error trap if sheet with this name already exists
End With