Results 1 to 3 of 3

Thread: Sheetchange

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to run some code after an "AutoFilter" dropdown has been changed; doesn't matter which dropdown.

    I have tried using "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" and it does not appear that the sheet actually changes.

    Any ideas?

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jstevens' post='770062' date='10-Apr-2009 19:55']I would like to run some code after an "AutoFilter" dropdown has been changed; doesn't matter which dropdown.

    I have tried using "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" and it does not appear that the sheet actually changes.

    Any ideas?

    Thanks,
    John[/quote]
    Hi John

    I suspect that you have the wrong concept. Changing the value of a cell is an event that triggers the "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" procedure. The following code is an example of the procedure at work.

    [codebox]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim isect As Variant
    Set isect = Application.Intersect(Target, Range("A:A"))
    If Not isect Is Nothing Then
    MsgBox "Column A on sheet " & Sh.Name & " has been changed."
    End If
    End Sub[/codebox]
    Regards
    Don

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An autofilter change does not trigger any event in VBA. You can workaround this problem by putting a SUBTOTAL calculation in the worksheet based on the autofilter data. This will be recalculated whenever any of hte autofilter items are changed. This change in formula will trigger a "Worksheet_Calculate" event which you can run the code...

    [Note it will be triggered by other calculations as well so can slow down the worksheet if many calculations are done]

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •