VBA for error message to prompt if cell is not filled when there is text inside another cell

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
83
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello all,

I am after a VBA that displays a message box to prompt the user to complete another cell dependant on the Data Validation List option, i.e. if 'Cancelled by tenant' is selected from the DVL in column H, then column AC also needs to be completed (by selecting 'Cancelled' from that DVL). I want to be able to do this for several choices from the first DVL (e.g. 'Cancelled by LA', 'Cancelled by RSL', 'Completed', 'Refused'). I want to apply this to the whole of the columns (row range 9:568). Oh, and I already have conditional formatting applied (quite a bit, so I wanted an actual message box!). I do have a DV Input Message, but some people still ignore that too!! 🤨 Hence the pop-up message box!

Not sure it's possible with everything I already have set up in this workbook, but any advice would be greatly received!

Ta muchly, folks!
Sara
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Carrying out these types of checks on the sheet could be difficult.
It is recommended to capture the data in a UserForm, validate and until the data is correct and complete, then pass the data to the sheet.
In any case, validations (DVL) or UserForm there are advantages and disadvantages.

I leave you a code for the events of your sheet.
If you capture a data in column H, check if there is data in AC, if it does not exist in AC, then select cell AC and it does not allow you to select another cell until you capture a data in AC.

VBA Code:
Option Explicit

Dim needData As String

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim msg As Boolean
  
  If Not Intersect(Target, Range("H9:H568")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Range("AC" & Target.Row).Value = "" Then msg = True
  End If
  '
  If Not Intersect(Target, Range("AC9:AC568")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then
      If Range("H" & Target.Row).Value <> "" Then msg = True
    End If
  End If
  '
  If msg Then
    MsgBox "cell is not filled"
    Application.EnableEvents = False
    Range("AC" & Target.Row).Select
    Application.EnableEvents = True
    needData = Range("AC" & Target.Row).Address
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If needData <> "" Then
    If Range(needData).Value <> "" Then
      needData = ""
      Exit Sub
    End If
    If ActiveCell.Address(0, 0) = Range(needData).Address(0, 0) Then
      Exit Sub
    End If
    MsgBox "cell is not filled"
    Application.EnableEvents = False
    Range(needData).Select
    Application.EnableEvents = True
  End If
End Sub


Note: it would be more practical to perform these validations in a UserForm.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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
Top