Combobox Change event running whenever a row is deleted

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
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:
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
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:
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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,680
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You've already posted a code solution which would be my preferred option, unless you can use a data validation dropdown instead.
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
Ok I will give that code another try.

Maybe I missed something really easy. Thanks!!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,680
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Also, please read the forum rules on cross-posting and add the appropriate links here. Thank you.
 

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,518
Messages
5,548,521
Members
410,844
Latest member
Juno49
Top