Subtle Cell Protection
 

Howdy All,

I'm trying to find a way to either keep a cell from being modified, or have
it automatically set back to zero, if it is hidden, and in the 'center' of a
value drag(fill).

I've tried locking it, but that generates an error message.
I've tried using a validation object, but that generates a message.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.

Subtle Cell Protection
 

Hi Lance,

Does this help?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5") = 0
End Sub

It goes in the sheet module for the sheet in question and makes cell
A5 equal 0 everytime the cell selection changes (which can cause
issues, like wiping the change history).

Cheers,
JF

On 3 Sep, 16:42, Lance Roberts <LJRoberts(at)gvea.com wrote:
Howdy All,

I'm trying to find a way to either keep a cell from being modified, or have
it automatically set back to zero, if it is hidden, and in the 'center' of a
value drag(fill).

I've tried locking it, but that generates an error message.
I've tried using a validation object, but that generates a message.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.

Subtle Cell Protection
 

Howdy Joshua,

That's what I'm looking at as my last resort (though using the
Change Event), but since there are a lot of entries (and selections)
on the page, it would be a lot of extra processing.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.


" wrote:

Hi Lance,

Does this help?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5") = 0
End Sub

It goes in the sheet module for the sheet in question and makes cell
A5 equal 0 everytime the cell selection changes (which can cause
issues, like wiping the change history).

Cheers,
JF

On 3 Sep, 16:42, Lance Roberts <LJRoberts(at)gvea.com wrote:
Howdy All,

I'm trying to find a way to either keep a cell from being modified, or have
it automatically set back to zero, if it is hidden, and in the 'center' of a
value drag(fill).

I've tried locking it, but that generates an error message.
I've tried using a validation object, but that generates a message.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.

Subtle Cell Protection
 

Here is an idea to try. For this to work, you have to assign a Conditional
Format formula to each cell you want to subtly protect, but there is no need
to actually give the cells a format (the formula is all we need). Since this
is a Conditional Format, you can select all the cells you want to be in the
subtly protected group and give them all the Conditional Format formula all
at once. Later on, you can add a new cell or cells to the subtly protected
group by simply giving the cell or cells the same Conditional Format formula
(no need to touch the other already conditionally formatted cells). Also, to
remove a cell or cells from the subtly protected group, just clear that cell
or those cells' conditional format. Okay, the formula I am suggesting is
this...

="PROTECTED"

