VBA - multiple filters from clicking different cells

[jh9940]

Hi

I successfully used the following code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("I2")) Is Nothing Then
Call AR1_KS3
End If
End If
End Sub

I have multiple macros that filter data (school grades) e.g., Key Stage 3 - Academic Review 1. My problem is that whenever I add the same code again for another macro it doesn't work and I get the error message: Compile error: Ambiguous name detected: worksheet_selectionChange.

I've now learnt you can't have two of the same and I am not skilled enough to be able to write multiple macros within the same bit of code.

How can I have multiple macros assigned to different cells please?

Huge thanks in advance

JH

 

[Joe4]

Welcome to the Board!

Just repeat this section in your code, replacing the range address and the procedure you want to call each time:

If Not Intersect(Target, Range("I2")) Is Nothing Then
    Call AR1_KS3
End If

 

[jh9940]

Thank you so much for your reply. I tried this code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("I2")) Is Nothing Then
Call AR1_KS3
If Not Intersect(Target, Range("E2")) Is Nothing Then
Call clear_filters
End If
End If
End Sub

I added the bit in bold but I get the following error message: "Compile error: Block If without End If". Sorry to be a pain!

Many thanks

 

[Joe4]

You have three IFs, but only two END IFs.

In any event, I don't think your code will work. You do NOT want to imbed the IFs inside one another. They should be totally separate.
I also changed your first IF to be self-contained and simply exit the sub if that first condition is not met (the one checking how many cells were updated).

So try this. If you need to add more, follow the same pattern ("Third Check", etc).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Selection.CountLarge  > 1 Then Exit Sub

'   First Check
    If Not Intersect(Target, Range("I2")) Is Nothing Then
        Call AR1_KS3
    End If

'   Second Check
    If Not Intersect(Target, Range("E2")) Is Nothing Then
        Call clear_filters
    End If

End Sub

 

[jh9940]

Bloody brilliant!! Thank you for solving this - amazing.

To be super cheeky - I have five identical sheets (Year 7, Year 8, Year 9, Year 10, Year 11). Is there anyway to apply the above code to all the sheets? i.e., it doesn't matter if I click E2 on the Year 7 or Year 9 sheet it will still run the same macro.

Do you have a 'buy me a coffee' link so that I can get one for you?

Thanks again

 

[Joe4]

I know you can do it for a change value across all sheets in a workbook, but I do not see a way to do it for cell selections.
(I am not saying with 100% certainty it is not possible, just saying that I do not see a way to do it).

At worst, you would just need to copy that code in to the Sheet modules for the 5 sheets you want to apply it to.

 

[jh9940]

Thank you again!

 

[Joe4]

You are welcome.

 

[Joe4]

I know you can do it for a change value across all sheets in a workbook, but I do not see a way to do it for cell selections.
(I am not saying with 100% certainty it is not possible, just saying that I do not see a way to do it).

At worst, you would just need to copy that code in to the Sheet modules for the 5 sheets you want to apply it to.

Actually, I misspoke. There is a way to do it in just one procedure!
In the "ThisWorkbook" module, there is a event procedure called "SheetSelectionChange". If you put the code in there, it will apply to ALL sheets in your workbook.

So you could use that. Just bear in mind that it there are sheets you do NOT want to apply it to, you will need to add coding in there to check the sheet name first.

 

[jh9940]

Hi, sorry to be a pain again.

All of my macros work perfectly with the above solution apart from one.

Basically, on the 'Home' sheet are 5 boxes with "Year 7", "Year 8" etc..., which open up the corresponding sheets. On each of the sheets is a button called 'Home' which users can click to go back to 'Home' - i.e., essentially allowing them to close all sheets and start again to look at another year group.

This is the code that I currently use under ThisWorkbook:

Sub Workbook_Open()
Show_Home_Only
ProtectAndAllowFiltering
End Sub

Sub Show_Home_Only()
Dim curSheet As Object
For Each curSheet In Sheets
If curSheet.Name <> "Home" Then
curSheet.Visible = False
End If
Next curSheet
End Sub

Sub ProtectAndAllowFiltering()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="school", UserInterFaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
Next wSheetName
End Sub


Whenever I try to use 'Show_Home_Only' on each sheet with your solution above I get an error message.

Huge thanks for your help