Stop renaming or moving sheet tabs

D

Dave Peterson

How about just protecting the workbook?

Tools|Protection|protect workbook|check structure.

(You won't be able to add more sheets to this protected workbook, either.)
 
S

sparx

Hello Dave, Thank you for your reply - I should have listened to you
tried what you said and it did work - I tried protecting worksheets no
workbook and it was the wrong option - I thought I knew what I was o
about again!! - You helped not so long back regards some other vb
codes - they also worked for me so again thanks
 
B

Bob

I have a similar issue.
I can't protect the workbook because I have macros that hide and unhide
sheets.
I get a runtime 1004 error:
'unable to set the visible property of the worksheet class'

Dave Peterson said:
I don't think you protected the workbook correctly.

I'd try it once more.
Click to expand...
 
B

Bob

Found this and it works well enough
Application.CommandBars("Ply").Controls("move or copy...").Enabled = False
Application.CommandBars("Ply").Controls("move or copy...").Visible = False
Application.CommandBars("Edit").Controls("Move or Copy Sheet...").Enabled =
False
Application.CommandBars("Edit").Controls("Move or Copy Sheet...").Visible =
False

Dave Peterson said:
I don't think you protected the workbook correctly.

I'd try it once more.
Click to expand...
 
S

sparx

Hello Bob, after several attempts of placing where to set the unprotect
password, ive managed to get my file working fine - I have vba with hide
and unhidesheets at workbook open and close - this is 2 of the codes I
use - kindly provided by others on this forum but have added some items
- you must note, I am nowhere near a vba expert so here goes.

Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="Something Usefull"
ThisWorkbook.Sheets("Information").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.name <> "Information" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True
Application.EnableEvents = False
ActiveWorkbook.Protect Password:="Something Usefull",
Structure:=True, Windows:=False
ThisWorkbook.Save
Application.EnableEvents = True

End Sub

Private Sub UnhideSheets()
ActiveWorkbook.Unprotect Password:="Something Usefull"
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Information").Visible = xlSheetVeryHidden
ActiveWorkbook.Protect Password:="Something Usefull",
Structure:=True, Windows:=False
Application.ScreenUpdating = True

End Sub

The above code is within the "ThisWorkbook" page.

Its obviously doing something right - because I did get the message you
discussed but now dont and everything works perfectly - my saving, save
as and close - and when re-opening my file, if disable macro's is
selected, the workbook is still protected.

Hope you find some of this helpful.
 
D

Dave Peterson

Users can still just drag and drop, though.
Found this and it works well enough
Application.CommandBars("Ply").Controls("move or copy...").Enabled = False
Application.CommandBars("Ply").Controls("move or copy...").Visible = False
Application.CommandBars("Edit").Controls("Move or Copy Sheet...").Enabled =
False
Application.CommandBars("Edit").Controls("Move or Copy Sheet...").Visible =
False
Click to expand...
 
S

sparx

Just to make sure you can not cut:

Sub DisableCut()
On Error Resume Next
With Application
'disables shortcut keys
..OnKey "^x", ""
'Disables Cut
..CommandBars("Standard").FindControl(ID:=21).Enabled = False
..CommandBars("Edit").FindControl(ID:=21).Enabled = False
..CommandBars("Cell").FindControl(ID:=21).Enabled = False
..CommandBars("Column").FindControl(ID:=21).Enabled = False
..CommandBars("Row").FindControl(ID:=21).Enabled = False
..CommandBars("Button").FindControl(ID:=21).Enabled = False
..CommandBars("XLM Cell").FindControl(ID:=21).Enabled = False
..CommandBars("Formula Bar").FindControl(ID:=21).Enabled = False
..CommandBars("Query").FindControl(ID:=21).Enabled = False
..CommandBars("Query Layout").FindControl(ID:=21).Enabled = False
..CommandBars("Object/Plot").FindControl(ID:=21).Enabled = False
..CommandBars("Phonetic Information").FindControl(ID:=21).Enabled =
False
..CommandBars("Shapes").FindControl(ID:=21).Enabled = False
..CommandBars("Inactive Chart").FindControl(ID:=21).Enabled = False
..CommandBars("ActiveX Control").FindControl(ID:=21).Enabled =
False
..CommandBars("OLE Object").FindControl(ID:=21).Enabled = False
..CommandBars("Excel Control").FindControl(ID:=21).Enabled = False
..CommandBars("WordArt Contex Menu").FindControl(ID:=21).Enabled =
False
..CommandBars("Curve").FindControl(ID:=21).Enabled = False
..CommandBars("Pictures Contex Menu").FindControl(ID:=21).Enabled =
False
..CommandBars("Rotate Mode").FindControl(ID:=21).Enabled = False
..CommandBars("Connector").FindControl(ID:=21).Enabled = False
..CommandBars("Script Anchor Popup").FindControl(ID:=21).Enabled =
False
..CommandBars("Worksheet Menu Bar").FindControl(ID:=21).Enabled =
False
End With
End Sub

Sub EnableCut()
On Error Resume Next
With Application
'enables shortcut keys
..OnKey "^x"
'Enables Cut
..CommandBars("Standard").FindControl(ID:=21).Enabled = True
..CommandBars("Edit").FindControl(ID:=21).Enabled = True
..CommandBars("Cell").FindControl(ID:=21).Enabled = True
..CommandBars("Column").FindControl(ID:=21).Enabled = True
..CommandBars("Row").FindControl(ID:=21).Enabled = True
..CommandBars("Button").FindControl(ID:=21).Enabled = True
..CommandBars("XLM Cell").FindControl(ID:=21).Enabled = True
..CommandBars("Formula Bar").FindControl(ID:=21).Enabled = True
..CommandBars("Query").FindControl(ID:=21).Enabled = True
..CommandBars("Query Layout").FindControl(ID:=21).Enabled = True
..CommandBars("Object/Plot").FindControl(ID:=21).Enabled = True
..CommandBars("Phonetic Information").FindControl(ID:=21).Enabled =
True
..CommandBars("Shapes").FindControl(ID:=21).Enabled = True
..CommandBars("Inactive Chart").FindControl(ID:=21).Enabled = True
..CommandBars("ActiveX Control").FindControl(ID:=21).Enabled = True
..CommandBars("OLE Object").FindControl(ID:=21).Enabled = True
..CommandBars("Excel Control").FindControl(ID:=21).Enabled = True
..CommandBars("WordArt Contex Menu").FindControl(ID:=21).Enabled =
True
..CommandBars("Curve").FindControl(ID:=21).Enabled = True
..CommandBars("Pictures Contex Menu").FindControl(ID:=21).Enabled =
True
..CommandBars("Rotate Mode").FindControl(ID:=21).Enabled = True
..CommandBars("Connector").FindControl(ID:=21).Enabled = True
..CommandBars("Script Anchor Popup").FindControl(ID:=21).Enabled =
True
..CommandBars("Worksheet Menu Bar").FindControl(ID:=21).Enabled =
True
End With
End Sub

Sub Find_Disable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=21) '21 = cut
For Each ctl In myControls
ctl.Enabled = False
Next ctl
End Sub

