Stop renaming or moving sheet 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!.

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

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.