Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2012-08-16
Medium Priority
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
  • 3
  • 2
LVL 25

Expert Comment

ID: 38299633
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

ID: 38299647

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

ID: 38299659
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 38299763
that didn't work :(
LVL 25

Expert Comment

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

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38300106

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


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I came across an unsolved Outlook issue and here is my solution.
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

564 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