Pop Up Excel VBA MsgBox When Cell Meets Criteria

Code Explanation

Sub ShowMarksSummary()
    Dim ws As Worksheet
    Dim rng As Range
    Dim totalMarks As Long
    Dim avgMarks As Double
    Dim belowAvg As String, aboveAvg As String, equalAvg As String
    Dim cell As Range
    Set ws = ActiveSheet
    Set rng = ws.Range("A5:D12")

We have declared the necessary variable and selected a range from A5 to D12.

totalMarks = Application.WorksheetFunction.Sum(rng.Columns(4))
   avgMarks = Application.WorksheetFunction.Average(rng.Columns(4))

These two lines get the sum and the average marks.

For Each cell In rng.Columns(4).Cells
        If cell.Value < avgMarks Then
            belowAvg = belowAvg & cell.Offset(0, -2).Value & vbNewLine
        ElseIf cell.Value > avgMarks Then
            aboveAvg = aboveAvg & cell.Offset(0, -2).Value & vbNewLine
        Else
            equalAvg = equalAvg & cell.Offset(0, -2).Value & vbNewLine
        End If
    Next cell

We get the names of the student getting below-average, average, and above-average numbers.

cell.Offset(0, -2).Value & vbNewLine

This snippet extracts the names and creates a new line.

MsgBox "The average marks in English subject is " & avgMarks

This line of code shows the average marks in the  MsgBox.

If belowAvg <> "" Then
        MsgBox "The following students scored below average in English subject:" & vbNewLine & belowAvg, vbCritical
    End If
    If aboveAvg <> "" Then
        MsgBox "The following students scored above average in English subject:" & vbNewLine & aboveAvg, vbInformation
    End If
    If equalAvg <> "" Then
        MsgBox "The following students scored equal to average in English subject:" & vbNewLine & equalAvg
    End If
End Sub

This snippet shows Excel MsgBox with different criteria. The code shows the student name one line after another getting three different categories.

Code Explanation

Sub ShowStudentNamesStatus()
    Dim name As String
    Dim obtainedNumber As Double
    Dim goodNames As String
    Dim satisfactoryNames As String
    Dim failedNames As String
    goodNames = ""
    satisfactoryNames = ""
    failedNames = ""

• name is a string variable that will hold the name of a student.
• obtainedNumber is a double variable that will hold the numerical score obtained by a student.
• goodNames, satisfactoryNames, and failedNames are string variables that will hold the names of students who achieved good, satisfactory, and failed scores, respectively.

The variables goodNames, satisfactoryNames, and failedNames are initialized to an empty string. This is because they will be used to store the names of students based on their scores. The code will loop through a list of students, determine their scores, and add their names to the corresponding variable.

   For i = 5 To 13
        name = Cells(i, 2).Value
        obtainedNumber = Cells(i, 4).Value
        If obtainedNumber >= 70 And obtainedNumber <= 100 Then
            goodNames = goodNames & name & vbCrLf
        ElseIf obtainedNumber > 40 And obtainedNumber <= 69 Then
            satisfactoryNames = satisfactorytNames & name & vbCrLf
        ElseIf obtainedNumber < 39 Then
            failedNames = failedNames & name & vbCrLf
        End If
    Next i

• The name variable is assigned the value of the cell in column 2 (i.e., column B) and the current row number (i).
• The obtainedNumber variable is assigned the value of the cell in column 4 (i.e., column D) and the current row number (i).
• The code then checks the value of the obtainedNumber variable against three different conditions:

1. • If the obtainedNumber is between 70 and 100, the name variable is added to the goodNames string along with a line break (vbCrLf).

• • If the obtainedNumber is between 40 and 69, the name variable is added to the satisfactoryNames string along with a line break (vbCrLf).

• • If the obtainedNumber is less than 39, the name variable is added to the failedNames string along with a line break (vbCrLf).
• The loop continues until it has iterated through all rows from 5 to 13.

  If goodNames <> "" Then
        MsgBox "Good Marks obtained: " & vbCrLf & goodNames, vbInformation, "Good Mark"
    End If
    If satisfactoryNames <> "" Then
        MsgBox "Satisfactory Mark obtained: " & vbCrLf & satisfactoryNames, vbInformation, "Satisfactory"
    End If
    If failedNames <> "" Then
        MsgBox "Failed Marks obtained: " & vbCrLf & failedNames, vbCritical, "Failed"
    End If
