VBA to check-if blank cell

[pkb_pkb]

Hon'ble sirs,
I've a protected worksheet where all cells are locked except only 4 cells. I want that the user has to fill up all 4 cells else print out should not execute. Otherwise speaking, before Print is clicked, we should check if those 4 cells are kept blank. I want to highlight the cells those are kept blank. How can I write the code? Please guide me. Thanks.

[gecs]

First you have to create a procedure to trap the print event and within this procedure to write the code checking your condition, displaying the warning message, if necessary and cancel printing if the condition is not met.

In the ThisWorkbook section of your project you may use a code similar to the following one:

Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim r As Range
    Set r = ThisWorkbook.Sheets(1).Range("A1:A4")
    For Each cell In r
        If IsEmpty(cell) Then
            MsgBox ("Fill all required cells")
            Cancel = True
            Exit For
        End If
    Next
End Sub

Obviously you have to change the sheet index and the "A1:A4" range in the code above to point to the sheet and cells you want to be tested and you may also find a better warning message to be displayed (line MsgBox("..."))

If the cells don't form a contiguous range you have to use a cascade of IF tests for each cell like:

    If IsEmpty(ThisWorkbook.Sheets(1).Range("B1")) Then
        MsgBox ("Fill cell B1")
        Cancel = True
    Else
        If IsEmpty(ThisWorkbook.Sheets(1).Range("B3")) Then
            MsgBox ("Fill cell B3")
            Cancel = True
        Else
            If IsEmpty(ThisWorkbook.Sheets(1).Range("B5")) Then
                MsgBox ("Fill cell B5")
                Cancel = True
            End If
        End If
    End If

Hope it works.

[VoG]

See this earlier thread Help on making sure all the data is filled in

[kumarvelcbe]

Sub del()
 Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Dim objRange1 As Range
     
    Application.ScreenUpdating = True
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set wbCodeBook = ThisWorkbook
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "D:\Blank"
        .FileType = msoFileTypeExcelWorkbooks
         '.Filename = " Book*.xls"
         
        If .Execute > 0 Then 'Workbooks in folder
            
        For lCount = 1 To .FoundFiles.Count ' Loop through all.
                 'Open Workbook x and Set a Workbook  variable to it
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                'If IsEmpty(Range("A2")) Then
                    'Kill ActiveWorkbook.FullName
                    'Else: MsgBox ("File Has Data")
                'End If
                'On Error GoTo ErrorHandler
                With ActiveWorkbook
If IsEmpty(Range("A2")) Then
.Saved = True
'.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
End If
End With
wbResults.Close SaveChanges:=True
                 
            Next lCount
        End If
    End With
     
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    MsgBox ("Finished")
End Sub

Hi friends when i try to use this coding , file s not getting deleted. Pls Correct this coding.