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


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

[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:
If Cells(a, "B").Value = "" Then
MsgBox "nothing in cell" to
If Cells(a, "B").Value = "" Or Cells(a, "B").Value = """" Then
MsgBox "nothing in cell: " & "B" & Str(a)
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.

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