Trigger change event with data validation

gschwint · May 4, 2005

I am trying to trigger a worksheet change event when changes are made to a in-cell drop down list created via data validation. The change event works okay when the entry is typed into the cell however when it is selected from a list it does not work. Below is the code that I want to run when a cell in either column J or L of the target row is changed.

Code:

    If Range("W" & target.Row).Value <> "" And Range("A" & target.Row).Value <> "" Then

            If Application.WorksheetFunction.IsError(Range("AG" & target.Row)) = False Then

            If Range("AH" & target.Row).Value > (Range("Q" & target.Row).Value _
                + Range("Q" & target.Row).Value * 0.2) And _
                Range("W" & target.Row).Interior.ColorIndex <> 6 Then
                ActiveSheet.Unprotect
                Range("W" & target.Row).Activate
                    With ActiveCell.Interior
                        .ColorIndex = 6

                    End With
                ActiveSheet.Protect
                Response = MsgBox(prompt:="The application rate for this field provides significantly more Nitrogen than recommended by the University of Minnesota. " & vbNewLine & vbNewLine & "This application rate provides for a total of " & Range("AH" & target.Row).Value & " lbs of Nitrogen when combined with all nitrogen sources." & vbNewLine & "The University of Minnesota recommends " & Range("Q" & target.Row).Value & " lbs of Nitrogen for this crop after accounting for all" & vbNewLine & "Nitrogen Credits from previous crops." & vbNewLine & vbNewLine & "Do you still plan to apply manure at this rate for this field?", Buttons:=vbYesNo, Title:="Application Rate Provides more Nitrogen than Recommended")
                    If Response = vbNo Then
                        MsgBox prompt:="Please adjust the manure application rate highlighted in yellow." & vbNewLine & vbNewLine & "The cell will continue to be highlighted with yellow until an applicaiton rate that complies with the University of Minnesota recommendations is entered.", Buttons:=vbOKOnly, Title:="Correct the Application Rate"
                    End If
                    If Response = vbYes Then
                        MsgBox prompt:="The application rate identified for this field exceeds the 20% variation from the University of Minnesota recommendations allowed by Minnesota Feedlot Rules if site nutrient management history, soil conditions, or cool weather warrant additional nitrogen application." & vbNewLine & vbNewLine & " An allowable deviation from University of Minnesota recommendations is the use of the most recent nitrogen recommendation publication of a land grant college in a contiguous state.  The Minnesota Feedlot Rules also allow for deviations when crop nitrogen deficiencies are visible or measured." & vbNewLine & vbNewLine & "If any of the above are the reasons for exceeding the University of Minnesota recommendations by more than 20% the reason must be documented and have a written explanation included with the Manure Management Plan.", Buttons:=vbOKOnly, Title:="Allowable Deviations from University of Minnesota Recommendations"
                    End If
             End If
             If Range("AH" & target.Row).Value <= (Range("Q" & target.Row).Value + Range("Q" & target.Row).Value * 0.2) Then
                ActiveSheet.Unprotect
                    With Range("W" & target.Row, "X" & target.Row).Interior
                    .ColorIndex = 0

                    End With
                ActiveSheet.Protect
             End If
             End If
    End If

Any suggestions?

Von Pookie · May 4, 2005

Maybe try a Worksheet_SelectionChange macro instead?

When you make a selection from a data validation dropdown list, it selects the cell as well--you would just need to check for the target column to run the code.

Something like this, maybe? I had some time to kill and added some variables to the code to make it a bit more readable (at least to me)--other than one line being added, this is the same code you wrote.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Response As Variant
Dim prompt1 As String, title1 As String
Dim prompt2 As String, title2 As String
Dim prompt3 As String, title3 As String
Dim rngAH As Range, rngQ As Range, rngW As Range
Dim rngA As Range, rngX As Range, rngAG As Range

