joelle
09-29-2005, 10:52 AM
Dear Experts,
Thanks to the kind help of the experts VBA, I have these 2 options of VBA below and each option "worked" fine for my purpose -- meaning the codes turn any number input to any cell in the named range to an negative ABS value.
However, when I retried each of the options below this morning, using the same ws, nothing happens !!! (very PANICKING). Pls note I have one ws for each VBA.
Pls someone be so sympathic and point out to me where the gremlins came from. SOS!
Set#1 -- using defined name range "credit"
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rngCell As Range
If Not (Application.Intersect(Target, Range("credit")) Is Nothing) Then
For Each rngCell In Range("credit")
If IsNumeric(rngCell) And (rngCell.Value <> 0) Then rngCell.Value = -1 * Abs(rngCell.Value)
Next rngCell
End If
End Sub
Set#2 -- simply using cell address:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric([A1]) Then [A1] = -Abs([A1])
If IsNumeric([A2]) Then [A2] = -Abs([A2])
Application.EnableEvents = True End Sub
Thanks to the kind help of the experts VBA, I have these 2 options of VBA below and each option "worked" fine for my purpose -- meaning the codes turn any number input to any cell in the named range to an negative ABS value.
However, when I retried each of the options below this morning, using the same ws, nothing happens !!! (very PANICKING). Pls note I have one ws for each VBA.
Pls someone be so sympathic and point out to me where the gremlins came from. SOS!
Set#1 -- using defined name range "credit"
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rngCell As Range
If Not (Application.Intersect(Target, Range("credit")) Is Nothing) Then
For Each rngCell In Range("credit")
If IsNumeric(rngCell) And (rngCell.Value <> 0) Then rngCell.Value = -1 * Abs(rngCell.Value)
Next rngCell
End If
End Sub
Set#2 -- simply using cell address:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric([A1]) Then [A1] = -Abs([A1])
If IsNumeric([A2]) Then [A2] = -Abs([A2])
Application.EnableEvents = True End Sub