End Sub

• The first If statement checks if the goodNames string variable is not empty. If it’s not empty, it means that some students achieved good scores. The MsgBox function displays a message box that shows the string “Good Marks obtained:” followed by a line break and the goodNames variable. The message box also has an icon of an “information” symbol and a title “Good Mark“.
• The second If statement checks if the satisfactoryNames string variable is not empty. If it’s not empty, it means that some students achieved satisfactory scores. The MsgBox function displays a message box that shows the string “Satisfactory Mark obtained:” followed by a line break and the satisfactoryNames variable. The message box also has an icon of an “information” symbol and a title “Satisfactory“.
• The third If statement checks if the failedNames string variable is not empty. If it’s not empty, it means that some students failed to achieve passing scores. The MsgBox function displays a message box that shows the string “Failed Marks obtained:” followed by a line break and the failedNames variable. The message box also has an icon of a “critical” symbol and a title “Failed“.

Code Explanation

Sub FindStudents_AccordingtocertainNumber()
    Dim studentName As String
    Dim obtainedNumber As Integer

The code declares two variables, studentName as a string and obtainedNumber as an integer. These variables will be used to store the student’s name and their score in the exam, respectively.

    For i = 5 To 13
        obtainedNumber = Cells(i, 4)
        If obtainedNumber = 98 Then
            studentName = Cells(i, 2)
            MsgBox studentName & " obtained a score of 98 in English."
        End If
    Next i
End Sub

• The For loop iterates through rows 5 to 13 of the worksheet.
• Within the loop, the code reads the score from column 4 of the current row using the Cells function and stores it in the obtainedNumber variable.
• The If statement checks if the obtainedNumber variable is equal to 98, which is the desired score.
• If the score is equal to 98, the code reads the name of the student from column 2 of the same row using the Cells function and stores it in the studentName variable.
• The code then displays a message box with the name of the student and their score using the MsgBox function.

Code Explanation

Sub FindStudents()
    Dim studentName As String
    Dim comment As String
    Dim passedStudents As String

The code declares three variables, studentName as a string, comment as a string, and passedStudents as a string. These variables will be used to store the name of the student, the comment about their performance, and the list of names of students who passed the English subject, respectively.

The For loop iterates through rows 5 to 13 of the worksheet.

 comments = Cells(i, 5)
        If comments = "Passed" Then
            studentName = Cells(i, 2)
            passedStudents = passedStudents & studentName & ", "
        End If
    Next i

• Within the loop, the code reads the comment from column 5 of the current row using the Cells function and stores it in the comment variable.
• The If statement checks if the comment variable is equal to “Passed”, which indicates that the student has passed the English subject.
• If the student has passed, the code reads the name of the student from column 2 of the same row using the Cells function and appends it to the passedStudents string with a comma and space.

If Len(passedStudents) > 0 Then
        passedStudents = Left(passedStudents, Len(passedStudents) - 2)
        MsgBox "The following students passed the English subject: " & passedStudents, vbInformation, "passed"
    Else
        MsgBox "No students passed the English subject."
    End If
End Sub

• The Len function checks the length of the passedStudents string.
• If the length of the passedStudents string is greater than 0, the code removes the last comma and space from the string using the Left function and displays a message box with the names of the students who passed the English subject using the MsgBox function. If there are no students who passed, the code displays a message box with the appropriate message.
• The program ends.

Code Explanation

Dim prevValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    prevValue = Target.Value
End Sub

• Worksheet_SelectionChange: This event procedure is triggered whenever a new cell is selected in the worksheet. The code stores the previous value of the selected cell in a variable called prevValue. This is useful if you want to track changes made to the cell later.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Cell " & Target.Address & " has been changed from " & prevValue & " to " & Target.Value
End Sub

• Worksheet_Change: This event procedure is triggered whenever a cell in the worksheet is changed. The code displays a message box with the address of the changed cell, the previous value stored in prevValue, and the new value in the cell. This can be helpful for auditing and tracking changes made to cells in a worksheet.

  Code Explanation

