Solved: ??? Why ???

[johnske]

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

[Jacob Hilderbrand]

I just made a workbook with your code in two worksheets and it works fine. Does the attached file work for you?

[Jacob Hilderbrand]

You can also try this alternative.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 0 And IsNumeric(Target.Value) = True Then
Target.Value = ".01"
End If
End Sub

[johnske]

Hi Jacob,

Thanx, yeah, your attached example worked as it should on both sheets - all I can think of is that I may have a corrupted sheet in the book???

Your 2nd option worked on one sheet but gave a "Type mismatch" error if I cleared it (an "on error" statement would fix that) - but on the other sheet it gave 0.01s when just pressing enter to go across to the next cell(s)

Will just have to try replacing one of the sheets I suppose....:bawl

[johnske]

Hi Jacob,

Mystery solved!! :) - there was no corrupted sheet - the full picture is this...

There is a 3rd sheet with a worksheet calculate event linked to one sheet & the 3rd sheet returns a value to a single (linked) cell on the 'problem' sheet.

Now, although the linked cell is neither 'Active' nor 'Selected' it IS interpreted by Excel as being the 'Target', hence Target.Previous must now be used on this particular sheet (really quite logical when you think about it). :blush

My boo-boo, thanx very much for your time and input just the same...

John :hi:

[Jacob Hilderbrand]

You're welcome. :)

Glad you got it figured out.