Macro for displaying messages using values from a different worksheet

Pantherlucky

New Member
Joined
Oct 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello! I need to create a macro to create a message box which will contain the names and the grades of 8 different candidates. These values are located in a different worksheet (Sheet 2). For the first 2 candidates the message must be "*insert name of candidate here* is accepted to Department A with a grade of *insert grade here*". For the next 2 the message must be "*insert name of candidate here* is accepted to Department B with a grade of *insert grade here*". For the next candidate the message is "*insert name of candidate here* is accepted to Department C with a grade of *insert grade here*". Finally for the last three candidates the message must be " *insert name of candidate here* is a back up candidate*". The two columns which are used for this are, as i mentioned, in Sheet2 and are sorted in a descending order ( top to lowest grade). The A column contains the name of the candidates whilst the B column contains their respective grades. Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,003
This macro assumes you have headers in row 1 and the names start in row 2.
VBA Code:
Sub MessageBoxes()
    MsgBox (Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
        & Chr(10) & Range("A3") & " is accepted to Department A with a grade of " & Range("B3"))
    MsgBox (Range("A4") & " is accepted to Department B with a grade of " & Range("B4") _
        & Chr(10) & Range("A5") & " is accepted to Department B with a grade of " & Range("B5"))
    MsgBox (Range("A6") & " is accepted to Department C with a grade of " & Range("B6"))
    MsgBox (Range("A7") & ", " & Range("A8") & " and " & Range("A9") & " are back up candidates.")
End Sub
 

Pantherlucky

New Member
Joined
Oct 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
This macro assumes you have headers in row 1 and the names start in row 2.
VBA Code:
Sub MessageBoxes()
    MsgBox (Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
        & Chr(10) & Range("A3") & " is accepted to Department A with a grade of " & Range("B3"))
    MsgBox (Range("A4") & " is accepted to Department B with a grade of " & Range("B4") _
        & Chr(10) & Range("A5") & " is accepted to Department B with a grade of " & Range("B5"))
    MsgBox (Range("A6") & " is accepted to Department C with a grade of " & Range("B6"))
    MsgBox (Range("A7") & ", " & Range("A8") & " and " & Range("A9") & " are back up candidates.")
End Sub
I want to do exactly this but the columns that i need to use are on Sheet2. Using this code, it runs it on Sheet1. Is there a fix for this?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,003
Try:
VBA Code:
Sub MessageBoxes()
    With Sheets("Sheet2")
        MsgBox (.Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
            & Chr(10) & .Range("A3") & " is accepted to Department A with a grade of " & .Range("B3"))
        MsgBox (.Range("A4") & " is accepted to Department B with a grade of " & .Range("B4") _
            & Chr(10) & .Range("A5") & " is accepted to Department B with a grade of " & .Range("B5"))
        MsgBox (.Range("A6") & " is accepted to Department C with a grade of " & .Range("B6"))
        MsgBox (.Range("A7") & ", " & .Range("A8") & " and " & .Range("A9") & " are back up candidates.")
    End With
End Sub
Also, to avoid clutter, click the "Reply" button instead of the "Reply With Quote" button.
 
Solution

Pantherlucky

New Member
Joined
Oct 31, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hey i have a small question for the code that you sent me earlier. So there seems to be one small bug...(excuse the weird letters, those are supposed to be greek)

With Sheets("Sheet2")
MsgBox (.Range("A1") & " äåêôüò óôï ôìÞìá Á ìå óõíïëéêÜ ìüñéá, " & Range("B1") _
& Chr(10) & .Range("A2") & " äåêôüò óôï ôìÞìá Á ìå óõíïëéêÜ ìüñéá, " & .Range("B2"))
MsgBox (.Range("A3") & " äåêôüò óôï ôìÞìá  ìå óõíïëéêÜ ìüñéá, " & .Range("B3") _
& Chr(10) & .Range("A4") & " äåêôüò óôï ôìÞìá  ìå óõíïëéêÜ ìüñéá, " & .Range("B4"))
MsgBox (.Range("A5") & " äåêôüò óôï ôìÞìá à ìå óõíïëéêÜ ìüñéá, " & .Range("B5"))
MsgBox (.Range("A6") & " Åðéëá÷þí ")
MsgBox (.Range("A7") & " Åðéëá÷þí ")
MsgBox (.Range("A8") & " Åðéëá÷þí ")
End With

The result of "Range("B1") displays the result of the B column, first row of Sheet1 instead of Sheet2 which was assigned via With Sheets("Sheet2"). This is fairly weird and i am wondering if you have an explanation for this. Thanks again for the help!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,003
Rich (BB code):
Sub MessageBoxes()
    With Sheets("Sheet2")
        MsgBox (.Range("A2") & " is accepted to Department A with a grade of " & .Range("B2") _
            & Chr(10) & .Range("A3") & " is accepted to Department A with a grade of " & .Range("B3"))
        MsgBox (.Range("A4") & " is accepted to Department B with a grade of " & .Range("B4") _
            & Chr(10) & .Range("A5") & " is accepted to Department B with a grade of " & .Range("B5"))
        MsgBox (.Range("A6") & " is accepted to Department C with a grade of " & .Range("B6"))
        MsgBox (.Range("A7") & ", " & .Range("A8") & " and " & .Range("A9") & " are back up candidates.")
    End With
End Sub
I missed entering a period in .Range("B2")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,094
Messages
5,576,072
Members
412,696
Latest member
khanhvy31
Top