Geek Answers Handbook

Automatic Excel VBA UDF calculation associated with cell properties

I wrote UDF to count cells of a certain color and with some LineStyles, I send the whole function:

Function CountTime(rData As Range, cellRefColor As Range) As Variant    

    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Variant

    Application.Volatile

    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
        If cellCurrent.Borders(xlDiagonalUp).LineStyle <> xlNone Then
            cntRes = cntRes + 0.5
        End If
    Next cellCurrent

    CountTime = cntRes
End Function

Now the problem is that the formula does not calculate automatically when one of the cells in rDatahas color or linear properties. I added Application.Volatile, and I also tried calling the calculation using Worksheet_Change, but this does not work, since Excel does not seem to consider color changes in a cell / sheet.

Is there a way to make the cell automatically calculate and update when the user changes the color or properties of the cell line in rData?

- ignotus, ChangeSelection . . , .

+4
2

, , , :

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

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

, , , Me.Calculate

FYI: Me?

Me , "". "", Me .

Me , , , , " " - UserForm, . Me, , .

Sheet1 module :

Worksheets("Sheet1").Range("A1").Select
Me.Range("A1").Select
+1

(, 50 ), Private Sub Worksheet_SelectionChange(ByVal Target As Range); , . , . , , recalc , , , Private Sub Worksheet_Deactivate() . , .

0

More articles:


All Articles