Results 1 to 4 of 4

VBA to check-if blank cell

This is a discussion on VBA to check-if blank cell within the Excel Questions forums, part of the Question Forums category; Hon'ble sirs, I've a protected worksheet where all cells are locked except only 4 cells. I want that the user ...

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    41

    Arrow VBA to check-if blank cell

    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.

  2. #2
    Board Regular
    Join Date
    Jan 2009
    Posts
    320

    Default Re: VBA to check-if blank cell

    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.

  3. #3
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,101

    Default Re: VBA to check-if blank cell


  4. #4
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Post Re: VBA to check-if blank cell

    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 Zack Barresse; Mar 2nd, 2012 at 01:03 PM. Reason: Added CODE tags

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com