{ 1 Intelligent Comments }

HOWTO: Excel – Detect an Empty Cell

Microsoft Excel Logo

Check For An Empty Cell in Microsoft Excel

This HOWTO explains how you can detect an empty cell and provide an error message if the cell is empty in a Microsoft Excel worksheet.

It should work for all versions of Excel.

The cell we are checking for an empty value will be checked every time a user makes a change to the cell.

Download a sample excel file here

In this example, that cell will be cell C5.

Use Microsoft Excel’s VBA to detect the empty cell

  1. Open the worksheet you want to detect the empty cell in.
  2. Take a note of the cell you want to check for an empty value. e.g. C5 in this example
  3. Open the the Visual Basic Editor by clicking Tools > Macro > Visual Basic Editor or press ALT+F11 on the keyboard
  4. On the left pane, right click the sheet that contains the cell you want to check and click ‘View Code
  5. On the right pane, enter or copy/paste the following code:
  6. Private Sub Worksheet_Change(ByVal Target As Range)

     

    If IsEmpty(Cells(5, 3)) Then MsgBox (“Empty Cell – Please ensure there is a value in all grey cells.  Thanks”)

     

    End If

     

    End Sub

  7. The above code will check cell C3 on my worksheet which is highlighted in grey in the screen shot below.
  8.  

    Excel Worksheet Example:  Check grey cell for an empty value

  9. Change the your code as follows:
    • Enter your cell number in place of the Cells(5,3) reference.
      • For example, Cells(5,3) = Cell C3.  The 5 is the row number and the 3 is the column number.
    • For MsgBox, enter your own custom message between the quotes.
  10. Close the Visual Basic Editor by clicking the X in the top right corner of the window
  11. Test your worksheet.  It should look like the below screenshot after you attempt to clear the value from cell C3.

 

Microsoft Excel:  Sample error message after detecting an empty cell

Discussion


One Lonely Comment. Go on, have a debate or say something nice.

  1. [...] This download is a Microsoft Excel workbook containing sample Visual Basic for Applications (VBA) code that was mentioned in the HOWTO article – HOWTO: Excel – Detect an Empty Cell [...]

Leave a Reply