Class Module Triggered by multiple Target.Cell(1).Addresses

[RawlinsCross]

Good day,

I have a dashboard on a worksheet that has 50-60 numbers, when you click on any of those numbers a 30-day trend on a userform appears. In my excel vba naivety I inserted 50-60 userforms all with identical code to form the graph. Everything works but boy, what a waste!

I have 50-60 trigger events as shown below (just first 5 shown). How do I lump them all 50-60 of these calls together and call a single class module userform when I click on any of those cells? I have figured out all the class module code - all I need to learn is how to trigger it with all the Target.Addresses I have.

 If Target.Address = "$K$10" Then
        A212PRODUCTPCTS.Show vbModeless
    End If
    
If Target.Cells(1).Address = "$C$14" Then
        A212FLOWWATER.Show vbModeless
    End If
    
If Target.Cells(1).Address = "$H$12" Then
        A212FLOWSMD.Show vbModeless
    End If
    
If Target.Cells(1).Address = "$M$17" Then
        A212FLOWCON.Show vbModeless
    End If
    
If Target.Cells(1).Address = "$W$19" Then
        A212FLOWCPL.Show vbModeless
    End If

 

[RoryA]

You could do something like this:

If Not Intersect(Target, Range("K10,C14,H12,M17,W19")) Is nothing Then
        combined_form.Show vbModeless
    End If

 

[RawlinsCross]

Thanks Rory... did a count of these numbers. There's over 150 of them (sorry for the miscount) and yes, my file has a userform for each one. Might you comment on the feasibility of this plan

step#1 - Encompass all the numbers in a named range
step#2 - For each cell is range, if "isnumber" then add to an array
step#3 - Perform your intersect function as Intersect(Target, array)

John

 

[RoryA]

I'm not clear on why you need step 2. If Target intersects the named range, do whatever you need to do.

 

[RawlinsCross]

The numbers are not in neat rows and columns, they are spaced on in a 'map' and there are empty cells between them.

 

[RoryA]

If your named range only includes the populated cells, that won't matter.

 

[RawlinsCross]

Okay, I'll give it a go. Cheers.