Protect the sheet from move or copy

[Useful]

Hi mr excel!
This time i want to ask a question about how to disble the sheet from "move or copy" and also dragging with help left click of mouse to creat copy?
I find this two codes below:

I-First code; this code protects to insert new sheet with "Shift+F11" and From the menu bar "Insert worksheet"

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sh.Delete
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
MsgBox "You can't to insert new worksheet"
Application.CommandBars("Ply").FindControl(, 945).Enabled = False
End Sub

II. Second code; helps to disable the tab menu bar (on right click).
also hides menu bar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Sheet").Enabled = False
Application.CommandBars("Ply").Enabled = False
End Sub

But still i have problem -that users can creat new sheets with "Ctrl+left click on mouse then dragging and new sheet already created
Now i want to disable all of possibility to creating a new sheets
have any idea?
Thanks in advance!

 

[Jonmo1]

You can protect the workbook..

Tools - Protection - Protect Workbook - Structure.

(this is different from Sheet protection, it's Protect - Workbook)


Hope that helps.

 

[Useful]

You can protect the workbook..

Tools - Protection - Protect Workbook - Structure.

(this is different from Sheet protection, it's Protect - Workbook)


Hope that helps.

Jonmo 1 Thank for your answer it works
(i thing that why i could'nt to thing do this action before that is simple way indeed)
Thanks a lot!

 

[Jonmo1]

Glad to help !

 

[Useful]

Glad to help !

Hi Jonmo1! thanks for your advice but after using the VBA codes that posted above I have a problem when by clicking right button of mouse on the cells- menu not appearing and also when clicking on the sheet tab-Insert isn't active I've removed all this codes but still I've problem with appearing active full menu by right cliking
Thanks in advance!

 

[Jonmo1]

You probably need to reverse the changes it made...

Run this

sub test()
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Sheet").Enabled =True
Application.CommandBars("Ply").Enabled = True
end sub

 

[Useful]

You probably need to reverse the changes it made...

Run this

sub test()
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Sheet").Enabled =True
Application.CommandBars("Ply").Enabled = True
end sub

jonmo1 thanks for your answer i tried this way too but there is no positive result. "Insert" isn't active on sheet tab yet
have you any other idea?

 

[Jonmo1]

Is the workbook still protected?

Tools - Protection - UNprotect Workbook

 

[Useful]

Is the workbook still protected?

Tools - Protection - UNprotect Workbook

No not protected insert is inactive at all of excel workbooks

 

[Useful]

( Finally the thread is closed) The problem is solved with this code

Application.Commandbars("Ply").Reset</pre>