VBA code to have a message box appear if when a option is selected from a drop down list

[dpaton05]

I have a cell with a drop down list in it and I want a message box to appear when a certain option is selected from the drop down list. I am pretty sure you can do it with vba but I don't know the code to do so. Can someone help me please?

 

[dpaton05]

The option that I want the message box triggered is when the user selects Activities from the drop down list.

 

[Michael M]

Your gonna need a worksheet_change event in the sheet module...something like
But I notice you already have a worksheet_change event that adresses the very same cells.
Maybe it would be a good idea to revisit this snippet of code and modify it to suit.
It's in the NPSS quoye sheet

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ans As String
        If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 1
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Value < Date Then
                    If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
                        Target.Value = ""
                    End If
                End If
            Case Is = 2
                If Target = "*Activities" Then
                    Do
                        ans = InputBox("Please enter the Activities cost.")
                        If ans <> "" Then
                            Cells(Target.Row, "N") = ans
                            Exit Do
                        Else
                            MsgBox ("You must enter a Activities cost.")
                        End If
                    Loop
                End If
        End Select
        End If
    Application.EnableEvents = True
End Sub

 

[dpaton05]

Would I need to add a line following "Case Is =2 If Target = "*Activities" Then Do", to be something like ans = MsgBox ("To change the activity cost, you must choose a different item from the list, other than" *Activities", then select "*Activities" again?

 

[Michael M]

why not simply change the dropdown list to allow for ALL activities and remove "*Activities" from the list ?
How many activities and their associated costst are there ?
If you made a list of ALL activites and their costs,You could then use a VLOOKUP to populate the cost cells !!

 

[dpaton05]

There would be an infinite number of activities. They are any misc. cost associated with the service. I can't just remove Activities as it may need to be changed. At the moment, when activities is selected, it asks for the cost and puts that in the column N. If the cost needs to be changed for a row, if you select another service then choose Activities again and it shows another input box and asks for the cost.

 

[Michael M]

It seems a little confusing...but maybe something like this....
Comment out the current Worksheet_Change event and try this one instead

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ans As String
        If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 1
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Value < Date Then
                    If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
                        Target.Value = ""
                    End If
                End If
            Case Is = 2
                If Target = "*Activities" Then
                        ans = InputBox("Please enter the Activities cost.")
                        If ans <> "" Then
                            Cells(Target.Row, "N") = ans
                        Else
                        MsgBox "To Change the activity cost, you must choose a different item from the List !!"
                        Application.EnableEvents = True
                        Exit Sub
                        End If
                End If
        End Select
        End If
    Application.EnableEvents = True
End Sub

 

[dpaton05]

I should try before I ask for help as I am starting to understand VBA a little more. I had already got it to work with this code:

All I did was add this line:

MsgBox ("To change an activity cost, change the Service to  something other than Activities the change it back to  Activities.")

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ans As String
        If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 1
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Value < Date Then
                    If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
                        Target.Value = ""
                    End If
                End If
            Case Is = 2
                If Target = "Activities" Then
                    MsgBox ("To change an activity cost, change the Service to something other than Activities the change it back to Activities.")
                    Do
                        ans = InputBox("Please enter the Activities cost.")
                        If ans <> "" Then
                            Cells(Target.Row, "N") = ans
                            Exit Do
                        Else
                            MsgBox ("You must enter a Activities cost.")
                        End If
                    Loop
                End If
            
        End Select
        End If
    Application.EnableEvents = True
End Sub

I also went through the whole spreadsheet and removed all the *Activities and replaced with Activities. I did this as there was now no need for the asterisk.

Thanks anyway Michael,
Dave

 

[Michael M]

well done....Glad it worked for you..