VBA MSGBOX displaying Multiple Value VLOOKUP

[David_Sands]

Hi everyone,
Hope someone can help me with this ... I'm trying to work out how to do what is displayed in the image below. The table (cells C9:AB28) are all COUNTIFS based on data on a different worksheet (see the 2nd image below) in the same workbook and when the user clicks on one of the cells (for example I've clicked on Q23 below) a MSGBOX pops up showing all the student names behind the number 17 - or in other words all the people in column D (2nd image below) who match the matrix criteria in the first image below (in this example KS3 Sub-Level 5C and KS2 Sub-Level 5C).
Really hope someone can help me with this and many thanks in advance,
Dave

 

[saltkev]

Hi

Try this in a command button, then perhaps move it to an event that suits you.

Application.ScreenUpdating = False
Dim holder, fx
holder = ""
For Each fx In ActiveSheet.Range("a3:a8") ' Adjust range to Suit Your Sheet
holder = holder & fx & Chr(13)
Next fx
MsgBox holder, vbOKOnly, "Populated Message Box"

Regards

Kev

 

[saltkev]

This Might be better for you, you can define the cell you wish to click to cause the msgbox to display.

Pop the whole thing into your vba window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim holder, fx
holder = ""
If Intersect(ActiveCell, Range("Q23")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display
      
    Else
   
For Each fx In ActiveSheet.Range("a3:a20") ' Adjust to Suit the Range You Wish to Display
holder = holder & fx & Chr(10)
Next fx
MsgBox holder, vbOKOnly + vbInformation, "Populated Message Box"
 
End If
End Sub

Kev

 

[David_Sands]

Thank you for this, however it doesn't seem to be working, I get the msgbox come up but with none of the 17 names that should appear, any ideas?

This Might be better for you, you can define the cell you wish to click to cause the msgbox to display.

Pop the whole thing into your vba window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim holder, fx
holder = ""
If Intersect(ActiveCell, Range("Q23")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display
      
    Else
   
For Each fx In ActiveSheet.Range("a3:a20") ' Adjust to Suit the Range You Wish to Display
holder = holder & fx & Chr(10)
Next fx
MsgBox holder, vbOKOnly + vbInformation, "Populated Message Box"
 
End If
End Sub

Kev

 

[saltkev]

Have you adjusted the range in the code to suit your sheet as detailed in code

 

[saltkev]

hold fire I see you need to a selection on the names, I will have a look at it

 

[saltkev]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim holder, fx
holder = ""
If Intersect(ActiveCell, Range("q23")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display


Else

For Each fx In ActiveSheet.Range("d9:d20") ' Adjust to Suit the Range You Wish to Display
If fx.Offset(0, 2).Value = "5C" Or fx.Offset(0, 3).Value = "5C" Or fx.Offset(0, 3).Value = "5c" Or fx.Offset(0, 2).Value = "5c" Then

holder = holder & fx & Chr(10)
End If
Next fx
MsgBox holder, vbOKOnly + vbInformation, "Populated Message Box"


End If
End Sub

 

[David_Sands]

This works brilliantly, thank you so much, could I just check one more thing though, as there are 520 cells to my table, I would need to repeat this code 520 times, 1 for each different set of parameters ... is this right or is there another automated way that I could do this e.g. is it possible for your 'If' statement in the code below to reference the countifs statement in the range cell on the worksheet (in this example Q23) rather than fixed values (in this example 5C's)?

I hope you don't think I'm being cheeky asking all these questions, trust me your help is hugely appreciated.

Dave

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim holder, fx
holder = ""
If Intersect(ActiveCell, Range("q23")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display


Else

For Each fx In ActiveSheet.Range("d9:d20") ' Adjust to Suit the Range You Wish to Display
If fx.Offset(0, 2).Value = "5C" Or fx.Offset(0, 3).Value = "5C" Or fx.Offset(0, 3).Value = "5c" Or fx.Offset(0, 2).Value = "5c" Then

holder = holder & fx & Chr(10)
End If
Next fx
MsgBox holder, vbOKOnly + vbInformation, "Populated Message Box"


End If
End Sub

 

[saltkev]

Hi

Not a 100% sure what you are asking me but here goes. You could alter the line of code:-

If Intersect(ActiveCell, Range("q23")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display

to

If Intersect(ActiveCell, Range("q23:S77")) Is Nothing Then 'Adjust to Suit the Cell You Click to Cause the box to display

then any cell selected within this range will cause the same msgbox to be displayed. If however you need a different message box for each groupe of cells then that would be different.

Kev

 

[David_Sands]

Hmmmm, still really struggling to get everything to work in various areas, below is a link to the file that I'm trying to replicate, I'd be very grateful if you could take a look and see if you know how I could do this?

https://drive.google.com/file/d/0Bw6W1WHEetl7U3ZaVHYxQXUyZDg/edit?usp=sharing

Please do not worry if this isn't possible or you are too busy,

Many thanks