CODEWITHSUNDEEP
vbaexcel
takiyasamsama
takiyasamsama

Reputation: 21

Display message when cell is empty

Currently I managed to do for a single cell when the specified cell is empty then message / statement display on the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("AA17").Value = ISBLANK Then
    Range("AA17").Value = "Please Specify"
End If

End Sub

What I would like to do is, for a several cell it will display the same thing. I can go ahead and do the same as above for all celsl but I have a few hundred cell to format it that way.

Is there a way to do so?

Upvotes: 1

Views: 455

Answers (2)

PASUMPON V N
PASUMPON V N

Reputation: 1186

if there is any changes within the specified Range, the below code will run

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Rng As Range
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    Set Rng = ws.Range("A1:A100")

        If Not Intersect(Target, Rng) Is Nothing Then
            For Each Cell In Rng
                If IsEmpty(Cell.Value) = True Then
                     Cell.Value = "Please Specify"
                End If
            Next
        End If

    Set Rng = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub

Upvotes: 1

teylyn
teylyn

Reputation: 35915

If the cells are contiguous, you could loop through them.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim checkRng As Range
Dim cel As Range

Set checkRng = Range("A7:A70")
For Each cel In checkRng
    If cel.Value = ISBLANK Then
        cel.Value = "Please Specify"
    End If
Next cel

End Sub

Upvotes: 1

Related Questions