VBA code and data validation

[uivandal]

In cell A1 I have data validation that only allows a list of 1, 2, or 3. In cell D1, I have data validation that only allows a list of A, B, or C. I need to keep a user from selecting 1 in cell A1 and C in cell D1. I cannot figure out how to keep a user from selecting this combination. Is there VBA that can be written to have a MsgBox appear when a user attempts to select this combination?


Thank you for your time in assisting me.

 

[SirJB7]

Hi, uivandal!


In the VBA code pane of related sheet type this code:


-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range("A1"), Target)) Is Nothing Or _
Not (Application.Intersect(Range("D1"), Target)) Is Nothing Then
If Range("A1").Value = 1 And Range("D1").Value = "C" Then
MsgBox "Error. Invalid combination for A1 & D1 cells.", vbCritical, "Warning"
End If
End If
End Sub

[/pre]
-----


Regards!

 

[uivandal]

I am getting a runtime 424 error message and when I click to debug it is higlighting these rows:


If Not (Application.Intersect(Range("A1"), Target)) Is Nothing Or _

Not (Application.Intersect(Range("D1"), Target)) Is Nothing Then

 

[NARAYANK991]

Hi ,


Change it to :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Or _
Not Application.Intersect(Range("D1"), Target) Is Nothing Then
If Range("A1").Value = 1 And Range("D1").Value = "C" Then
MsgBox "Error. Invalid combination for A1 & D1 cells.", vbCritical, "Warning"
End If
End If
End Sub

[/pre]

The brackets have been removed.


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Thanks for the correction.

Regards!

 

[uivandal]

That worked! Thank you both, NARAYANK991 and SirJB7, for your assistance

 

[SirJB7]

Hi, uivandal!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!