Excel VBA - Multiple cell update based on a change in ONE cell

Question

I want to fill a range of cell in Excel, when another one changes.

I have a macro which can retrieve customer details from another master workbook (WB2), based on the project number. Project numbers are in WB1 as a list. As and when user selects a project number from WB1, I need to fill cells in the range of H9:H15 (in WB1) with customer details. I am using worksheet_change event to trigger this.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$15" Then
        Call modInvoiceFiller.FillCustomerDetails
    End If
End Sub

With this code, no update happens. If I run the procedure manually, it fills in the required details. Could someone please help?

Regards, Prabhu

Answer 1

That works fine for me. Are you sure the macro is attached to the right worksheet rather than the workbook (or worse, in a module)?

An easy way to tell is to change it to:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Address
    If Target.Address = "$A$15" Then
        MsgBox "XX " & Target.Address
    End If
End Sub

and make sure a message pops up when you change any cell (including the value you should be checking for in your if statement), and also make sure you get two messages when you change cell A15.

Answer 2

A couple of things to check:

• Have you implemented Worksheet_Change on the Sheet that contains the project numbers?
• If you remove your modInvoiceFiller.FillCustomerDetails code and replace it with a msgbox call, does the code get executed?