CODEWITHSUNDEEP
excelvba
user2872175
user2872175

Reputation: 25

Check If excel cell is empty

I need vba code for excel. I'm checking whether A1, B7, C9 are empty onclick.

If they are empty (any or all of the variables is/are empty), I need to return:
" is so so so cell is empty click ok to fill it"

If none of the cells is empty, or if all of them are not empty, or they contain any value, I need to return:
"Do my stuff"

There is a link to a particular workbook, but I wanted to check here also.

Upvotes: 0

Views: 16263

Answers (4)

Santosh
Santosh

Reputation: 12353

If you prefer using non vba solution then you can conditional formatting. It wont give a message box but would highlight the cells when they are blank.

To use conditional formatting follow the below steps

  • Select the cells
  • Under Home Tab > Sytles > Conditional formatting
  • Conditional formatting dialog box will appear. Click on New Rule button.
  • Select use a formula to determine which cells to format.
  • Enter the formula (if cell A1 is selected then A1="")
  • Click on Format > Goto Fill and select the color you want.

enter image description here

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

If you care if the cell is truly empty, you need to use the IsEmpty function on the Value property. This will return false for cells with single apostrophes or functions that return an empty string.

Public Function CellsAreEmpty(ParamArray aCells() As Variant) As Boolean

    Dim vItm As Variant
    Dim bReturn As Boolean

    bReturn = True

    For Each vItm In aCells
        bReturn = bReturn And IsEmpty(vItm.Value)
    Next vItm

    CellsAreEmpty = bReturn

End Function


Sub TestCells()

    If CellsAreEmpty(Range("A1"), Range("B7"), Range("C9")) Then
        Debug.Print "Do stuff"
    Else
        Debug.Print " is so so so cell is empty click ok to fill it"
    End If

End Sub

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Sub tgr()

    Dim CheckCell As Range

    For Each CheckCell In Sheets("Sheet1").Range("A1,B7,C9").Cells
        If Len(Trim(CheckCell.Value)) = 0 Then
            CheckCell.Select
            MsgBox "Cell " & CheckCell.Address(0, 0) & " is empty. Click OK and populate it.", , "Missing Information"
            Exit Sub
        End If
    Next CheckCell

    'All cells filled, code to Do My Stuff goes here
    MsgBox "Do my stuff", , "Continue macro"

End Sub

Upvotes: 1

Santosh
Santosh

Reputation: 12353

Is it what you are looking for ?

Below code will check for sheet1- Range A1, B7, c9 .

Sub checkEmptyCells()

    With ThisWorkbook.Sheets("sheet1")
        If (Len(.Range("A1")) = 0) Then
            MsgBox "Cell A1 is empty. Click ok to fill"
            Exit Sub
        ElseIf (Len(.Range("B7")) = 0) Then
            MsgBox "Cell B7 is empty. Click ok to fill"
            Exit Sub
        ElseIf (Len(.Range("C9")) = 0) Then
            MsgBox "Cell C9 is empty. Click ok to fill"
            Exit Sub
        Else
            MsgBox "Do my stuff"
            Exit Sub
        End If

    End With

End Sub

Upvotes: 0

Related Questions