'if selected cell is not in column J or L, code should end here
If Target.Column = 10 Or Target.Column = 12 Then
 Set rngAH = Cells(Target.Row, "AH")
 Set rngQ = Cells(Target.Row, "Q")
 Set rngW = Cells(Target.Row, "W")
 Set rngA = Cells(Target.Row, "A")
 Set rngX = Cells(Target.Row, "X")
 Set rngAG = Cells(Target.Row, "AG")
 prompt1 = "The application rate for this field provides significantly " _
 & "more Nitrogen than recommended by the University of " _
 & "Minnesota. " & vbNewLine & vbNewLine & "This application rate " _
 & "provides for a total of " & rngAH & " lbs of Nitrogen when " _
 & "combined with all nitrogen sources." & vbNewLine _
 & "The University of Minnesota recommends " & rngQ & " lbs of " _
 & "Nitrogen for this crop after accounting for all" & vbNewLine _
 & "Nitrogen Credits from previous crops." & vbNewLine _
 & vbNewLine & "Do you still plan to apply manure at this " _
 & "rate for this field?"
 title1 = "Application Rate Provides more Nitrogen than Recommended"
 prompt2 = "Please adjust the manure application rate highlighted " _
 & "in yellow." & vbNewLine & vbNewLine & "The cell will " _
 & "continue to be highlighted with yellow until an applicaiton " _
 & "rate that complies with the University of Minnesota " _
 & "recommendations is entered."
 title2 = "Correct the Application Rate"
 prompt3 = "The application rate identified for this field exceeds " _
 & "the 20% variation from the University of Minnesota " _
 & "recommendations allowed by Minnesota Feedlot Rules if site " _
 & "nutrient management history, soil conditions, or cool " _
 & "weather warrant additional nitrogen application." & vbNewLine _
 & vbNewLine & " An allowable deviation from University of " _
 & "Minnesota recommendations is the use of the most recent " _
 & "nitrogen recommendation publication of a land grant college " _
 & "in a contiguous state. The Minnesota Feedlot Rules also " _
 & "allow for deviations when crop nitrogen deficiencies are " _
 & "visible or measured." & vbNewLine & vbNewLine & "If any of " _
 & "the above are the reasons for exceeding the University of " _
 & "Minnesota recommendations by more than 20% the reason " _
 & "must be documented and have a written explanation " _
 & "included with the Manure Management Plan."
 title3 = "Allowable Deviations from University of Minnesota Recommendations"

 If rngW <> "" And rngA <> "" And _
 Application.WorksheetFunction.IsError(rngAG) = False And _
 rngAH > rngQ + (rngQ * 0.2) And rngW.Interior.ColorIndex <> 6 Then
 ActiveSheet.Unprotect
 rngW.ColorIndex = 6
 ActiveSheet.Protect
 Response = MsgBox(prompt1, vbYesNo, title1)
 If Response = vbNo Then
 MsgBox prompt2, title2
 Else
 MsgBox prompt3, title3
 End If
 End If
 If rngAH <= (rngQ + rngQ * 0.2) Then
 ActiveSheet.Unprotect
 Union(rngW, rngX).Interior.ColorIndex = 0
 ActiveSheet.Protect
 End If
End If
End Sub

gschwint · May 4, 2005

That did not work for me either.

Thanks for the cleaned up code though, all the code that I know I have learned via this board, so it is mostly things I've seen on other posts and things that I have done with the help info and trial and error.

Any other ides on how to make this work?

Von Pookie · May 4, 2005

Hmm. You could possibly have enable events set to false.

In the Visual Basic Editor, press Ctrl+G. This should open a little area called the Immediate window. In that area, enter

Code:

Application.EnableEvents=True

and press enter.

After that, if you select a cell in column J or L does the code run?

gschwint · May 16, 2005

That did not work for me either. I guess it isn't that big of a problem because as soon as the user choses a different cell the code executes and with my worksheet the likelyhood that someone would exit the sheet without choosing another cell is very unlikely so the code should always execute.

Thanks for the ideas it is really just more of a little quirk I wanted fixed. I'd be open to other ideas if something else may work?

George

Von Pookie · May 16, 2005

Well, at first you were saying if a cell in columns J and L were selected to run the code. That is what I wrote that for.

If you want the code to run every time no matter what cell on the sheet is selected, remove

Code:

If Target.Column = 10 Or Target.Column = 12 Then

and the final "End If" from the code, then give it another go.

Edit: However, having the code run (and possibly seeing message boxes) every single time you click on a cell could get old *very* quickly.

gschwint · May 18, 2005

I have discovered something that I think is related to this issue I am having. I put this post up today, and I think that this is also causing the problem we have been discussing. If you would be so kind as to take a look at the post as see what you tkink, I'd appreciate it!

http://www.mrexcel.com/board2/viewtopic.php?p=707972#707972

Thanks

George

tusharm · Dec 29, 2005

IIRC, some version of XL has a bug in which the change event is not fired when a value is selected from a validation list. I just tested XL2003 and the event does fire. If you haven't updated your version with all the available service packs you may want to do so. Of course, you accept responsibility for any other issues that might arise from doing so.

gschwint said:
I am trying to trigger a worksheet change event when changes are made to a in-cell drop down list created via data validation. The change event works okay when the entry is typed into the cell however when it is selected from a list it does not work. {snip}

Any suggestions?

Trigger change event with data validation

gschwint

Board Regular

Excel Facts

Von Pookie

MrExcel MVP

gschwint

Board Regular

Von Pookie

MrExcel MVP

gschwint

Board Regular

Von Pookie

MrExcel MVP

gschwint

Board Regular

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

Trigger change event with data validation

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock