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]

How exactly is the combobox populated? Using a ListFillRange?

 

[jonnn21]

That is correct.

I named the Pivottable "Category" then placed that in the ListFillRange of the combobox

 

[jonnn21]

I found this on another forum:

• Re: ComboBox Change Event Triggered by Cell Deletion
  
  1) Delete ListFillRange property from the Property Box.
  2) to ThisWorkbook module
  Code
  1. Private Sub Workbook_Open()
  2. Application.Run "Sheet1.Worksheet_Activate"
  3. End Sub
  
  3) to Sheet1 module
  Code
  1. Private Sub Worksheet_Activate()
  2. Me.ComboBox1.List = Range("b6:b10").Value
  3. End Sub
  
  ListFillRange will update the list whenever the structure of the sheet changes as smuzeon stated.
  

 

[RoryA]

You can't just use a pivot table name like that. Do you mean you created a named range? If so, how did you define it?

 

[RoryA]

Generally, it is better to avoid linking controls directly to ranges if possible. It's also better to avoid activex controls if you can.

 

[jonnn21]

You can't just use a pivot table name like that. Do you mean you created a named range? If so, how did you define it?

Yes Im sorry, its a Named Range

 

[jonnn21]

All im trying to do is make an easier filtering of Parts by category. The excel filter button is too cumbersome. What I made works great until I delete a row and it refilters with whatever is in that box
I created the Pivottable because I needed a way to weed out the duplicates and only show the unique values in the Category column.

 

[RoryA]

Yes Im sorry, its a Named Range

And how exactly did you define the named range? If you used a formula, it's probably volatile so it recalculates when the table changes.

 

[jonnn21]

I highlighted the cells in the pivot table and in the upper left I named the range


I REALLY wish I could explain this better. I apologize. Anyway to upload a cleanup copy?

End Goal:
Combobox to show the unique values of a column
Be able to use that drop down to filter that column
Have a button to un-filter and show all items