A couple of nights ago, with the help of several of you, I built a journal entry sheet with some very helpful formatting. There is a column in each worksheet with identical parameters. Same special number format, same spacing and both controlled by the following VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
'modify the range A1:A20 to match your input cells
If Not Intersect(Target, [J8:J46]) Is Nothing Then
Application.EnableEvents = False
Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
Application.EnableEvents = True
In the attached workbook, Tab JE Page 1, the column is J8..J46. You enter say 12 and Enter and the cell is formatted 1200-00-00. If you hit Delete, the cell clears to blank!
In the attached workbook, Tab JE Page 2, the column is H8..J48. You enter say 12 and Enter and the cell is formatted 1200-00-00. If you hit Delete, THE CELL CLEARS TO 0000-00-00!
Why one cell clears to blank (as it should) and another clears to 0000-00-00 (as it should not) is beyond me. Everything about these two cells are identical, except the outcome.
There must be something different that just I am not seeing.