0

I am still quite new to VBA and have run into an issue with my msgbox function. Essentially I am selecting a name from a drop down list, If a certain name is selected a message box should pop up. For some reason it does not want to fire even when the condition is true. EDIT: new Code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Selected_PC")) Is Nothing Then

    If Range("Selected_PC").Value = "S&S - Marine" Then
        MsgBox "Please be aware, for this profit center the template will not produce a result for the Underwriter assignment. If an Underwriter sent in the submission refer to that Underwriter."

    End If
End If
End Sub

Code placement was my issue. I originally was attempting to add the code to my module where I created my user defined function for "Selected_PC". I moved the code to the worksheet in which the target cell is located. This seemed to do the trick. Rookie move by calling a worksheet change in a module...live and learn I guess. Thank you all for the help!

Martin Wickman
  • 19,662
  • 12
  • 82
  • 106
Johnny Hammersticks
  • 1
  • 2
  • 2
    Do you execute this macro everytime you select a name from that drop down list? – Damian Oct 15 '19 at 15:05
  • So you have a single-cell range named "Selected_PC" which contains "S&S - Marine" ? That works for me. But your use of "firing" suggests this code should somehow be automatically triggered by something. – SJR Oct 15 '19 at 15:06
  • 1
    Are you calling this macro in a `change_event`? – Cyril Oct 15 '19 at 15:07
  • Damian to answer your question, I do not execute this macro every time I select a name from the drop down. I am attempting to have it execute only when this particular name is selected. – Johnny Hammersticks Oct 15 '19 at 15:09
  • SJR, yes you are right, when the option of S&S - Marine is selected it should trigger the msgbox to pop up – Johnny Hammersticks Oct 15 '19 at 15:10
  • @JohnnyHammersticks you will need to create a `change_event` that triggers when your value change in that cell, or the collection of cells you are assessing. Otherwise, you must manually run this macro every time. – Cyril Oct 15 '19 at 15:10
  • Cyril, I do not believe I am calling it in a change event. How would I go about figuring our if I am or not? sorry, still very new. – Johnny Hammersticks Oct 15 '19 at 15:12
  • cyril, I will take a look at this thread and attempt to add in a change_event. Will report back! Thank you all for the quick help – Johnny Hammersticks Oct 15 '19 at 15:15
  • @JohnnyHammersticks put the code from the link posted by Cyril in the worksheet module where you have that drop down list. If your sheet is called "MySheet" go to "MySheet" on your vba project, then on the left drop down list pick `Worksheet` and on the second one pick `Worksheet_Change` inside it put the code from the link and change H5 for your named range. – Damian Oct 15 '19 at 15:15
  • 1
    @JohnnyHammersticks please use the "@" symbol to tag people so they get alerted that you are responding to them. I happened to reload this post to see your response to me... please research `worksheet_change()` events when the value changes in a cell. that will allow you to write minimal code towards that. [Link about change events](https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) – Cyril Oct 15 '19 at 15:15
  • @Damian would it look something like this? `Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("Selected_PC")) Is Nothing Then If Range("Selected_PC").Value = "S&S - Marine" Then MsgBox ("Please be aware, for this profit center the template will not produce a result for the Underwriter assignment. If an Underwriter sent in the submission refer to that Underwriter.") End If End If End Sub ` Sorry this formatted so ugly. – Johnny Hammersticks Oct 15 '19 at 15:36
  • @JohnnyHammersticks Kind of, try to edit your question and throw there the code, comments are not for such things. – Damian Oct 15 '19 at 15:44
  • @Damian the new code has been added to the post. i think may be running into a placement issue. The drop down list selection is located in a module rather than a worksheet because it is a UDF. Should I place my worksheet change event within the module where the UDF is or should I put it in the sheet where the change takes place? – Johnny Hammersticks Oct 15 '19 at 15:52

0 Answers0