PDA

View Full Version : blank cell values



talytech
10-07-2008, 12:56 PM
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
10-07-2008, 01:11 PM
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
10-08-2008, 06:41 AM
that code works the same way as mine but it still doesn't return the message box when the cell is equal to "".

RonMcK
10-08-2008, 07:15 AM
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
10-08-2008, 08:29 AM
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