Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing A Blank Cell (Excel 2K/SR1)

    I have a worksheet that I would like to send to others so they can provide information to me. What would be the best approach to notify the user if all the required information is not provided? In other words, I want them to fill in all the cells from A1:A4 and if they skip a cell I want them to know it.
    Thanks,
    Caroline in lala-land

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing A Blank Cell (Excel 2K/SR1)

    Would a macro be OK?

    If so, I'd use the workbook_beforeclose event in the thisworkbook module of that workbook.

    It might look like this:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oCell As Range
    For Each oCell In Me.Worksheets("Sheet1").Range("a1:a4")
    If oCell.Value = "" Then
    MsgBox "please make sure all cells within the range A1:A4 are filled!!!"
    Cancel = True
    End If
    Next
    End Sub

    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing A Blank Cell (Excel 2K/SR1)

    One addition to Jan Karel's macro to prevent getting the message multiple times if more than one cell is empty:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oCell As Range
    For Each oCell In Me.Worksheets("Sheet1").Range("a1:a4")
    If oCell.Value = "" Then
    MsgBox "please make sure all cells within the range A1:A4 are filled!!!"
    Cancel = True
    oCell.Select
    Exit Sub
    End If
    Next
    End Sub
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing A Blank Cell (Excel 2K/SR1)

    Or, using the quick and dirty approach, why not conditional format the cells you want filled in bright red while they're blank, but 'normal' when they've been completed.
    Then your colleagues need only understand that you don't want to see anything red in their completed handiwork.

Posting Permissions

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