Adding message boxes to more than one cell

[CraigRackham]

Hi,

I have this code to produce a message box when a cell is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("K26")) Is Nothing Then _
MsgBox "INC000011108068 " & "- Pending Issue", vbInformation, "Joseph Waheed"
End Sub

How can i add more message boxes to other cells so that each selected cell will present a different message box?

Thanks,
Craig

 

[Yard]

Just add another If statement??

Questions:
How many cells are you talking about?
Does each cell generate a different message?
You hardcoded the address "K26", so does that mean the cell addresses will never change?
Why concatenate the two strings "INC000011108068 " & "- Pending Issue"?

Yard

 

[CraigRackham]

Just add another If statement??

Questions:
How many cells are you talking about? It could be as many as 200 depends on teh information i need to add

Does each cell generate a different message? I would like this to happen yes

You hardcoded the address "K26", so does that mean the cell addresses will never change? I want each cell to present it's own message in the message box

Why concatenate the two strings "INC000011108068 " & "- Pending Issue"? I have ammended this to one string, thanks

I got this code from the internet as i'm still a beginner where excel is concerned but would love to learn more.

Thanks for your reply too!



Yard

Please see answers to your questions in RED

 

[My Aswer Is This]

You can add as many as you like. Just do it this way. Now only one Message Box can be visible at any time.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("K26")) Is Nothing Then _
 MsgBox "INC000011108068 " & "- Pending Issue", vbInformation, "Joseph Waheed"
 
 If Not Intersect(Target, Range("K27")) Is Nothing Then _
 Target.Interior.ColorIndex = 6: MsgBox "Hello I'm setting on cell " & Target.Address
 
 If Not Intersect(Target, Range("K28")) Is Nothing Then MsgBox "Good Morning"
 
End Sub

 

[My Aswer Is This]

If you wanted you could use this ideal to add a comment to the target cell that would stay visible

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("K28")) Is Nothing Then Target.AddComment "Im Watching you"
 End Sub

 

[Yard]

As My Answer Is This has shown, you can add as many If statements as you like, one after the other. The interesting thing is that, if you select range K26:K28, then it will execute each statement in turn and you will get 3 message boxes. Personally I would avoid change the colour of the cell in that way, since that change will apply to the entire selection.

However, since your number of dependent cells is ~200, that's a lot of IF statements to write and maintain. I would be more inclined to have a table (separate tab) in your spreadsheet which lists the relevant cell addresses and the corresponding messages. Then your code can simply check if the address of the selected cell appears in the table and then return the message. That would seem easier to maintain?

 

[CraigRackham]

You can add as many as you like. Just do it this way. Now only one Message Box can be visible at any time.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("K26")) Is Nothing Then _
 MsgBox "INC000011108068 " & "- Pending Issue", vbInformation, "Joseph Waheed"
 
 If Not Intersect(Target, Range("K27")) Is Nothing Then _
 Target.Interior.ColorIndex = 6: MsgBox "Hello I'm setting on cell " & Target.Address
 
 If Not Intersect(Target, Range("K28")) Is Nothing Then MsgBox "Good Morning"
 
End Sub

Works a treat, thank you very much mate

 

[Yard]

My alternative, using a table to maintain your 200 messages:

• A sheet with a VB name of "shtMsgTable"
• A named range on that sheet called "msgtable"
• That range has two columns, one listing the addresses, the next listing the messages relating to those addresses
• This code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim strAddress As String, strMessage As String
Dim lColMessage
Dim rngCell As Range, rngMsgTable


lColMessage = 2
Set rngMsgTable = shtMsgTable.Range("msgtable")
On Error Resume Next


For Each rngCell In Target
    strMessage = ""
    strAddress = Replace(rngCell.Address, "$", "")
    strMessage = WorksheetFunction.VLookup(strAddress, rngMsgTable, lColMessage, 0)
    If strMessage <> "" Then MsgBox strMessage
Next rngCell


End Sub

 

[My Aswer Is This]

I had a similar ideal now that "Yard" suggested this:
In my way do this:

In Sheet(2) Column "A" enter your cells address you want comments for.
Enter the address like this $F$12 for example
In Sheet(2) Column "B" enter the comment you want for that cell.
Start entering your address's in row(1) and keep going as far as you want
Then put this script in your "Master" sheet with your data

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Sheets(2).Range("A1:A" & Lastrow)
        If c.Value = Selection.Address Then MsgBox c.Offset(0, 1).Value
    Next
End Sub