Solved: Need to use the sheets change event to undo changes made to Cell A1

[frank_m]

(1) I have Range("A1:Z9") merged. When selected, Excel treats it as being Cell A1.
- It contains a value. Lets say that value is "My Value in Cell A1".

(2) I also have the sheets selection change event turning sheet protection off if that merged cell is selected.

(3) The sheet is (protected again) when any other cell is selected.
That command is also handled in the selection change event.

(4) --- What is the code that I can use in the Sheets change event that will undo any change that is made in that merged cell(?)- The undo routine would be triggered when the selection is moved out of the merged cell to any other cell.

[GTO]

Hi Frank,

I personally avoid merged cells if possible. That said, here's a try:
[VBA]
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(0, 0) = "A1:Z9" Then
Me.Unprotect
ElseIf Not Me.ProtectContents Then
Me.Protect , , , , True
Me.Cells(1).Value = "My Value in Cell A1"
End If
End Sub
[/VBA]
Hope that helps,

Mark

[frank_m]

Hi Mark,

It looks to me like that code sets the cells value only if that cell is selected, rather than undo-ing a change, Edit: no matter what cell is selected, as I need.

I believe we need to use the change event.

Thanks buddy

[frank_m]

Edit: This modified version of your code does what I need.
In fact I know enough I should have thought of this approach myself.
-- Thanks Mark.
[vba]Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Cells(1).Value <> "My Value in Cell A1" Then
Me.Unprotect
Me.Cells(1).Value = "My Value in Cell A1"
ElseIf Not Me.ProtectContents Then
Me.Protect , , , , True

End If
End Sub [/vba]