Mouseover macro doesn't fire advanced filter.

[Rob]

Hi everyone,

I've previously asked this question in the Excel Hero Academy forum, but haven't had any luck.

I'm trying to use a mouseover macro on sheet1 to change the contents of a cell on sheet2 [current_rd].

Public Function RoundOne()
 
If [current_rd] = 1 Then
    Exit Function
        Else
            [current_rd] = 1
End If
 
End Function

When the contents of [current_rd] change I want an advanced filter to fire. Unfortunately nothing happens.

The worksheet change event fires, but the advanced filter doesn't work. Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Intersect(Target, [current_rd]) Is Nothing Then
        Exit Sub
    End If
 
    'MsgBox "Worksheet_Change event has fired!"
 
    [d_inclusive_rds_name].AdvancedFilter xlFilterCopy, , [filtered],
 
Unique:=True
 
    With Sheets(1).Shapes("Scroll Bar 3").ControlFormat
 
        .Min = 1
 
        .Max = [L_Scroll_Max]
 
        .SmallChange = 1
 
        .LargeChange = 1
 
        .LinkedCell = "L_Scroll_Pos"
 
    End With
 
End Sub

When I change the contents of [current_rd] manually it works fine.

I also tried using a slicer to change [current_rd] but still nothing happens.

Has anyone had any experience of this and knows of a workaround?

Thanks for any help, this has been bugging me for a while now.

Rob

 

[SirJB7]

Hi, Rob!
Tried adding a space followed by an underscore after "[filtered]," (continuation line) or deleting characters after "[filtered]," so as "Unique..." gets into the same line?
But that doesn't explain why it works when doing manually
Regards!
PS: Any chance uploading a sample file?

 

[Rob]

Hi Sir,
I'm not sure what happened when i posted the code, but the 'Unique' is on the same line in the original code.

I've also this evening added a drop down (data validation) list and linked current_rd to it, but still no advanced filter.

Only manually typing a number into current round and hitting enter fires it.

Thanks for taking a look.

Rob

 

[SirJB7]

Hi, Rob!
Not more clues just with the posted code without squeezing my brains, so maybe you could upload a sample file just to check the described behavior.
Regards!