prevent copy sheet in a workbook

[LFKim2018]

I have a workbook of several worksheets and I want to deactivate the COPY SHEET for a specific worksheet named "Form".
meaning I do not want a duplicate of this sheet to be created by pressing Ctrl key and mouse dragging of the sheet,
How do I prevent right click or copy sheet function for that particular sheet named "Form"?
Thank you

 

[PatOBrien198]

Have you tried to "Protect" the sheet, removing the ability to select cells?

 

[LFKim2018]

prevent copy sheet within a workbook

How can I prevent the RIGHT CLICK on a worksheet (to deactivate "Delete". "Rename", "move or Copy" commands?);
protect workbook - would not work as it "freezes" all worksheets.
I want to prevent a specific worksheet from being deleted, renamed, moved or copied (duplicated).
Pressing Ctrl key + mouse dragging of sheet tab to the right also copy sheet - this also has to be deactivated.

The workbook consists of several worksheets, there is a specific sheet that is the template and I don't want it to be duplicated within the workbook but still enabling users to enter data to it.

Thanks a lot

 

[starl]

Re: prevent copy sheet within a workbook

I would have to say you can't. You'd have to protect the workbook and then create a tool allowing users to manipulate the sheets that can be.

 

[Joe4]

Re: prevent copy sheet within a workbook

I have merged your two threads together.

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

 

[LFKim2018]

Re: prevent copy sheet within a workbook

so sorry for that
tnx

 

[starl]

Re: prevent copy sheet within a workbook

I have merged your two threads together.

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I thought I was going nuts! I'm reading the first post and thinking - I don't remember this at all!! Even the post I'm after doesn't seem the same..
@LFKim2018: based on the additional information - the sheet in question is a template, you could hide it and use either another sheet for them to enter values on that you then have code update the template, or design a userform for them to enter information and that is written to the hidden template.

 

[LFKim2018]

i have a workbook of several worksheets and i want to deactivate the copy sheet for a specific worksheet named "form".
Meaning i do not want a duplicate of this sheet to be created by pressing ctrl key and mouse dragging of the sheet,
how do i prevent right click or copy sheet function for that particular sheet named "form"?
Thank you

no need to reply to this thread
i have the solution already
thanks

 

[jproffer]

........care to share?? Someone else might have the same question someday

 

[LFKim2018]

........care to share?? Someone else might have the same question someday

HOPE THIS HELPS

Option Private Module


Sub Autpen()
Application.CommandBars("ply").Enabled = False
End Sub




Sub Restore()
Application.CommandBars("ply").Enabled = True
End Sub


Sub Auto_close()
Call Restore
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name Like msOldsheet & " (#)" Or Sh.Name Like msOldsheet & " (##)" Then
MsgBox "COPYING OF THIS SHEET IS NOT ALLOWED!"
Application.DisplayAlerts = False
Application.EnableEvents = False
msOldsheet = ""
Sh.Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
End If
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "SHEETNAME" Then
msOldsheet = Sh.Name
End If
End Sub