Checking on empty (not filled) records in Excel Worksheet

Question

I have excel Worksheet where I used the following vba code, to check on empty records:

     Private Sub Workbook_BeforeClose(Cancel As Boolean)
     On Error GoTo NoBlanks
  
     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(1, Columns.Count).End(xlToLeft).Column

    Set emptyCells = sh.Range(sh.Cells(1, 1), sh.Cells(lastRow, 
                                                       lastCol)).SpecialCells(xlCellTypeBlanks)
    If emptyCells.Cells.Count > 0 Then
    MsgBox "There are empty cells, which must be filled: " & emptyCells.Address(0, 0)
    emptyCells.Select
    Else
       Resume Next
   Exit Sub
 End If

  NoBlanks:
     Resume Next

  End Sub

But it still lets me exit out of the Worksheet, even if there empty records

Are there any ways to modify this code - so it wouldn't be possible to close my Worksheet before the records are filled with values?

(I used this code on "Before Close" event)

A couple of things: firstly, you'll need a `Cancel = True` in your code to stop the Close event from happening. I don't see it anywhere in your code. Secondly, I assume you know this private sub should be attached to the WorkBook and not the individual WorkSheet it applies to in the VBA editor? — , Aug 19 '20 at 06:30
@kevin9999 yes, it should be attached toWorkBook (if to WorkSheet - then it'll work on going to another sheet, not on exit) — Hell-1931, Aug 19 '20 at 20:09

Puntal · Accepted Answer · 2020-08-19T20:27:53.507

1

Please try following code. I have modified few lines based on your requirements in comments.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
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
'lastRow = sh.UsedRange.Rows.Count

lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
'lastCol = sh.UsedRange.Columns.Count

On Error GoTo NoBlanks
Set emptyCells = sh.Range(sh.Cells(1, 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)
        emptyCells.Interior.Color = RGB(255, 0, 255)
        Cancel = True
    Else  
NoBlanks:
        Cancel = False
        sh.Range(sh.Cells(1, 1), sh.Cells(lastRow, _
 lastCol)).Interior.ColorIndex = 0
        If Me.Saved = False Then Me.Save
        'Workbook will be saved & closed if all cells in UsedRange are filled
    End If
End Sub

Regards.

edited Aug 19 '20 at 20:27

answered Aug 19 '20 at 14:26

Puntal

118
8

your code works, but it understands all non filled cells in a sheet as empty and I can't close it at all (needs to work only on the cells range in the Worksheet) You said to use UsedRange property.... Sorry, how? (not familiar w vba; all new to me) – Hell-1931 Aug 19 '20 at 20:16
May be your worksheet is having data scattered, in that case pls use your way of ````lastRow```` & ````lastCol```` in code I have mentioned. That much change must solve your concern I guess. – Puntal Aug 19 '20 at 20:22
My data arent scattered; but I still having message about empty cells - all in magenta color - everything outside my Worksheet ! – Hell-1931 Aug 19 '20 at 20:26
I have edited the code above. Pls run it & let me know your feedback. – Puntal Aug 19 '20 at 20:28
ok, now it works; but still - it changes the colors of the entire Worksheet I don't need this at all I beleive it is because of the emptyCells.Interior.Color = RGB(255, 0, 255) and sh.Cells(lastRow, _ lastCol)).Interior.ColorIndex = 0 Can I get rid of these lines? – Hell-1931 Aug 19 '20 at 20:36
What range do you get in message as empty range ? Can u pls tell me? Because code worked fine when I tried it. Also pls check if range displayed in message & that filled with color are same or different. – Puntal Aug 19 '20 at 20:39
- Now it works good, but it changes the entire colors of the sheet – Hell-1931 Aug 19 '20 at 20:40
Pls visit this interesting link on UsedaRange [https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.worksheet.usedrange?view=vsto-2017]. – Puntal Aug 19 '20 at 21:00
Your adjustments worked perfectly, finally figured But - here is another problem - inside Workbook Before Close event I have 2 more actions - manually record and then export csv file. When I try to paste this code inside "Before Close" - it's not working anymore... I'll create a separate topic for my problem – Hell-1931 Aug 20 '20 at 06:42
Share your code here on SO. By the way copying & pasting Code from ````ThisWorkbook```` to code of some worksheet wherein you want code to work...say e.g. in code window of ````Sheet1````, will help you debug the code step-by-step, albeit after some modifications in code. – Puntal Aug 20 '20 at 07:44
can I provide a link here? I just created a new post about this problem, with the full code... Otherwise - I will update my original question here (I'll wait for your response either here on within the new thread) Thank you for helping! – Hell-1931 Aug 20 '20 at 07:49
https://stackoverflow.com/questions/63500545/executing-several-actions-withing-before-close-excel-vba – Hell-1931 Aug 20 '20 at 07:49
I have replied in comment there in your new post. Pls check & let me know. Thanks – Puntal Aug 20 '20 at 08:06
Just answered there, no luck for now – Hell-1931 Aug 20 '20 at 08:16

Checking on empty (not filled) records in Excel Worksheet

1 Answers1