In VBA don't allow rename of sheet w/out protecting file.

eliz

New Member
Joined
Sep 12, 2006
Messages
46
Hello!

Is there a way to keep a user from renaming or deleting a particular sheet without protecting the workbook?

I want the user to be able to add /rename /delete other sheets, but I want to keep this one particular sheet unaffected.

Thx,
Eliz
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
In VBA don't allow rename of sheet w/out protecting file

Hi,

I want this code to protect one sheet that acts as a log for the file.
Users will still need to add new sheets, rename them, etc.., but don't want them to touch this one particular sheet. The content is protected, but the sheet itself is vulnerable to being deleted or renamed.

That's all.
 
Upvote 0
If you don't want them to touch it then make it very hidden. In the VBE use the property window to change the sheet visible property to xlVeryHidden.
 
Upvote 0
Yeah, I thought of that, but the users will need to view and print the sheet on a regular basis.

Thx,
Eliz
 
Upvote 0
Have the actual data on a hidden sheet, and the Viewing/Reporting done on a visible sheet via formulae ( hidden ). If they delete the Visible one the actual data would still be there.

You could even have a regeneration macro to recreate the visible Viewing/Reporting sheet by adding a sheet and filling in the appropriate formulae, for when the little darlings destroy the wrong sheet.
 
Upvote 0
That sounds interesting.

Would that require a lot of code?
Right now I am doing something like this...

Sub AutoProtect(bIsOpening)

'reduce screen flicker
Application.ScreenUpdating = False

'show Protection toolbar
If bIsOpening Then Application.CommandBars("Protection").Visible = True

'declare variables
Dim SH As Worksheet
Dim rng As Range
Dim sErrors As String 'for error handlers

On Error Resume Next

'loop through each sheet (Except for ChangeLog), lock formulas, protect sheets
For Each SH In Worksheets

If SH.Name <> "ChangeLog" Then
SH.Unprotect
'error handling
If Err <> 0 Then
If Err.Number <> 1004 Then
sErrors = sErrors & "The sheet named " & SH.Name & " could not be unprotected." & vbCrLf & vbCrLf
End If
End If

If SH.Name <> "changelog" Then
With SH.Cells ' used to say with sh.usedrange
.Locked = False
Set rng = Nothing
Set rng = .SpecialCells(xlCellTypeFormulas)
Err.Clear '//The Err.Clear
'this was throwing an error if there were no
'formula cells on a worksheet.
If Not rng Is Nothing Then
'rng.Font.ColorIndex = 7 'optional changes color of all formulas
If rng.Cells.Count <> 0 Then
rng.Locked = True
End If

'error handling
If Err <> 0 Then
sErrors = sErrors & "The formulas on " & SH.Name & " could not be locked." & vbCrLf & vbCrLf
End If
End If
End With
End If

' allow users certain functionality while worksheet is protected
' user will be able to format cells, adjust columns width and row height
' user will be able to insert rows/columns
' user is only able to delete row/columns that do NOT contain locked cells
' user is only able to sort/filter data range that does NOT contain
' locked cells

SH.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True

'error handling
If Err <> 0 Then
If Err.Number <> 1004 Then
sErrors = sErrors & "The sheet " & SH.Name & " could not be protected." & vbCrLf & vbCrLf
End If
End If
End If

Next SH

Application.ScreenUpdating = True

'error handling
If sErrors <> "" Then
Dim sModule As String
If bIsOpening Then sModule = "Workbook_Open" Else sModule = "Workbook.BeforeSave"
MsgBox "Some errors occurred in Workbook.BeforeSave: " & vbCrLf & vbCrLf & sErrors, vbInformation, "Errors in Workbook.BeforeSave"
End If

End Sub

Sub UpdateLog(ByVal SheetName As String, ByVal strRange As String, _
ByVal InitialVal As String, ByVal NewVal As String, Optional ByVal strAccepted As String)

'error handler
On Error GoTo err_

'reduce screen flicker
Application.ScreenUpdating = False

'declare variables
Dim rwIndex As Long
Dim sErrorMessage
Dim shtChangeLog As Worksheet

rwIndex = 1

Set shtChangeLog = ThisWorkbook.Sheets("ChangeLog")
shtChangeLog.Unprotect ("ll1nc3")

Do While shtChangeLog.Cells(rwIndex, 1) <> ""
rwIndex = rwIndex + 1
Loop

If shtChangeLog.Cells(rwIndex, 1) = "" Then
shtChangeLog.Cells(rwIndex, 1).Value = Now()
shtChangeLog.Cells(rwIndex, 2).Value = SheetName
shtChangeLog.Cells(rwIndex, 3).Value = strRange
shtChangeLog.Cells(rwIndex, 4).Value = "'" & InitialVal
shtChangeLog.Cells(rwIndex, 5).Value = "'" & NewVal
shtChangeLog.Cells(rwIndex, 6).Value = strAccepted
shtChangeLog.Cells(rwIndex, 7).Value = Environ("USERDOMAIN") & "\" & Environ("USERNAME")
End If

shtChangeLog.Protect Password:="ll1nc3"
Application.ScreenUpdating = True

Exit Sub

'error handler
err_:

sErrorMessage = "ERROR - An error occurred when updating the Change Log. " & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Please contact the ### service desk at (###) ###-####."
Application.ScreenUpdating = True
MsgBox sErrorMessage, vbCritical, "Critical Error Updating the Change Log!"

End Sub
 
Upvote 0
No I don't think it would take a lot of code. Your current code would stay the same, but the ChangeLog sheet would be hidden, and you'd have a ChangeLogReport sheet that would be visible.

I'd set that up with formulae linking to the ChangeLog so that it would automatically update when the ChangeLog changes, and I'd record the setting up of the ChangeLogReport so that I could do the set up again via macro if necessary. I'd protect the ChangeLogReport after formatting the Protection as Hidden as well as Locked for the cells there.
 
Upvote 0
In VBA don't allow rename of sheet w/out protecting file

Hi GlennUk,

Thanks for the replies.
I will give the duplicate/hidden sheet a try at some point.

Just to be sure, there is no option or property that could be set to disallow renaming/deleting of a single sheet while still allowing it to be visible and printable in an unprotected workbook?

Seems like a pretty common requirement...

Anyway, thanks again!

Eliz
 
Upvote 0
I'll see if any of the event driven options will do exactly what you want, but to be honest most of the stuff I do is on protected workbooks ( all the add/rename/delete of sheets being handled by macros ).
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top