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:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
How exactly is the combobox populated? Using a ListFillRange?
 

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
That is correct.

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

jonnn21

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

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

ADVERTISEMENT

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

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
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

New Member
Joined
Feb 9, 2012
Messages
21

ADVERTISEMENT

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

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

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
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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,519
Messages
5,548,523
Members
410,844
Latest member
Juno49
Top