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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.