blank cell values

[talytech]

I'm having a problem getting my message box to return the value of a null cell. Basically on the "BeforeClose" event, I want to check cell B starting with row 14 to see if the cell is blank or if it has something in it, Then I want to cancel the close event. My code doesn't work when there isn't any value in the cell. It doesn't give me my message instead it just cancels the close event. What am I doing wrong? What should the value equal to if the value is blank? Here's my code:

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For a = 14 To lastrow
        
        If Cells(a,"b").value = "" then
            msgbox "nothing in cell"
        else

            MsgBox Cells(a, "b").Value
        end if
        
    Next a
    Cancel = True

[mdmackillop]

I'm maybe not fully following your logic but try

[VBA]
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long, a As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For a = 14 To LastRow
If Cells(a, "b").Value = "" Then
MsgBox "nothing in cell"
Cancel = True
Exit Sub
Else
MsgBox Cells(a, "b").Value
End If
Next a
End Sub

[/VBA]

[talytech]

that code works the same way as mine but it still doesn't return the message box when the cell is equal to "".

[RonMcK]

talytech,

If youi want to capture the case where a cell is empty or contains "" (2 quote marks), change:
[vba]If Cells(a, "B").Value = "" Then
MsgBox "nothing in cell"[/vba] to
[vba] If Cells(a, "B").Value = "" Or Cells(a, "B").Value = """" Then
MsgBox "nothing in cell: " & "B" & Str(a)[/vba]
I added the cell id to the message so you know which cell needs to be fixed.

Cheers,

[Kenneth Hobs]

You may want to set the sheetname.

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, r As Range, rc As Boolean
Dim s As Worksheet
rc = False
Set s = Worksheets("Sheet1")
Set r = s.Range("B14", s.Cells(Rows.Count, "B").End(xlUp))
For Each c In r
If c.Value = "" Then
MsgBox "Nothing in cell: " & s.Name & "!" & c.Address
Cancel = True
rc = True
Else
MsgBox c.Value
End If
Next c
Cancel = rc
End Sub[/VBA]