|
|
|
|
|
|
|
|
|
Excel
|
Prohibit specific cell changes
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2002
|
|
Submitted by:
|
Zack Barresse
|
|
Description:
|
This allows you to specify a range of cell(s) in which the user will not be able to change the content(s) or formula(s).
|
|
Discussion:
|
Sometimes formulas are a valuable commodity on a spreadsheet, as well as text you may not want changed. If multiple users are accessing your file, or you want to email it, but do not want them to accidentally erase or change your formulas/data, this type of routine will prohibit such an action. There is an option of worksheet protection and locked cells although sometimes you do not want the entire sheet protected, and it is often difficult to know which cells you have locked and which cells you have not.
|
|
Code:
|
instructions for use
|
Option Explicit
Public myForm As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:D1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Formula = myForm
MsgBox "Please do not change that cell." & vbCrLf & vbCrLf & _
"Thank you," & vbCrLf & "Management", vbInformation, "Nope!"
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myForm = Target.Formula
End Sub
|
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Double click the worksheet on the left you wish to add the code to.
- Paste code into the right pane.
- Change the Range to desired cell(s).
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
|
Test the code:
|
- Prior to installing code, type in some erroneous data (or use an existing worksheet with data).
- Install the code and set your Range.
- In Excel, attempt to change a cell in the Range you set in the code.
- A message will pop up and the value or formula will revert to it's previous status.
|
|
Sample File:
|
limitCellAccess.Ex.zip 7.2KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 284 times.
|
|
|