Initiating Subrotines in VBA

Hello Everyone,

I would greatly appreciate if someone would check my code and tell me why the subroutine "QTRRMS" works bu the code that calls the subroutine does not.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("QTRRevModSel")) Is Nothing Then

Call QTRRMS

End If

End Sub

Sub QTRRMS()

If Range("QTRRevModSel") = "Aggressive Model" Then
Range("Selection") = 1
ElseIf Range("QTRRevModSel") = "Standard Model" Then
Range("Selection") = 2
ElseIf Range("QTRRevModSel") = "Conservative Model" Then
Range("Selection") = 3
ElseIf Range("QTRRevModSel") = "Enter a Roll Your Own Number" Then
Range("Selection") = 4
ElseIf Range("QTRRevModSel") = "Splash a Number" Then
Range("Selection") = 5
Else
Range("Selection") = 6

End If

End Sub

Noire, Yes that is exactly correct

Romperstomper,

Please see the screen shot I have uploaded

BTW "QTRRevModSel" is located at cell "A5". It is a data validation cell.

Could you please explain what you mean by data validation cell?

To note, the Event Worksheet_Change will not fire if this cell contains a formula and the value of the formula changes only because the values of the cells it is linked to change. The event should normally fire if the cell value is manually changed in the worksheet, or changed through VBA.

UnderscoreBadger,

I am not sure that I understand your comment above. I have other subs that are fired off by a Data Validation cell. In fact there are other examples on the web such as
http://stackoverflow.com/questions/1...tion-drop-down

My drop down menu is located in "A5" and it consists of 6 different text selections. I have done this before and it works. In this case I have not found what I did wrong. the subroutine works fine. It is just the subroutine call that is not working.
I tried setting up from a text entry in a cell and that did not work either.

Ah understood. Thanks rjgoldstein. The event should indeed trigger from a drop-down.

As romperstomper has asked, does the event fire at all? If not, then perhaps check if Application.EnableEvents is set to True. I have in the past left it switched off and scratched my head wondering why my events weren't triggering.

Thank you everyone I found the issue. It was an excel addon for my BI tool that is not functioning as expected. The data validation tool with the BI addon does not operate the same way the excel data validation does. Therefore the sub being called by the line

Private Sub Worksheet_Change(ByVal Target As Range)

was never really called. I have to develop a workaround for that issue.