VBA Modification

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
336
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I need help with this code that I have put together:

Private Sub worksheet_selectionchange(ByVal target As Range)
Static LastCell As Range

If Not LastCell Is Nothing Then
If LastCell.Column = 16 Then
If Not IsEmpty(LastCell.Offset(0, -1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank.")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

If Not LastCell Is Nothing Then
If LastCell.Column = 15 Then
If Not IsEmpty(LastCell.Offset(0, 1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

End Sub

What I am trying to achieve is a pop-up if a cell in column O is blank but the cell in column P is not blank and vice versa.

It works when cell in column O is not blank and you try to leave cell in column P blank but it let's you leave cell in column O blank and populate cell in column P and go to the next row unless you go back to the cell in column O afterwards!

Or if you know of a better way to code this, please let me know.

Thanks.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I believe you should be using:
Private Sub Worksheet_Change(ByVal Target As Range)
Which activates when you change a value in the sheet.
Your using a script that runs when just move from one cell to another
 
Upvote 0
I tired this but still the same issue:

Private Sub Worksheet_Change(ByVal Target As Range)

Static LastCell As Range

If Not LastCell Is Nothing Then
If LastCell.Column = 16 Then
If Not IsEmpty(LastCell.Offset(0, -1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank.")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

If Not LastCell Is Nothing Then
If LastCell.Column = 15 Then
If Not IsEmpty(LastCell.Offset(0, 1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

End Sub
 
Upvote 0
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Script will run if you enter a value in column O or column P
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/27/2020  9:27:17 PM  EST
If Target.Column = 15 Or Target.Column = 16 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 15 And Target.Offset(, 1).Value = "" Then MsgBox "Fill in column 16"
If Target.Column = 16 And Target.Offset(, -1).Value = "" Then MsgBox "Fill in column 15"
End If
End Sub
 
Upvote 0
Thank you very much.

It is working fine.

I am trying to add a new condition to it but am not savvy enough!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Or Target.Column = 8 Or Target.Column = 4 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 4 And Target.Offset(, 0).Left(Value.text, 1) = "4" Then MsgBox "Fill in column H"
If Target.Column = 8 And Target.Offset(, 1).Value = "" Then MsgBox "Fill in column name"
If Target.Column = 9 And Target.Offset(, -1).Value = "" Then MsgBox "Fill in column num"
End If
End Sub

Does it make sense? I am trying to say if Left value in column D = "4" then pop up a message?
Or would this be a different code altogether?

Thank you.
 
Upvote 0
Why was it you originally want to work with column O and P and now you want to work with some other columns?
 
Upvote 0
Ok, thank you.

I am trying to have pop-ups if the value in column D starts with 4 to alert user to enter a value in column O and P.
Also, column O cannot be blank if column P is populated and vice versa.

Thanks again.
 
Upvote 0
You post number five has nothing to do with columns O and P as far as I can see.
Column 9 and 8 are not O and P
 
Upvote 0
If Target.Column = 4 And Target.Offset(, 0).Left(Value.text, 1) = "4" Then MsgBox "Fill in column H"
If this is why you wish to consider columns H and I instead of O and P then try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim s   As String

    With Target
        If .CountLarge > 1 Or .Value = "" Then Exit Sub
        If .Column = 4 Then If Left$(.Offset(, 1).Value, 1) + 0 = 4 Then s = "Don't forget to fill column H"
        If .Column = 8 And Len(.Value & .Offset(, 1).Value) = Len(.Value) Then s = "Missing column name"
        If .Column = 9 And Len(.Value & .Offset(, -1).Value) = Len(.Value) Then s = "Missing column number"
    End With
  
    If s <> "" Then MsgBox s, vbInformation, "Enter missing details"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top