Sub Find_Enable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=21) '21 = cut
For Each ctl In myControls
ctl.Enabled = True
Next ctl
End Sub

and change the above code from 21 ( cut ) to:

drag-and-drop
"Allow cell drag and drop" box, 112
button controls, 52
creating toolbar shortcuts with, 23
manipulating cell contents with, 123-125
sheets and, 97

I use the top code when my file starts and it stops the user being able
to cut by either mouse or keyboard shortcut.

Also:

Sub DisableMoveorCopy()
On Error Resume Next
With Application
'Disables Move or Copy
..CommandBars("Edit").FindControl(ID:=848).Enabled = False
..CommandBars("Ply").FindControl(ID:=848).Enabled = False
..CommandBars("Ply").FindControl(ID:=847).Enabled = False
..CommandBars("Ply").FindControl(ID:=889).Enabled = False
..CommandBars("Ply").FindControl(ID:=945).Enabled = False
..CommandBars("Ply").FindControl(ID:=1561).Enabled = False
End With
End Sub

Sub EnableMoveorCopy()
On Error Resume Next
With Application
'Enables Move or Copy
..CommandBars("Edit").FindControl(ID:=848).Enabled = True
..CommandBars("Ply").FindControl(ID:=848).Enabled = True
..CommandBars("Ply").FindControl(ID:=847).Enabled = True
..CommandBars("Ply").FindControl(ID:=889).Enabled = True
..CommandBars("Ply").FindControl(ID:=945).Enabled = True
..CommandBars("Ply").FindControl(ID:=1561).Enabled = True
End With
End Sub

This lot stops the move, copy, etc.
 
Top