Worksheet_Selection Change problem

[max57]

Hi folks,

I wrote this code which allows me to:
-- enter the DATE, then have the DESIGNATION column fill in automatically.
-- enter the CHARGE amount and have the CREDITS column fill in $0.00, giving me the option to
overwrite it if there is an actual credit to be posted.

I would like to run the "Clear_NONFOODS" macro w/o having zero's replaced everywhere,
and to be able to Right Click and Clear Contents w/o the having the same problem.
I'm wondering if there is a better way to achieve this?

Is it possible to also have DESIGNATION columns clear if the TARGET cell is cleared?
(This column will be hidden . It is used primarily for sorting when combined with other sheets on to a TOTALS page)

Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
   If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
    On Error GoTo 0

If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
  Application.EnableEvents = False
        Target.Offset(0, 1).Value = "Amex-NonFoods"
        Application.EnableEvents = True
End If

If Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
  Application.EnableEvents = False
        Target.Offset(0, 1).Value = "0"
        Application.EnableEvents = True

End If
End Sub

I’ve included a workbook for inspection.

Thanks!

[max57]

Any suggestions?

[max57]

Would a Select Case Statement be more appropriate in this instance?

[JBeaucaire]

Like so:

Sub ClearNonFoodPage()
    Application.EnableEvents = False
        Range("ClearArea_NONFOOD").ClearContents
    Application.EnableEvents = True
End Sub

The EnableEvents function controls OTHER macros triggering. Turning it off lets it run its code without other macros triggering on there own, which is what is happening.

[JBeaucaire]

Also consider:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
   If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
On Error GoTo 0

Application.EnableEvents = False

    If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
        Target.Offset(0, 1).Value = "Amex-NonFoods"
    ElseIf Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
        Target.Offset(0, 1).Value = "0"
    End If

Application.EnableEvents = True
End Sub

[Huron]

Put

Application.EnableEvents = False

before the first line of your "ClearNonFoods" macro and then

Application.EnableEvents = True

as the last line. This will stop the Worksheet_Change code from running.

To delete the Designation value if the date is cleared, change the code to

If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
  If Target.Value = "" Then
     Target.Offset(0, 1).Value = ""
  Else: Target.Offset(0, 1).Value = "Amex-NonFoods"
  End If
End If

[JBeaucaire]

EDIT on the macro suggested post #5:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
   If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
On Error GoTo 0

Application.EnableEvents = False

    If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
        If Target.Value = "" Then
            Target.Offset(0, 1).Value = ""
        Else
            Target.Offset(0, 1).Value = "Amex-NonFoods"
        End If
    ElseIf Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
        Target.Offset(0, 1).Value = "0"
    End If

Application.EnableEvents = True
End Sub

[max57]

Wow!

Thanks guys!

I'll try it and get back to you.

Mark

[max57]

Huron & JBeaucaire: Adding the App Events for the Clear_NONFOODS Macro worked - Thanks.

Huron & JBeaucaire: Your codes work except when I right click and clear selected rows. Then I get an Error #13 Mismatch at this line:

Application.EnableEvents = False

    If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then

        If Target.Value = "" Then <----------ERROR
     Target.Offset(0, 1).Value = ""
        Else

Thanks.
Mark

[JBeaucaire]

Maybe add this to the top:

If Target.Cells.Count > 1 Then Exit Sub

[max57]

JBeaucaire,

The error is no more but an odd thing happens; if I right click and Clear Contents, the code no longer works.

I have to close the file and reopen it in order for it to function once again.

[JBeaucaire]

Let's see your complete code again. Perhaps you're turning off Events, then Exiting the Sub without turning it back on again.

[max57]

Doesn't seem to be.
Very odd...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
   If Range("NONFOOD_DATES", "NONFOOD_CHARGES") Is Nothing Then Exit Sub
On Error GoTo 0

Application.EnableEvents = False

    If Not Intersect(Target, Range("NONFOOD_DATES")) Is Nothing Then
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Value = "" Then
            Target.Offset(0, 1).Value = ""
        Else
            Target.Offset(0, 1).Value = "Amex-NonFoods"
        End If
    ElseIf Not Intersect(Target, Range("NONFOOD_CHARGES")) Is Nothing Then
        Target.Offset(0, 1).Value = "0"
    End If

Application.EnableEvents = True
End Sub

[JBeaucaire]

Yep, that's exactly what you did. You turn off events in line 6, then put my Exit Sub (quick quit) in row 9 without turning events back on. Oops. If those events occur in that sequence, you've turned of all macros for good.

In post 10, I indicated to put that quit at the top, not line 10. Move it to line 2 and that will offer the quick quit before we've even done anything else.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[max57]

Thanks JBeaucaire!

Works as hoped for.

Mark