Message box after drop down list has been used

[pgrad]

Hi All,

I am hoping to create a msg box after the user has selected from one drop down list which will instruct them to select from the next drop down list.

Is this possible? Or is there an alternative way to highlight / force them to complete all the drop down lists in a specific order?

Thanks
Paul

 

[Andrew Poulsom]

What sort of drop down list is it and where is it?

 

[pgrad]

Hi Andrew

I'm using Data Validation lists in cells b1, b2 and b3 and then finally b8,

So either want to prompt or force users to complete them in that order.

Thanks
Paul

 

[Andrew Poulsom]

It's difficult to force users to do anything. Why is the order important? You could use the Worksheet_Change event procedure to pop up a message box when each selection is made. Or you could just use some cells with formulas as prompts.

 

[pgrad]

Hi Andrew,

The order is not really important I guess, the main thing is that they are prompted to complete things and not leave blanks etc.

I am hoping to gather some data from very very low level users some of which we recently caught using typewriters! Hence the need for message to tell them exactly what to do and where to do it.

I'd never come across the worksheet_change event procedure but it looks like it will do the trick.

Thanks again
Paul

 

[Andrew Poulsom]

Bear in mind that the users must enable macros for event procedures to fire. That may be beyond their ability.

 

[pgrad]

Yes, I was thinking I would address this in a briefing session..... unless there is a clever way of forcing them to run Macros, which I doubt there is due to the security issiue it would pose.

 

[pgrad]

Hi All,

This code is working fine.... but if I want to replicate this, so that once B3 has been changed via the drop down list I can generate one final msg instucting the user to do "xyz"


Private Sub Worksheet_Change(ByVal Target As Range)
'This will generate a message box when the value of b2 is changed via the dropdown list'
Dim Rng As Range
Set Rng = Intersect(Target, [B2])
If Not Rng Is Nothing Then
MsgBox "Please select your main place of work"
End If
'This selects cell B3
Range("B3").Select
End Sub

If I simply repeat the code I get the error message " Compile Error - Ambiguous Name Detected: Worksheet_Change"

I have tried renaming the second set of code but get the same error message.

Any advice would be much appreciated.

Thanks
Paul

 

[Andrew Poulsom]

You can only have one Worksheet_Change event procedure. So you need to put all your code in there. Example:

Private Sub Worksheet_Change(ByVal Target As Range)
'   This will generate a message box when the value of b2 is changed via the dropdown list'
    Dim Rng As Range
    Set Rng = Intersect(Target, [B2])
    If Not Rng Is Nothing Then
        MsgBox "Please select your main place of work"
'       This selects cell B3
        Range("B3").Select
        Exit Sub
    End If
    Set Rng = Intersect(Target, [B3])
    If Not Rng Is Nothing Then
        MsgBox "Whatever"
'       This selects cell B4
        Range("B4").Select
        Exit Sub
    End If
End Sub

 

[pgrad]

Andrew,

Once again thanks very much!

Thats working perfectly and I would never have figured out that you cannot have multiple Worksheet_Change event procedures.

Really appreciate all your help on here!

Cheers
Paul