Help Debugging

[Kiba]

This is my code the part surronded by <<>> is where it errors at.
_________________________________________________________
Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Range
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
<< If SaveValue = "" Then Exit Sub>>
If Mech = True Then
Mech = False
Exit Sub

End If
If MsgBox("Are you sure you want to change that value?", vbYesNo) = vbNo
Then
Mech = True
Target.Value = SaveValue
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Value
End Sub
________________________________________________
I get an error message when I highlight several cells and press delete. Or
if the cell contains a formula.

 

[Dave Peterson]

I had trouble with the _activate event.

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Makes more sense to me.

 

[Dave Peterson]

Ps. you may want to change this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Cells(1).Value
End Sub

SaveValue will contain the value of the first cell in the target area.

 

[Tom Ogilvy]

Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
On Error goto ErrHandler
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub

End If
If MsgBox("Are you sure you want to change that value?", _
vbYesNo) = vbNo Then
Mech = True
Application.EnableEvents = False
Target(1).Value = SaveValue
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Value
End Sub

 

[Tom Ogilvy]

I agree with Dave

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target(1).Value
End Sub

 

[Kiba]

When I tried that, If I select 3 different cells with 3 differnt values and
click delete, and say no to the prompt then it replaces the values of the 3
with the first cell value.

 

[Kiba]

When I tried that, If I select 3 different cells with 3 differnt values and
click delete, and say no to the prompt then it replaces the values of the 3
with the first cell value.

Ps. you may want to change this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Cells(1).Value
End Sub

SaveValue will contain the value of the first cell in the target area.

 

[Kiba]

When I tried this code if I select multiple cells and click delete there is
no prompt of are you sure.

 

[Kiba]

I was talking to Jim and he thought maybe something like this:
Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
On Error GoTo ErrHandler
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub
End If
If MsgBox("Are you sure you want to change that value?", vbYesNo) = vbNo
Then
Mech = True
Target.Value = SaveValue
End If
ErrHandler:
Dim rng As Range

For Each rng In Target
MsgBox rng.Value
Next rng
Static Mechs As Boolean
On Error GoTo ErrHandler

If MsgBox("Are you sure you want to change that value?", vbYesNo) =
vbNo Then
Mech = True
Target.Value = SaveValue
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Value
End Sub

__________________________________________
But I'm having trouble fine tuning it. At this point I appreciate any and
all suggestions.

 

[Tom Ogilvy]

Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
On Error goto ErrHandler
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub

End If
If MsgBox("Are you sure you want to change that value?", _
vbYesNo) = vbNo Then
Mech = True
Application.EnableEvents = False
Target.Areas(1)(1).Value = SaveValue
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Areas(1)(1).Value
End Sub

to demo the concept (from the immediate window):

? Selection.Address
$D$6,$F$9,$D$11,$E$15:$E$16,$E$8
? selection.Areas(1)(1).Address
$D$6

 

[Kiba]

When I click no it still only returns the first selected cell to the sheet
the rest are then blank. If this can't be done would it be possible to
display an error message and prevent the cells from being deleted if it is
more than one cell?

 

[Dave Peterson]

Maybe an alternative approach?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Long

res = MsgBox("Are you sure you want to change that value?", vbYesNo)

On Error GoTo ErrHandler:

If res = vbNo Then
With Application
.EnableEvents = False
.Undo
End With
End If

ErrHandler:
Application.EnableEvents = True

End Sub

 

[Kiba]

Thanks!!!!!!!!!! That was just what I needed. I ended up coding the page
like this.
__________________________________________________-
Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Static Mech As Boolean
On Error GoTo ErrHandler
If SaveValue = "" Then Exit Sub
If Mech = True Then
Mech = False
Exit Sub
End If

Dim res As Long

res = MsgBox("Are you sure you want to change that value?", vbYesNo)

On Error GoTo ErrHandler:

If res = vbNo Then
With Application
.EnableEvents = False
.Undo
End With
End If

ErrHandler:
Application.EnableEvents = True

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SaveValue = Target.Areas(1)(1).Value
End Sub
______________________________________________________