Sub FindBlankCells()
Dim rng As Range
Dim cell As Range
Set rng = Selection 'Get the selected range

The code first declares two variables: rng and cell. rng will be used to store the range of cells selected by the user, and the cell will be used to loop through each cell in that range.

The code sets the range rng to the user’s selection using the Selection object.

For Each cell In rng 'Loop through each cell in the range
If IsEmpty(cell) Then 'Check if the cell is empty
MsgBox "Cell " & cell.Address & " is blank.", vbCritical, "BlankCell"
End If
Next cell
End Sub

The code then enters a loop that iterates through each cell in the range rng. For each cell, the code checks if it is empty using the IsEmpty function. If the cell is empty, the code displays a message box using the MsgBox function that informs the user of the empty cell’s location.

The message box displays the text “Cell [cell address] is blank.” with the address of the blank cell, and the vbCritical flag is used to display the message box with a red X icon to indicate that the message is an error or critical warning.

Code Explanation

Sub CheckConsistency()
    Dim rng As Range
    Dim cell As Range
    Dim isConsistent As Boolean
    Dim inconsistentCells As String
    isConsistent = True ' Assume consistency until proven otherwise
    Set rng = Range("c5:c" & Range("B" & Rows.Count).End(xlUp).Row) ' Set the range to check (assuming the table starts in cell B2)

This VBA subroutine starts by declaring several variables including a range object rng, a range object cell, a boolean variable isConsistent, and a string variable inconsistentCells.

It sets the value of isConsistent to True as an initial assumption.

Then, it sets the value of rng to a range of cells in column C starting from row 5 and going down to the last non-empty row in column B. This range is determined by using the Range function and the Rows.Count property to find the last non-empty row in column B, and the End method with the xlUp parameter to find the last non-empty cell in column C.

 For Each cell In rng ' Loop through each cell in the range
        If Not IsEmpty(cell.Value) And cell.Value <> "English" Then  ' Check if the value is not empty and not "English" nor "Math"
            isConsistent = False ' Set the flag to indicate inconsistency
            If inconsistentCells <> "" Then ' If this is not the first inconsistent cell, add a comma before appending the address
                inconsistentCells = inconsistentCells & ", "
            End If
            inconsistentCells = inconsistentCells & cell.Address ' Record the address of the inconsistent cell
        End If
    Next cell

• The code starts by using a For Each loop to iterate over each cell in the specified range (rng).
• The If statement checks two conditions for each cell:
  a. If the cell is not empty: This is checked using the IsEmpty function which returns a Boolean value (True or False) indicating whether the cell is empty or not.
  b. If the cell value is not “English”: This is checked using a logical operator (And) in combination with the Not operator. If the value is not “English” and not “Math”, the code will execute the following block of code.
• If the cell value does not meet the criteria mentioned in step 2, the isConsistent variable is set to False to indicate that there is inconsistency in the data.
• The code checks if inconsistentCells is empty or not. If it is not empty, the code adds a comma and space before appending the address of the inconsistent cell to the string variable.
• The cell address is obtained using the Address property of the cell object.
• Finally, the loop moves to the next cell in the range and repeats the process.

   If Not isConsistent Then ' Display an error message if the flag is set
        MsgBox "The Subject column contains inconsistent data type. The inconsistent cells are: " & inconsistentCells, vbCritical, "Error"
    End If
End Sub

This code displays an error message using the MsgBox function if the consistency check of the data in a specified range reveals inconsistencies. The message includes a custom error message and a list of cell addresses that were identified as inconsistent, which is stored in the inconsistentCells variable. The vbCritical argument of the MsgBox function specifies that the message box should display a critical icon to indicate the severity of the error. The purpose of this code is to provide a clear and concise error message to the user when there are inconsistencies in the data.

Code Explanation

For i = 1 To i
        rNumber = WorksheetFunction.RandBetween(1000, 2000)
        MsgBox rNumber
    Next i

This VBA code generates a random number between 1000 and 2000 using the RandBetween function of the WorksheetFunction object, and displays the number in a message box. The For loop is used to repeat this process 10 times, and the variable i is used as the loop counter. Finally, the message box displays each random number generated during the loop.