create a validation error pop-up message when cell left empty

Megat91

New Member
Joined
Jul 9, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Hi,

i got a template to do for my staff. it contain 3 column, section (a), barcode (b) & quantity (c).
how do i create a error pop-up message if cell in column (b) was filled up, but column (c) left blank. because they tend to sometime forget to key column (c). help me! thanks a lot in advance!

can refer attached picture, i want to avoid the "green" color cell to left empty before they can proceed to key next cell (b4,c4)
 

Attachments

  • Excel Problem 1.JPG
    Excel Problem 1.JPG
    28.8 KB · Views: 1

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,239
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 2
            If Target.Offset(, 1) = "" Then
                Target.Offset(, 1).Select
                MsgBox ("Please enter a quantity for barcode " & Target.Value & ".")
            End If
        Case Is = 3
            If Target.Offset(, -1) = "" Then
                Target.Offset(, -1).Select
                MsgBox ("Please enter a barcode.")
            End If
    End Select
End Sub
 

Megat91

New Member
Joined
Jul 9, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 2
            If Target.Offset(, 1) = "" Then
                Target.Offset(, 1).Select
                MsgBox ("Please enter a quantity for barcode " & Target.Value & ".")
            End If
        Case Is = 3
            If Target.Offset(, -1) = "" Then
                Target.Offset(, -1).Select
                MsgBox ("Please enter a barcode.")
            End If
    End Select
End Sub

above code work well, but it appear to popup right after column (b2) were filled by barcode.
but can the popup msg appear if person move down and click the (b3)? if they move to c2, no popup appear.
if not everytime column (b) was fill, the popup come out. it will slow down the process to key
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,239
Try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target.Offset(-1) <> "" And Target.Offset(-1, 1) = "" Then
        Target.ClearContents
        Target.Offset(-1, 1).Select
        MsgBox ("Please enter a quantity for barcode " & Target.Offset(-1) & ".")
    End If
    Application.EnableEvents = True
End Sub
 

Megat91

New Member
Joined
Jul 9, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target.Offset(-1) <> "" And Target.Offset(-1, 1) = "" Then
        Target.ClearContents
        Target.Offset(-1, 1).Select
        MsgBox ("Please enter a quantity for barcode " & Target.Offset(-1) & ".")
    End If
    Application.EnableEvents = True
End Sub

Hi, is there any other way than doing macro? any normal formula?. As the macro doesnt run on andriod excel version, hence when my staff use smartphone, all the popup not work
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,157
Members
416,368
Latest member
PaoloC

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