Preventing user altering cell except to delete the value.

[rob nobel]

Is there a way to stop a user altering the value in a cell but still allow
him to delete the value there-in?
Rob

 

[J.E. McGimpsey]

One way:

Put this event macro in your worksheet code module (right-click on
the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cVALUE As Double = 100
With Target
If .Address(False, False) = "A1" Then _
If Not IsEmpty(.Value) Then .Value = cVALUE
End With
End Sub

Change cVALUE and the address of your cell to suit.

 

[rob nobel]

Hi J.E,
I don't understand what "Const cVALUE As Double = 100 means". That's both
"cVALUE" nor "Double = 100". Can you explain these so I can change the
value correctly.
Also, I may not have fully explained what I needed as I don't think (at
first glance) that this code will work for more than 1 cell.
I need it to cover many cells in 1 column, namely from G27 to G1524.
Rob

 

[Dave Peterson]

You've used Dim to declare variables.

J.E. is using Const to declare a constant--never gonna change.

cVALUE is J.E.'s name for that constant--J.E. usually capitalizes constants in
his code and I bet he's prefixing it with a lower case c to represent constant.

Because he uses some coding standards, he'll know in 7 weeks when he comes back
to modify this code that it was a constant (and never changes).

Dim rng as Range
declares an object variable that will hold a range.

Const cVALUE as Double = 100
means that cVALUE will hold a Double (a giant negative number through a giant
positive number--includes decimal points).

And in this case, J.E. wants to make it 100. So now if he ever decides that he
actually wants it to be 12342.3234, he'll make the change in one spot and forget
about it.

(Imagine if you used that 100 in 17 different places to represent the same
thing. If you decide you want 99, you'll have to find all of them! By using a
constant, he just has to find it and change it once.)

There's lots more information in VBA's help for Constant and "data;type;summary"
(include the semicolons).

 

[J.E. McGimpsey]

cVALUE is the variable name, Double is the variable Type (see
"Double data type" in VBA help)

 

[J.E. McGimpsey]

One way to keep a column of cells constant (except for deletions):

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
CheckValues
End Sub

Put this in the Worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("G27:G1524")) Is Nothing Then _
CheckValues Target
End Sub

Put this in a regular module:

Public Static Sub CheckValues(Optional rCell As Range)
Dim vCheck As Variant
If IsEmpty(vCheck) Then
vCheck = Sheets("Sheet1").Range("G27:G1524").Value
End If
If Not rCell Is Nothing Then
With rCell
If Not IsEmpty(.Value) Then
Application.EnableEvents = False
.Value = vCheck(.Row - 26, 1)
Application.EnableEvents = True
End If
End With
End If
End Sub

 

[rob nobel]

Thankyou Dave for your reply. Thankyou J.E. for your reply too and also for
that GREAT procedure. Works a treat. Yahoo!!!
One small question, though.
With the line "vCheck = Sheets("Sheet1").Range("G27:G1524").Value",
is it right that I can't change ("Sheet1") to ("Sheet4") but I have to
change Sheets("Sheet1") to just Sheet4. That is, I presume the actual name
of the sheet goes in brackets (with quotes), but if ref is to just the sheet
number it is just that, sheet4?
Rob

 

[J.E. McGimpsey]

Sheets("Sheet1") refers to the worksheet with the tab name "Sheet1",
so changing to Sheets("Sheet4") would refer to the sheet with the
tab name "Sheet4".

The other name for the sheet (i.e, Sheet4) is the code name - see

http://cpearson.com/excel/codemods.htm

for an explanation.

 

[rob nobel]

Thanks J.E., that seems to make it quite clear. (Both your reply and the
link.)
Rob