Is there some VBA that can stop you renaming or moving worksheet tabs?
Is there some VBA that can stop you renaming or moving worksheet tabs?
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.)
sparx wrote:
>
> Is there some VBA that can stop you renaming or moving worksheet tabs?
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=542084
--
Dave Peterson
Have tried and I can still insert, copy and move worksheets!.
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 not workbook and it was the wrong option - I thought I knew what I was on about again!! - You helped not so long back regards some other vba codes - they also worked for me so again thanks.
I don't think you protected the workbook correctly.
I'd try it once more.
sparx wrote:
>
> Have tried and I can still insert, copy and move worksheets!.
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=542084
--
Dave Peterson
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" wrote:
> I don't think you protected the workbook correctly.
>
> I'd try it once more.
>
>
>
> sparx wrote:
> >
> > Have tried and I can still insert, copy and move worksheets!.
> >
> > --
> > sparx
> > ------------------------------------------------------------------------
> > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> > View this thread: http://www.excelforum.com/showthread...hreadid=542084
>
> --
>
> Dave Peterson
>
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.
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" wrote:
> I don't think you protected the workbook correctly.
>
> I'd try it once more.
>
>
>
> sparx wrote:
> >
> > Have tried and I can still insert, copy and move worksheets!.
> >
> > --
> > sparx
> > ------------------------------------------------------------------------
> > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> > View this thread: http://www.excelforum.com/showthread...hreadid=542084
>
> --
>
> Dave Peterson
>
Users can still just drag and drop, though.
Bob wrote:
>
> 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" wrote:
>
> > I don't think you protected the workbook correctly.
> >
> > I'd try it once more.
> >
> >
> >
> > sparx wrote:
> > >
> > > Have tried and I can still insert, copy and move worksheets!.
> > >
> > > --
> > > sparx
> > > ------------------------------------------------------------------------
> > > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> > > View this thread: http://www.excelforum.com/showthread...hreadid=542084
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks