I keep getting the debugger message

[Kiba]

I have a workbook with a few sheets on it. One sheet is mostly a page to
enter data. I have a macro that if the cell isn't blank and you edit or
delete it. It prompts and r u sure message. only if you select multiple
cells then it gives an error/debug message. Here is my code.
_____________________________________________________

Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
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
_________________________________________________________
On another page is a few cells that have data entered but most pull
information from the other page. Whenever I change a cell with a formula in
it I get that error/debug message and here's my code for that sheet.
___________________________________________________________

Private SaveValue

Private Sub Worksheet_Activate()
SaveValue = ActiveCell.Value
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("A1:A60")
If cell.Value = "" Then _
cell.EntireRow.Hidden = True
Next cell
End With

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'm still fairly new to this so I appreciate any help. Thanks.

 

[Jim Thomlinson]

So you have an issue when target is more than one cell. if it is a range then
the value property is ???. To fix that you can create a seperate range object
to travers all of the cell(s) that make up the target. Something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

For Each rng In Target
MsgBox rng.Value
Next rng
End Sub

 

[Kiba]

When I tried adding that in it now asked everytime I change a cell and didn't
skip the blank cells. Secondly it seemed to get stuck in a non stop loop of
asking when I highlight an entire column.

 

[Jim Thomlinson]

Sorry I have not had much chance to devote to this. Without looking too
closely I suspect that your change event causes a change to occure which in
turn fires the change event. try turning the change event off something like
this

Application.enableevents = false
'your code
Application.enableevents = true

 

[Kiba]

No problem, I appreciate your trying to help. However, I can't seem to get
this to work right. No matter how I change it I end up with the debug
message.