Pop up message if 2 criteria selected. Then go to different sheet when click OK

[white84]

I have a Data Validation - List with 15+ options in the data set. I would like the same pop up message when 2 of the set are chosen. Ideally, upon clicking "OK" on the pop up message I would like it to take me to a different tab within the workbook. Would this require a Macro? Or be done within the VBA code? I have yet to create my first macro.

Cell criteria: "737NGCB" or "737MAX"
Message: Please chose part number on the Parts List Tab
Go to: Parts List


Thanks in advance!

 

[white84]

An update on my issue. I have created a VBA code to have 1 pop-up, if one criteria is met. But I can't determine how to have the same message, given a 2nd criteria.

My code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range, Rg As Range
On Error Resume Next
Set Rg = Application.Intersect(Target, Range("E3:E5"))
If Not Rg Is Nothing Then
For Each xCell In Rg
If xCell.Value = "737MAX" Then
MsgBox "Please Chose NW & MW Parts on Parts List tab"
Exit Sub
End If
Next
End If
End Sub


I would like to add
If xCell.Value = "737NGCB" Then

My first thought is some type of OR statement is needed... is it that simple??


Bonus Points: Upon clicking "OK" in the pop-up box, can it go to another tab? My current workaround is an image/box with a hyperlink.

Thanks in advance for any help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("E3:E5")) Is Nothing Then Exit Sub
   If Target.Value = "737MAX" Or Target.Value = "737NGCB" Then
      MsgBox "Please Chose NW & MW Parts on Parts List tab"
      Application.Goto Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("[COLOR=#ff0000]E5[/COLOR]"), True
   End If
End Sub

Change sheet name & range in red to suit

 

[white84]

Fluff - Thank you for that update. I lost my functionality with the code you provided, but I was able to update my code with the second criteria. I still don't have the "bonus" functionality, but this is suitable for now. Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("E3:E5"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            If xCell.Value = "737MAX" Or xCell.Value = "737NGCB" Then
                MsgBox "Please Chose NW & MW Parts on Parts List tab"
                Exit Sub
            End If
        Next
    End If
End Sub

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("E3:E5"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            If xCell.Value = "737MAX" Or xCell.Value = "737NGCB" Then
                MsgBox "Please Chose NW & MW Parts on Parts List tab"
                Application.Goto Sheets("Master").Range("E5"), True
                Exit Sub
            End If
        Next
    End If
End Sub

Are you pasting multiple cells?

 

[white84]

Tada!

Thank you!!!

 

[Fluff]

Glad to help & thanks for the feedback