Checking if cells are empty on "Change" event in Excel vba

[Elena Margulis]

I have a macro enabled excel sheet - Sheet_Change.xlsm
My goal is to check if a columns A and C only - are empty or not within a table.
If cells are empty, then a message should appear and user shouldn't be able to enter a new record (row).

It is currently works on any "Change" event.
But I need it to work only after entering a row or - even better - after activating a new worksheet - after switching from sheet 1 to sheet 2 (if that's easier to achieve)
Also, it's now working for ANY empty cells, while I need it to work for the columns A & C only.

Please, see the vba code below:

Private Sub Worksheet_Change(ByVal Target As Range)

'
' Checking on Empty Cells:
'

Dim Sh As Worksheet, lastRow As Long, lastCol As Long, emptyCells As Range
Set Sh = ActiveSheet 'use here your sheet

lastRow = Sh.Range("A" & Rows.Count).End(xlUp).Row
lastCol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column


On Error GoTo NoBlanks
Set emptyCells = Sh.Range(Sh.Cells(2, 1), Sh.Cells(lastRow, lastCol)).SpecialCells(xlCellTypeBlanks)

    If Not emptyCells Is Nothing Then
        MsgBox "There are empty cells, which must be filled: " & emptyCells.Address(0, 0)
        Sh.Activate: emptyCells.Select
        Cancel = True
    Else
NoBlanks:
        Cancel = False
        If wb.Sheets("Sheet1").Saved = False Then wb.Sheets("Sheet1").Save
        'Workbook will be saved & closed if all cells in UsedRange are filled

End If
End Sub

 

[bebo021999]

" If Not emptyCells Is Nothing Then"

may be

If Not intersect(sh.range("A:A"),emptyCells) or Not intersect(sh.range("C:C"),emptyCells) Is Nothing Then

 

[Elena Margulis]

" If Not emptyCells Is Nothing Then"

may be

If Not intersect(sh.range("A:A"),emptyCells) or Not intersect(sh.range("C:C"),emptyCells) Is Nothing Then

Unfortunately it creates Runtime error 424 and points to the last row of this code:
If wb.Sheets("Sheet1").Saved = False Then wb.Sheets("Sheet1").Save

 

[bebo021999]

Try again:

If Not intersect(sh.range("A:A"),emptyCells) is nothing or Not intersect(sh.range("C:C"),emptyCells) Is Nothing Then

 

[Elena Margulis]

Try again:

If Not intersect(sh.range("A:A"),emptyCells) is nothing or Not intersect(sh.range("C:C"),emptyCells) Is Nothing Then

unfortunately, the same error as before...

 

[Raymondc190466]

I have a macro enabled excel sheet - Sheet_Change.xlsm
My goal is to check if a columns A and C only - are empty or not within a table.
If cells are empty, then a message should appear and user shouldn't be able to enter a new record (row).

It is currently works on any "Change" event.
But I need it to work only after entering a row or - even better - after activating a new worksheet - after switching from sheet 1 to sheet 2 (if that's easier to achieve)
Also, it's now working for ANY empty cells, while I need it to work for the columns A & C only.

Please, see the vba code below:

Private Sub Worksheet_Change(ByVal Target As Range)

'
' Checking on Empty Cells:
'

Dim Sh As Worksheet, lastRow As Long, lastCol As Long, emptyCells As Range
Set Sh = ActiveSheet 'use here your sheet

lastRow = Sh.Range("A" & Rows.Count).End(xlUp).Row
lastCol = Sh.Cells(2, Columns.Count).End(xlToLeft).Column


On Error GoTo NoBlanks
Set emptyCells = Sh.Range(Sh.Cells(2, 1), Sh.Cells(lastRow, lastCol)).SpecialCells(xlCellTypeBlanks)

    If Not emptyCells Is Nothing Then
        MsgBox "There are empty cells, which must be filled: " & emptyCells.Address(0, 0)
        Sh.Activate: emptyCells.Select
        Cancel = True
    Else
NoBlanks:
        Cancel = False
        If wb.Sheets("Sheet1").Saved = False Then wb.Sheets("Sheet1").Save
        'Workbook will be saved & closed if all cells in UsedRange are filled

End If
End Sub

I have this code to find first empty row (found on internet, and use it now- works for me).
Maybe you can use it?

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row