johnske
01-05-2005, 04:13 AM
This has me bamboozled...
Two worksheets are formatted so as to not show zero values, and to show all numbers entered without decimal places (i.e. as integers)
Entries made in four cells by the user may be integers that are from anything from zero up to 360, however the rest of the procedure requires that a non-zero entry be made in every one of the four cells.
If a user enters zero in any of those four cells the zero has to be replaced with 0.01 in order for all the following procedures to work properly. (i.e. the 0.01 is to confirm that an entry of some kind has been made in each cell).
Now, on one worksheet the following code works fine, yet on the other worksheet it does not work and the Target.Replace must be changed to Target.Previous.Replace for it to work.
If I use either one of these alternative codings on both sheets only one sheet will work properly ??? WHY ???
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
On Error Resume Next
Target.Replace _
What:="0", Replacement:="0.01", _
SearchOrder:=xlByColumns, MatchCase:=True, lookat:=xlWhole
ActiveSheet.Protect
End Sub
Two worksheets are formatted so as to not show zero values, and to show all numbers entered without decimal places (i.e. as integers)
Entries made in four cells by the user may be integers that are from anything from zero up to 360, however the rest of the procedure requires that a non-zero entry be made in every one of the four cells.
If a user enters zero in any of those four cells the zero has to be replaced with 0.01 in order for all the following procedures to work properly. (i.e. the 0.01 is to confirm that an entry of some kind has been made in each cell).
Now, on one worksheet the following code works fine, yet on the other worksheet it does not work and the Target.Replace must be changed to Target.Previous.Replace for it to work.
If I use either one of these alternative codings on both sheets only one sheet will work properly ??? WHY ???
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
On Error Resume Next
Target.Replace _
What:="0", Replacement:="0.01", _
SearchOrder:=xlByColumns, MatchCase:=True, lookat:=xlWhole
ActiveSheet.Protect
End Sub