CODEWITHSUNDEEP
excelvba
jorden113
jorden113

Reputation: 1

Mandatory Row of cells in an Excel Table

I am trying to make a row in my Excel table mandatory before users close the document, then display a pop-up message stating "cells require input". I am running into an issue where users are still getting the pop-up message even if they have filled out all the mandatory cells.

This is a screenshot of what all I typed out. I have this in the workbook area I am typing what's in the screenshot, in the workbook area, and have it to run beforeclose.

This is the what I used below. My required fields is the row A3-O3

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(3, 1)(3, 2)(3, 3)(3, 4)(3, 5)(3, 6)(3, 7)(3, 8)(3, 9)(3, 10)(3, 11)(3, 12)(3, 13)(3, 14)(3, 15).Value = "" Then
        MsgBox "Cell(s) require input", vbInformation, "Kutools for Excel"
        Cancel = True
End If
End Sub

view of my spreadsheet

A plus would be a pop-up message letting the user know which cells are empty & for it to highlight the cells that are empty also

Upvotes: 0

Views: 111

Answers (1)

BigBen
BigBen

Reputation: 50162

Use WorksheetFunction.CountBlank:

If Worksheetfunction.CountBlank(ActiveSheet.Range("A3:O3")) > 0 Then
   MsgBox "Cell(s) require input", vbInformation
End If

Or SpecialCells(xlCellTypeBlanks):

On Error Resume Next
Dim rng As Range
Set rng = ActiveSheet.Range("A3:O3").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
   MsgBox "Cell(s) " & rng.Address(False, False) & " require input", vbInformation
End If

Note that Cells(3, 1)(3, 2)(3, 3)(3, 4)(3, 5)(3, 6)(3, 7)(3, 8)(3, 9)(3, 10)(3, 11)(3, 12)(3, 13)(3, 14)(3, 15) does not refer to A3:O3.

In the Immediate Window, put:

? Cells(3, 1)(3, 2)(3, 3)(3, 4)(3, 5)(3, 6)(3, 7)(3, 8)(3, 9)(3, 10)(3, 11)(3, 12)(3, 13)(3, 14)(3, 15).Address

The result is

$DB$31

Upvotes: 1

Related Questions