Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=542084 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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", Structu=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", Structu=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. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=542084 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stop renaming or moving sheet tabs
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).Enabl ed = 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).Enab led = 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).Enabl ed = 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).Enab led = 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. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=542084 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet tabs are hidden...option is checked | Excel Discussion (Misc queries) | |||
Why do sheet tabs keep disappearing? Box in Tools is checked. | Excel Discussion (Misc queries) | |||
Sheet Tabs | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Moving numbers from one sheet to another by date | Excel Worksheet Functions |