TWO IDENTICAL COLUMNS IN EXCEL - DIFFERENT RESULTS
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
End If
End Sub
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.
New-Journal-Entry-TEST--RBASEGOE10GA-.xl
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
End If
End Sub
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.
New-Journal-Entry-TEST--RBASEGOE10GA-.xl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On page 1 the macro is actually not working. To make it work you can change your line to
If Target.Rows.Count > 1 Then Exit Sub
If Target.Rows.Count > 1 Then Exit Sub
ASKER
This makes NO sense to me. Page 1 is working exactly as intended. I am not sure I understand the VBA code, but nonetheless, it is working as I had wanted it to. Therefore, how do I get Page 2 to NOT WORK the same way?
As I mentioned above change your line to
If Target.Value <> "" Then Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
If Target.Value <> "" Then Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
ASKER
Do I change the line in both places or just the code associated with Page 2?
As I said, the code is not working on page 1 (only when you delete the entry). So at this point it does not matter whether you change it for page 1 or not.
You do have to do it for page 2.
You do have to do it for page 2.
ASKER
IT worked. Thank you very much. Before I close the question, let me see if I understand: In the future I would use your code when the cell is comprised of only one column?
You can put it that way.
ASKER
Problems solved...Mission accomplished. Thanks guys. Now it is on to the next project.
If Target.Value <> "" Then Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")