Need list of unique values for sumiff'ed records

**mys Scott** · 07-27-2011, 06:49 AM

Roger,
Once again thank you. This is perfect. I especially like the automatic column width adjustment to accommodate the string of invoice numbers as it expands. Nice touch.
One question (demonstrating my macro-ignorance here): Can the macro be set to run any time a value is changed in column M "Billed on MYS invoice #" on workheet "all data entry"? The other workbook macros (or are they "just" code?) I have set up to run automatically I think, so there is no need to specifically run them. They just happen.
You've been so helpful in providing this code to us, could you also educate me just a tiny bit on how I determine or set a macro to run at specific times, or always, or on a certain event?
Hope I'm not pushing my luck here.
With great appreciation for your help,
Scott

**Roger Govier** · 07-27-2011, 07:55 AM

Hi Scott

It is just a question of adding another Case statement to the event code that you have on the data entry sheet.

Event code (in this case the change of a value in a cell) is triggered by the fact that an alteration has taken place in a specified range.
You are using the range of Target.column so it is just a simple addition to your code as shown below


Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
    Case 1  ' columns C & E are 3rd & 5th columns
        TypedVal = Application.WorksheetFunction. _
            Text(Target.Value, "000000")
        NewValue = Left(TypedVal, 2) & "/" & _
            Mid(TypedVal, 3, 2) & "/" & _
            Right(TypedVal, 2)
    Case 5, 6 ' Columns E & F are time columns
        TypedVal = Application.WorksheetFunction. _
            Text(Target.Value, "0000")
        NewValue = Left(TypedVal, 2) & ":" & _
            Right(TypedVal, 2)
    Case 13  ' change in the Invoice # column (M)
            Call CollectInvoices
            
    Case Else
    
    End Select
    If NewValue > 0 Then
        Application.EnableEvents = False
        Target.Value = NewValue
        Application.EnableEvents = True
    End If
End Sub

If a value changes in column M, then my macro will be called and run.