VBA to check-if blank cell

pkb_pkb

New Member
Joined
Jan 11, 2009
Messages
41
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:
Code:
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:
Code:
    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.
 
Upvote 0
Code:
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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top