Combobox Change event running whenever a row is deleted

[jonnn21]

I have a Combobox that is linked to a pivottable.
This pivot table is a list of Parts categories for an inventory sheet
I have a Drop down that lists all these items from the pivot table
When selecting an item int eh drop down it refilters the list to show just those parts
I have a button that you can press to unfilter and show all the items again
All works great!!

However, when I delete a row anywhere in the table when all the items are showing, it likes to rerun the Combobox change event, which in turns refilters the list again.

Below is the Worksheet Change Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update Quantity
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("C:C,G:G"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mm/dd/yy h:mm AM/PM"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If

All this code is meant for is to enter a date and time in a corresponding cell in the row where a column was changed. - Works very well

So nowhere in that code is it calling the combobox change event.

Here is the combobox change code:

Private Sub CmboCategory_Change()
On Error Resume Next
    ActiveSheet.ShowAllData
CmboCategory.ListRows = Range("ac4").Rows.Count - 1
Cells.AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=Range("AC1").Value
ActiveWindow.ScrollRow = 1
End Sub

I can see why its filtering, but I cant see why deleting a row runs this piece of code

 

[RoryA]

You've already posted a code solution which would be my preferred option, unless you can use a data validation dropdown instead.

 

[jonnn21]

Ok I will give that code another try.

Maybe I missed something really easy. Thanks!!

 

[RoryA]

Also, please read the forum rules on cross-posting and add the appropriate links here. Thank you.

 

[jonnn21]

Also, please read the forum rules on cross-posting and add the appropriate links here. Thank you.

I agree. I did it one direction but not the other way

Deleting a row, run Combobox change event

This is also posted on MrExcel but I see no way to attach an example: I have a list of inventory Each item has a Category associated with it I made a pivottable to list all the unique categories (To get rid of duplicates) I NameRanged that table as Category I created an activex...

www.excelforum.com