Dialog Box when different cells are selected

[buzz_uk]

Hi Mr Excel and hope you can help me please?

As a relatively newcomer to VBA, but being quite good with excel, I need to create a form that requires a series of Yes/No inputs (Yes/No drop downs)

Based on some of these inputs I need a dialog box to pop up giving guidance to the user of the required step if Yes is selected.

So, I have done this for when a single cell is selected, which works well –

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row = 7 Then
If Target.Value = "Yes" Then
MsgBox "You must contact a Health & Safety Representative before proceeding."
End If
End If
End Sub

I have done this when the top 6 question boxes require a pop up box using a range when Yes is selected

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, r As Range
Set A = Range("D7:D16")
If Intersect(Target, A) Is Nothing Then Exit Sub
For Each r In Target
If r.Value = "Yes" Then
MsgBox "You must contact a Health and Safety Representative before proceeding.", vbExclamation, " New Sourcing Request"
End If
Next r
End Sub

BUT - I need a pop up to appear when other cells, not in a range, where Yes has been selected for example D16, E24, C12 on a single worksheet for which different Messages are required

Can you help please?

THANK YOU

 

[wideboydixon]

Re: Dialog Box when different cells are selected - Help please

Union might be your friend here:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim A As Range, r As Range

Set A = Intersect(Union(Range("D7:D16"), Range("E24"), Range("C12")), Target)

If A Is Nothing Then Exit Sub

For Each r In A
    If r.Value = "Yes" Then
        MsgBox "You must contact a Health and Safety Representative before proceeding.", vbExclamation, " New Sourcing Request"
    End If
Next r

End Sub

WBD

 

[buzz_uk]

Re: Dialog Box when different cells are selected - Help please

Many thanks wideboydixon

This works for a single message ie D7:D16 I want to display MsgBox "You must contact a Health and Safety Representative before proceeding." BUT E24 I want to display a different message

Thank you very much for taking time out to help me

 

[wideboydixon]

Re: Dialog Box when different cells are selected - Help please

You can use a Select statement to pick the right message:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim A As Range, r As Range
Dim m As String

Set A = Intersect(Union(Range("D7:D16"), Range("E24"), Range("C12")), Target)

If A Is Nothing Then Exit Sub

For Each r In A
    If r.Value = "Yes" Then
        Select Case r.Address
            Case "$E$24"
                m = "Message for E24"
            Case "$C$12"
                m = "Message for C12"
            Case Else
                m = "You must contact a Health and Safety Representative before proceeding."
        End Select
        MsgBox m, vbExclamation, "New Sourcing Request"
    End If
Next r

End Sub

WBD

 

[buzz_uk]

Re: Dialog Box when different cells are selected - Help please

Many thanks again WBD - I do appreciate it

Unfortunately I am still getting the same message box for all cells selected. The "You must contact a Health and Safety Representative before proceeding" message box

Thanks for the structure and I'll try and use this as a base to see if I can get it going, unless there is a simple change?

Thanks for your time, I do appreciate your help

 

[buzz_uk]

Re: Dialog Box when different cells are selected - Help please

WBD - You are a genius - thank you very much indeed

There was nothing wrong with your code, unfortunately user error (when I amended a cell reference it was in lower case)

thanks again and I have learned a lot from this

Buzz