PDA

View Full Version : Solved: Mystery to SOLVED: Forced-in negative value



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

acw
09-29-2005, 09:01 PM
Hi

Make sure that the events haven't been accidently turned off. Build a little macro

sub ccc()
application.enableevents = true
end sub


Run it then see if the event macros start up again.


Tony

johnske
09-30-2005, 03:06 AM
Also, make sure this line you have asApplication.EnableEvents = True End Sub is written asApplication.EnableEvents = True
End Sub '< new line here

joelle
10-01-2005, 09:33 AM
Hello Tony / Johnske,

I try each of your code and they both work!

Thanks a bunch for the post-back! Cheers.

Nee