|
|
|
|
|
|
|
|
|
Excel
|
Prevent named ranges from being deleted
|
|
|
Ease of Use
|
Intermediate
|
|
Version tested with
|
2003
|
|
Submitted by:
|
byundt
|
|
Description:
|
If a "protected" named range is deleted, the deletion will be undone.
|
|
Discussion:
|
When a user deletes a named range, #REF! errors occur in any formulas that referenced it. It is sometimes desirable to prevent users from making this mistake, yet avoid the severe restrictions imposed on the user by worksheet protection.
Two subs are offered. The first one prevents any named range from being deleted. It assumes that all the named ranges in the workbook are initially valid (don't return #REF! errors). The second one protects just the named ranges on a specified list. Choose the sub you need for your particular situation.
Note: the suggested code will prevent the named range from being deleted in entirety, but will not prevent one or more cells within that range from being deleted.
|
|
Code:
|
instructions for use
|
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim rg As Range
Dim vName As Variant
On Error Resume Next
For Each vName In ThisWorkbook.Names
Set rg = Range(vName)
If Err <> 0 Then
Application.EnableEvents = False
Application.Undo
MsgBox "That's a protected named range, and you may not delete it"
Application.EnableEvents = True
Err.Clear
On Error GoTo 0
Exit Sub
End If
Next vName
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim rg As Range
Dim vNames As Variant, vName As Variant
On Error Resume Next
vNames = Array("Sheet4!var1", "var2", "var3")
For Each vName In vNames
Set rg = Range(vName)
If Err <> 0 Then
Application.EnableEvents = False
Application.Undo
MsgBox "That's a protected named range, and you may not delete it"
Application.EnableEvents = True
Err.Clear
On Error GoTo 0
Exit Sub
End If
Next vName
End Sub
|
|
How to use:
|
- Pick the one sub that best suits your need, then copy it. You may not use both subs in the same workbook!
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Double click ThisWorkbook in the Project Explorer pane on the left.
- Paste code into the right pane.
- If using the second sub, then put the names of named ranges you want to protect in the statement beginning vNames=Array(... on line 5, replacing Sheet4!var1, var2 & var3. Include as many as you would like. Notice that the workbook contains a named range var1 on both Sheet1 and Sheet4. The sub protects just the one on Sheet4 because the name is qualified in the array list.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
|
Test the code:
|
- Open the sample file. If using the first sub, then deleting any of the named ranges in entirety (as described below) will be reversed. If using the second sub, then only certain named ranges are protected?so deleting them will be reversed. Unprotected named ranges may be deleted.
- Try deleting var1 at Sheet1!B1:D1 using the Edit...Delete menu item. This operation is permitted by the second sub (but not the first) because that named range is not protected.
- Try deleting var2 at Sheet1!B2 using the Edit...Delete menu item. This operation is not permitted by either sub, and will be reversed along with a message box warning.
- Try clearing var2 by selecting the cell and hitting the Delete button on your keyboard. This operation is permitted, so no warning message occurs. The keyboard Delete button just deletes the contents?it does not delete the cell.
- Try deleting var4 by selecting Sheet1!B4:D4 and using the Edit...Delete menu item. This operation is permitted by the second sub, but not by the first.
- Try deleting var1 at Sheet4!B1:D1 using the Edit...Delete menu item. This operation is not permitted because Sheet4!var1 is protected. It will be reversed along with a message box warning.
|
|
Sample File:
|
ProtectNamedRanges.zip 7.54KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 200 times.
|
|
|