Use Select Case to launch an MsgBox

[Tanner_2004]

This seems easy enough, but can't get it right.

Sheet 1 has a column of 4 cells, B4 through B7. I want to immedialty alert the user that s/he has entered a value greater than 1 in any of the 4 cells at the time s/he is enters the value. I don't wan't the user to wait until all 4 values have been entered, but to inform him or her immediately after entering a value greater than 1 in any of the cells. I would like the alert to be in the form of a msgbox.

I understand that this can be achieved using datal validation, but I have other plans for this code.

Thank you,

Tony

 

[njimack]

The below code should be pasted into the module of the sheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Range("B4:B7"), ">" & 1) > 0 Then MsgBox "Uh oh!", vbCritical
End Sub

 

[pbornemeier]

This will work for a single cell, as well as if multiple cells were changed (such as through a paste action). It should be placed in the codepage of the worksheet involved.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lX As Long
    Dim sOutput As String
    
    If Not Intersect(Range("B4:B7"), Target) Is Nothing Then
        For lX = 4 To 7
            With Cells(lX, 2)
                If .Value <> vbNullString Then
                    If .Value > 1 Then
                        sOutput = sOutput & .Address(False, False) & ", "
                    End If
                End If
            End With
        Next
        
        If sOutput <> vbNullString Then
            sOutput = Left(sOutput, Len(sOutput) - 2)
            MsgBox "The value in cell" & IIf(Len(sOutput) > 2, "s ", " ") & sOutput & " is greater than 1.  Correct this error and try again."
        End If
        
    End If
End Sub

 

[Tanner_2004]

Wow, thank you both! You responded in less time than it took me to compose the question! I will give both a try. Thanks again!