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?

 

[Michael M]

You talking about a Data Validation list Dave ??

Sub MM1()
If cell(1, 1).Value = "YOUR CRITERIA" Then
MsgBox "you picked a name"
End If
End Sub

 

[dpaton05]

Yes

 

[Michael M]

Ok, then the code I supplied will do the trick !

 

[dpaton05]

Where do I put the code?

 

[Michael M]

How do you want it activated ??
Is it going to part of a bigger code ??
You can call it from another macro, you can have it in a worksheet_change event, you can run it from a button !!

If you call it or run it from a button, put it in a regular module...otherwise in the worksheet.

 

[dpaton05]

I am not sure how it will be activated. My supervisor wants the spreadsheet to be less cluttered and he suggested moving the text about changing the activity cost to a message box upon selecting activities, however, I am open to suggestions if you can think of a better way to do it.

 

[Michael M]

MAte....it would help if you provided all the info ??

Which sheet ?? and where on that sheet are we talking about ?
Remember though, it's probably more work to have a MsgBox pop up and then have to cancel or close it !!
you supervisor isn't named Peter is it .....??? I had one exactly the same....he didn't have a clue about VBA, except it must be simple to write !!!

 

[dpaton05]

Sorry I didn't provide all the info and no, my supervisor's name isn't peter. Lol, these supervisors.

• I will be on NPSS_quote_sheet.
• The first row of data in the table in this sheet will be in row 11 and the drop down list is in column B.
  • This might have activities selected from the drop down list or it may not.
• The activity column that will have the value entered in it from the message box that appears upon selecting it is in column N.

Which sheet ?? and where on that sheet are we talking about ?
Remember though, it's probably more work to have a MsgBox pop up and then have to cancel or close it !!
you supervisor isn't named Peter is it .....??? I had one exactly the same....he didn't have a clue about VBA, except it must be simple to write !!!

Not sure if you need this bit or not. It is the code that inputs the value upon the selection of 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
                    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

Thanks Michael,
Dave

 

[Michael M]

OK, found the cell....which option from that list do you want to trigger the code ??