Handling Worksheet_Change for a Named Value

I was recently thinking about an example of handling the Worksheet_Change event. Unlike, say, opening a Workbook or clicking an ‘OK’ button, this is a ‘background’ event, which happens many times during an editing session. So we need to be quite specific about which changes we’re interested in. An obvious test is to check whether the name of the target Range is relevant.

An example that springs to mind is keeping a log of changes to some key value: something like a tax rate, where the act of changing the value is itself of interest. It is also important that transaction records pick up the appropriate rate for their date, not the latest value.

Here is a simple Orders table:

The various Orders pick up their tax rate from a Rate table:

The Rate column in the Orders table has the formula:

=VLOOKUP([Date],RateTable[#Data],2,TRUE)

When the Tax value (top-right of the first screenshot) is edited, a new row is added to the Rate table.

We can do this by handling Worksheet_Change on the Orders Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'If Target is the named Range "Tax",
'then extend the change log
'on Worksheet "Tax Rates",
'with today's date and the new value.

The easiest (only?) way of checking the Range Name is to have a go, and handle the error if it is unnamed:

    On Error GoTo finish
            'If Target does not have a Name,
            'then Target.Name raises an error
            'So just ignore the event.
    Dim rngname As String   'Possible name of Target
    rngname = Target.Name.Name

If Target does have a name, and that name is ‘Tax’, then add a row to the Rate table:

    If rngname = "Tax" Then
        Dim newrow As ListRow

        Set newrow = _
        Worksheets("Tax Rates").ListObjects(1).ListRows.Add

        newrow.Range.Item(1).Value = Date
        newrow.Range.Item(2).Value = Target.Value

    End If
finish:
End Sub

The use of the Date function ensures that the first column of the Rate table is sorted ascending, which is required by the VLOOKUP. If you make more than one change of Rate on a given date (for example, you correct a mistake), the VLOOKUP picks up the latest value. So if today (24/6) I make a further change from 20% to 21% (I blame the fiscal squeeze), then this rate is picked up by today’s orders.

Advertisements

2 Responses to “Handling Worksheet_Change for a Named Value”


  1. 1 sam June 27, 2010 at 3:28 pm

    Whats wrong with

    If Target.Address = Range(“Tax”).address then
    ….

  2. 2 Roy MacLean June 28, 2010 at 8:50 am

    I assume that’s a rhetorical question 🙂
    Yes, that probably is better.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




June 2010
M T W T F S S
« May   Jul »
 123456
78910111213
14151617181920
21222324252627
282930  

%d bloggers like this: