I suspect I am running into a problem of either not understanding the limitations of Application.EnableEvents and/or a conflict with placing some of my code in the worksheet's code page instead of a module.
With the following, if I don't have EnableEvents set to false, the writes to the same page trigger the change event (which I am using each time the target cell is changed to update a group of displayed values).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCtr As Integer, intRow As Integer, intTblTopRow As Integer
Dim rngLow As Range, rngHigh As Range
Set rngLow = Worksheets("Rates").ListObjects("tblRateA").ListColumns("Low").DataBodyRange
Set rngHigh = Worksheets("Rates").ListObjects("tblRateA").ListColumns("High").DataBodyRange
intTblTopRow = Worksheets("Rates").ListObjects("tblRateA").HeaderRowRange(1, 1).Row
Application.EnableEvents = False
'Determine which row number we are working with
If Target.Address = Worksheets("Calculate").Range("nmCost").Address And Target.Value > 0 Then
For intCtr = 1 To rngLow.Row
If Target.Value >= rngLow.Cells(intCtr, 1) And Target.Value <= rngHigh.Cells(intCtr, 1) Then
intRow = intCtr
End If
Next
Worksheets("Calculate").Range("D7") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 3)
Worksheets("Calculate").Range("D8") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 4)
Worksheets("Calculate").Range("D9") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 5)
Worksheets("Calculate").Range("D10") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 6)
End If
Application.EnableEvents = True
End Sub
Unfortunately using Application.EnableEvents = False prohibits the lines
Worksheets("Calculate").Range("D7") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 3)
Worksheets("Calculate").Range("D8") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 4)
Worksheets("Calculate").Range("D9") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 5)
Worksheets("Calculate").Range("D10") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 6)
from functioning. I thought that Application.EnableEvents = False would only prevent Events from running, not prevent code from posting values!
I must be doing something fundamentally wrong here - Thoughts?
Thank-you!
Bookmarks