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:
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:
I can see why its filtering, but I cant see why deleting a row runs this piece of code
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:
VBA 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
So nowhere in that code is it calling the combobox change event.
Here is the combobox change code:
VBA 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
Last edited by a moderator: