none
how auto update vba script in excel ?

    Question

  • Hi people,

     i have a VBA script inside a Module

    Function SumColor(rColor As Range, rSumRange As Range)
    
     Dim rCell As Range
     Dim iCol As Integer
     Dim vResult
     
     Application.Volatile
     
     'iCol = rColor.Interior.ColorIndex
     iCol = rColor.Font.Color
    
     For Each rCell In rSumRange
    'If rCell.Interior.ColorIndex = iCol Then
     If rCell.Font.Color = iCol Then
     vResult = WorksheetFunction.Sum(rCell) + vResult
     End If
     Next rCell
    
     SumColor = vResult
    End Function

    how can simply sum cell if they are the same font.color that a reference cell

    but when i'm changing a cell the VBA dont auto-update the result and i have to push F9 to make it happen...

    so is there anyway to auto refresh the vba (on timer or on mouse event (click) )

    thx a lot people.


    Wednesday, February 29, 2012 10:05 PM

Answers

  • The result of a formula using SumColor should automatically be updated when you change the value of one of the cells involved, but not when you change the font color of one of those cells. You could create an event procedure for the Worksheet_SelectionChange event in the worksheet module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Me.Calculate
    End Sub

    This still won't update the formula result when you change the text color of a cell, but it will when you select a different cell or cells, either using the mouse or using the keyboard.

    Regards, Hans Vogelaar

    Wednesday, February 29, 2012 10:37 PM

All replies

  • The result of a formula using SumColor should automatically be updated when you change the value of one of the cells involved, but not when you change the font color of one of those cells. You could create an event procedure for the Worksheet_SelectionChange event in the worksheet module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Me.Calculate
    End Sub

    This still won't update the formula result when you change the text color of a cell, but it will when you select a different cell or cells, either using the mouse or using the keyboard.

    Regards, Hans Vogelaar

    Wednesday, February 29, 2012 10:37 PM
  • Thx a lot Hans,

    is totally what was i'm looking for ! and it's working well :)

    thx, thx, thx !!
    Thursday, March 01, 2012 7:55 AM