Pop-up with supplemental info using VBA

[Spreadthemsheets]

Hi all,

Thanks in advance for any help that you can provide. I haven't used VBA in years, but I'd like to get back into it and I'm having some trouble.
Here is the scenerio:

I have a product description and a range of values as percentage in cells H12:K12. I need to have a pop-up message with some background info on the percentages (mostly just contextual and for informative purposes only )displayed when a user selects any of the cells in that range.
A different message will need to be displayed if the user selects a cell in L12:O12 and again for P12:S12. So there are 3 unique messages for cells cell selection in 3 ranges within row 12. I will need to do the same thing for row 13, and on down the spreadsheet for about 20 rows.

I thought to do this with data validation initially, but the text limit seems to be too small.

 

[BarryL]

something like?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Row
      Case Is >= 12
Select Case Target.Column
      Case Is = 8, 9, 10, 11
            MsgBox "H:K"
      Case Is = 12, 13, 14, 15
            MsgBox "L:O"
      Case Is = 16, 17, 18, 19
            MsgBox "P:S"
End Select
End Select
End Sub

add to whatever worksheet object in vba it applies to.

 

[Spreadthemsheets]

Thank you, this is SOOO close (much better than what I had earlier) the only issue I am having with this is that it is the same message for each row. When I move to the next row the message would be unique for that row.

SO, for example when any cell within H12:K12 are selected the message will be "H12:K12" but if a cell is selected in H13:K13 the message would be "H13:K13"

 

[BKrukowski]

Use the event handler

Private Sub Worksheet_SelectionChange(ByVal Target as Range)

Dim Msg1 as String, Msg1Range as Range
Dim Msg2 as String, Msg2Range as Range
Dim Msg3 as String, Msg3Range as Range

Msg1 = [I]"Enter you message here"[/I]
Msg2 = "[I]Enter you message here"[/I]
Msg3 = [I]"Enter you message here"
'You can add to the number of messages if lines 13 - 20 need different messages 

[/I]Set Msg1Range = Range("H12:K12")
Set Msg2Range = Range("L12:O12")
Set Msg3Range = Range("P12:S12")

[I]'You can add to the number of Range Objects if lines 13 - 20 need different messages or else you can set the Range to H12:K32 

'Now check to see if the cell clicked on is within the MsgRanges

[/I]If Not Intersect (Target, Msg1Range) is nothing then [B][I]' this says if the range Msg1Range, and the Currently Selected Range dose not return a value of nothing (meaning they are not not within the same address) 

[/I][/B]      Msgbox Msg1

end if

If Not Intersect (Target, Msg2Range) is nothing then [B][/B]      Msgbox Msg2
end if

If Not Intersect (Target, Ms32Range) is nothing then [B][/B]      Msgbox Msg3
end if

End Sub

 

[BarryL]

how different are the messages? are they completely different texts or is it just mentioning the row number that's different. the latter is an easy fix but the former would require unique messages written into vba for each instance.

 

[Spreadthemsheets]

There are unique messages for each group of 4 cells. So
Group 1 will be H12:K12 with it's own message
Group 2 will be L12:O12 with it's own message
Group 3 will be P12:S12 with it's own message

Moving to the next row will be a whole new set of messages.

There is probably a more efficient way to do this, maybe if I have the messages stored somewhere else in the workbook? for instance Group1 message is in Sheet 2 in cell B2?

 

[BarryL]

yea so if the message was "you're in H12" and it was only the H12 that had to change that would be easy.

if it changes from "I love the Lions" to "I hate Pepsi" then that makes it tricky.

if you could store each message in a cell on each line so H:K has a different message in lets say B13, B13 etc and L:O is in C12,C13 etc and P:S is in D12,D13 etc you could use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Row
      Case Is >= 12
Select Case Target.Column
      Case Is = 8, 9, 10, 11
            cells(target.row,2).value
      Case Is = 12, 13, 14, 15
            cells(target.row,3).value
      Case Is = 16, 17, 18, 19
            cells(target.row,4).value
End Select
End Select
End Sub

 

[Spreadthemsheets]

When I run this I am getting a error: "Compile error: End If without block If"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Msg1 As String, Msg1Range As Range
Dim Msg2 As String, Msg2Range As Range
Dim Msg3 As String, Msg3Range As Range


Msg1 = "Group 1 message"
Msg2 = "Group 2 message"
Msg3 = "Group 3 message"
Msg4 = "Group 4 message"
Msg5 = "Group 5 message"
Msg6 = "Group 6 message"




'You can add to the number of messages if lines 13 - 20 need different messages


Set Msg1Range = Range("H12:K12")
Set Msg2Range = Range("L12:O12")
Set Msg3Range = Range("P12:S12")
Set Msg4Range = Range("P13:S13")
Set Msg5Range = Range("P13:S13")
Set Msg6Range = Range("P13:S13")




'You can add to the number of Range Objects if lines 13 - 20 need different messages or else you can set the Range to H12:K32


'Now check to see if the cell clicked on is within the MsgRanges


If Not Intersect(Target, Msg1Range) Is Nothing Then  ' this says if the range Msg1Range, and the Currently Selected Range dose not return a value of nothing (meaning they are not not within the same address)


      MsgBox Msg1


End If


If Not Intersect(Target, Msg2Range) Is Nothing Then MsgBox Msg2
End If


If Not Intersect(Target, Ms32Range) Is Nothing Then MsgBox Msg3
End If


End Sub

 

[BKrukowski]

There are unique messages for each group of 4 cells. So
Group 1 will be H12:K12 with it's own message
Group 2 will be L12:O12 with it's own message
Group 3 will be P12:S12 with it's own message

Moving to the next row will be a whole new set of messages.

There is probably a more efficient way to do this, maybe if I have the messages stored somewhere else in the workbook? for instance Group1 message is in Sheet 2 in cell B2?

Typing the message into a Cell B2 on sheet2 is just as easy as writing the Message in the VBA Modal. Is there some information you want to pull from the sheet to add it to your message, or will you need to change the message frequently and you do not want to enter the VBA editor to do it?

 

[BKrukowski]

What line is the error on when you click debug?