How...? If cell is not blank then error message

Posted on 2012-08-16
Last Modified: 2012-09-03
Hi Experts

I am creating a spread sheet and want to have a message box appear as well as a siutation where the user must make an entry if a certain cell is not blank.

For example
If Cell A1 has a content, the user must put an entry into B1 and if they don't a message appears telling them to do it?

Question by:heijmer
    LVL 25

    Expert Comment

    A simple (non message box) approach is a formula in column C e.g.

    =IF(A1="","",IF(B1="","<-- Enter Value Please!",""))

    If you want a message box ... are you processing the sheet via some VBA or something?

    Author Comment


    Sorry,  That doesn't work, because the cell is protected by data validation using a list, so any other entry won't work :(

    MsgBox is the preferred way :)

    not sure what you mean by
    If you want a message box ... are you processing the sheet via some VBA or something?
    LVL 25

    Expert Comment

    To make a a message box ... it would need to be done in VBA, I was wondering if you were already using VBA across the spreadsheet.  Code would be something like:
    If Range("A2").Value <> "" And Range("B2").Value = "" Then
        MsgBox "Value is missing"
    End If

    Open in new window


    Author Comment

    that didn't work :(
    LVL 25

    Expert Comment

    Where did you put it and how was it run?
    LVL 18

    Accepted Solution


    Put this code in Workbook module. Double click on ThisWorkbook and paste the code there.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ValueCount1     As Long
        Dim ValueCount2     As Long
        Const ValidationSheet As String = "Sheet1"      'adjust the sheet name
        Const ValidationRange As String = "A:B"         'adjust the range
        With Me.Worksheets(ValidationSheet)
            If Not Intersect(.UsedRange, .Range(ValidationRange)) Is Nothing Then
                ValueCount1 = WorksheetFunction.CountA(.Range(ValidationRange).Columns(1))
                ValueCount2 = WorksheetFunction.CountA(.Range(ValidationRange).Columns(2))
                If ValueCount2 < ValueCount1 Then
                    MsgBox "Value is missing", vbCritical
                    Application.Goto .Range(ValidationRange).Columns(2).SpecialCells(4).Cells(1)
                    Cancel = True
                End If
            End If
        End With
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now