(although any constant value will do) and I am suggesting you always assign
it by selecting "Formula Is" in the Conditional Format dialog box (there
seemed to be some instances when using "Cell Value Is" and "equal to" when
it didn't work right). The code shown below is set it automatically "do its
thing" when the workbook is first opened; however, if you add or remove any
cells from the subtly protected grouping, then you **must** run the
InitializeSubtleProtect macro in order to update everything. By the way, if
you need to change any values in the subtly protected group of cells, just
remove its Conditional Format, make your change to the cell value, reapply
the Conditional Format and run the InitializeSubtleProtect macro. Okay, with
the usage instructions out of the way, here is the code and with they should
be located...

Add a Module to the project and copy/paste this into the Module's code
window...

'*************** BEGIN MODULE CODE ***************
Public ProtectedCells As New Collection
Public Const AlwaysProtectedCell As String = "A5"

Public Sub InitializeSubtleProtect()
Dim X As Long
Dim C As Range
For X = 1 To ProtectedCells.Count
ProtectedCells.Remove 1
Next
For Each C In Range(AlwaysProtectedCell).SpecialCells( _
xlCellTypeSameFormatConditions)
ProtectedCells.Add C.Value, C.Address
Next
End Sub
'*************** END MODULE CODE ***************

Copy/Paste this into the ThisWorkbook code window...

'*************** BEGIN ThisWorkbook CODE ***************
Private Sub Workbook_Open()
InitializeSubtleProtect
End Sub
'*************** END ThisWorkbook CODE ***************

Finally, copy/paste this code into **every** worksheet code window (I used
Sheet1 for example purposes) where you want to have subtly protected
cells...

'*************** BEGIN Sheet1 CODE ***************
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
If Range(AlwaysProtectedCell).FormatConditions.Count = 0 Then
Range(AlwaysProtectedCell).FormatConditions.Add _
xlExpression, , "=""PROTECTED"""
End If
For Each C In Target
If Not Intersect(C, Range(AlwaysProtectedCell).SpecialCells( _
xlCellTypeSameFormatConditions)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
C.Value = ProtectedCells(C.Address)
End If
Next
Whoops:
Application.EnableEvents = True
End Sub
'*************** BEGIN Sheet1 CODE ***************

That's it. Save the workbook; then either close and reopen or run the
InitializeSubtleProtect macro to set everything up. After that, all cells
with a Conditional Format formula of ="PROTECTED" will not be able to be
permanently changed by either typing into, pasting over or series filling
across.

Let me know if this works out for you or not.

--
Rick (MVP - Excel)


"Lance Roberts" <LJRoberts(at)gvea.com wrote in message
...
Howdy Joshua,

That's what I'm looking at as my last resort (though using the
Change Event), but since there are a lot of entries (and selections)
on the page, it would be a lot of extra processing.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.


" wrote:

Hi Lance,

Does this help?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5") = 0
End Sub

It goes in the sheet module for the sheet in question and makes cell
A5 equal 0 everytime the cell selection changes (which can cause
issues, like wiping the change history).

Cheers,
JF

On 3 Sep, 16:42, Lance Roberts <LJRoberts(at)gvea.com wrote:
Howdy All,

I'm trying to find a way to either keep a cell from being modified, or
have
it automatically set back to zero, if it is hidden, and in the 'center'
of a
value drag(fill).

I've tried locking it, but that generates an error message.
I've tried using a validation object, but that generates a message.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.

Subtle Cell Protection
 

Rick,

That's great code. I'm copying to a text file for future use.
I tried to adapt it to my particular situation and learned some interesting
things,
like in Excel 2003, the bug still exists so that you can't call SpecialCells
when
the sheet is protected (noted as a problem by Microsoft in 97,98 and 2000, but
still never fixed).

I ended up finding a specific solution for the Change Event as follows:

-------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range

If Not Application.Intersect(Target, Range("TSRdst1")) Is Nothing Then
For Each R In Intersect(Target, Range("TSRDSTarea")).Cells
If R.Columns(1).Hidden And Not R.HasFormula Then
R = 0
End If
Next R
End If

End Sub

--------------------------------------------------------------

Obviously, it can be tighter since I only want a zero value in the cells of
interest.

Thanks,
--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.


"Rick Rothstein" wrote:

Here is an idea to try. For this to work, you have to assign a Conditional
Format formula to each cell you want to subtly protect, but there is no need
to actually give the cells a format (the formula is all we need). Since this
is a Conditional Format, you can select all the cells you want to be in the
subtly protected group and give them all the Conditional Format formula all
at once. Later on, you can add a new cell or cells to the subtly protected
group by simply giving the cell or cells the same Conditional Format formula
(no need to touch the other already conditionally formatted cells). Also, to
remove a cell or cells from the subtly protected group, just clear that cell
or those cells' conditional format. Okay, the formula I am suggesting is
this...

="PROTECTED"

(although any constant value will do) and I am suggesting you always assign
it by selecting "Formula Is" in the Conditional Format dialog box (there
seemed to be some instances when using "Cell Value Is" and "equal to" when
it didn't work right). The code shown below is set it automatically "do its
thing" when the workbook is first opened; however, if you add or remove any
cells from the subtly protected grouping, then you **must** run the
InitializeSubtleProtect macro in order to update everything. By the way, if
you need to change any values in the subtly protected group of cells, just
remove its Conditional Format, make your change to the cell value, reapply
the Conditional Format and run the InitializeSubtleProtect macro. Okay, with
the usage instructions out of the way, here is the code and with they should
be located...

Add a Module to the project and copy/paste this into the Module's code
window...

'*************** BEGIN MODULE CODE ***************
Public ProtectedCells As New Collection
Public Const AlwaysProtectedCell As String = "A5"

Public Sub InitializeSubtleProtect()
Dim X As Long
Dim C As Range
For X = 1 To ProtectedCells.Count
ProtectedCells.Remove 1
Next
For Each C In Range(AlwaysProtectedCell).SpecialCells( _
xlCellTypeSameFormatConditions)
ProtectedCells.Add C.Value, C.Address
Next
End Sub
'*************** END MODULE CODE ***************

Copy/Paste this into the ThisWorkbook code window...

'*************** BEGIN ThisWorkbook CODE ***************
Private Sub Workbook_Open()
InitializeSubtleProtect
End Sub
'*************** END ThisWorkbook CODE ***************

Finally, copy/paste this code into **every** worksheet code window (I used
Sheet1 for example purposes) where you want to have subtly protected
cells...

'*************** BEGIN Sheet1 CODE ***************
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
If Range(AlwaysProtectedCell).FormatConditions.Count = 0 Then
Range(AlwaysProtectedCell).FormatConditions.Add _
xlExpression, , "=""PROTECTED"""
End If
For Each C In Target
If Not Intersect(C, Range(AlwaysProtectedCell).SpecialCells( _
xlCellTypeSameFormatConditions)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
C.Value = ProtectedCells(C.Address)
End If
Next
Whoops:
Application.EnableEvents = True
End Sub
'*************** BEGIN Sheet1 CODE ***************

That's it. Save the workbook; then either close and reopen or run the
InitializeSubtleProtect macro to set everything up. After that, all cells
with a Conditional Format formula of ="PROTECTED" will not be able to be
permanently changed by either typing into, pasting over or series filling
across.

Let me know if this works out for you or not.

--
Rick (MVP - Excel)


"Lance Roberts" <LJRoberts(at)gvea.com wrote in message
...
Howdy Joshua,

That's what I'm looking at as my last resort (though using the
Change Event), but since there are a lot of entries (and selections)
on the page, it would be a lot of extra processing.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.


" wrote:

Hi Lance,

Does this help?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5") = 0
End Sub

It goes in the sheet module for the sheet in question and makes cell
A5 equal 0 everytime the cell selection changes (which can cause
issues, like wiping the change history).

Cheers,
JF

On 3 Sep, 16:42, Lance Roberts <LJRoberts(at)gvea.com wrote:
Howdy All,

I'm trying to find a way to either keep a cell from being modified, or
have
it automatically set back to zero, if it is hidden, and in the 'center'
of a
value drag(fill).

I've tried locking it, but that generates an error message.
I've tried using a validation object, but that generates a message.

--
Lance Roberts
Control Systems Engineer
Golden Valley Electric Assoc.