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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

eliz

New Member
Joined
Sep 12, 2006
Messages
46
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

eliz

New Member
Joined
Sep 12, 2006
Messages
46

ADVERTISEMENT

Yeah, I thought of that, but the users will need to view and print the sheet on a regular basis.

Thx,
Eliz
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

eliz

New Member
Joined
Sep 12, 2006
Messages
46

ADVERTISEMENT

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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

eliz

New Member
Joined
Sep 12, 2006
Messages
46
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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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 ).
 

Watch MrExcel Video

Forum statistics

Threads
1,114,040
Messages
5,545,676
Members
410,697
Latest member
srishtijain0708
Top