none
Auto Refresh/Calculate Worksheet Issue RRS feed

  • Question

  • Hello all

    I am attempting to create a Worksheet 2 that tracks changes to the work roster on Worksheet 1.

    The roster is managed by filling different colour backgrounds to indicate what type of day/leave the person is on.

    We need to track these days on a separate worksheet so currently I am using this:

    Colour Function:

    Function Colorfunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    If SUM = True Then
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
    End If
    Next rCell
    Else
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = 1 + vResult
    End If
    Next rCell
    End If
    Colorfunction = vResult
    Calculate
    End Function

    This is working terrific HOWEVER when I update Worksheet 1 (roster) with new colours and then move to Worksheet 2 it does not auto update.. I have to click in the cell and then off it and then it will recalculate.

    I have tried things such as (and many variations) but nothing works:

    Private Sub Workbook_SheetChange(ByVal Target As Range)
    If Target.Address = "Sheet1!AS29:IV59" Then
        If Target.Value < Range("Sheet1!AS29:IV59").Value Then
            ActiveSheet.EnableCalculation = True
            Application.Calculate
    End Sub

    Can someone please tell me what code to write and if I create it as a module or simply put it in the workbook screen itself.

    It would be good also if the Worksheet 2 only updated if the range from Sheet1!AS29:IV59 was modified (as a rectangle).

    Thanks very much in advance if anyone can help!

    Dan

    Tuesday, June 20, 2017 12:55 AM

All replies

  • It would be good also if the Worksheet 2 only updated if the range from Sheet1!AS29:IV59 was modified (as a rectangle).

    No way with that function, a change of a color doesn't force a calculation.

    Add "Application.Volatile" as first line. See also:
    https://msdn.microsoft.com/VBA/Excel-VBA/articles/application-volatile-method-excel

    Andreas.

    Tuesday, June 20, 2017 3:22 PM
  • You could use the worksheet deactivate event to force a re-calc:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Private Sub Worksheet_Deactivate()
    Application.CalculateFull
    End Sub

    Tuesday, June 20, 2017 9:17 PM
  • Hi DanJ85,

    I can see that after creating this thread , you did not responded to this thread.

    is your issue resolved?

    if yes, then try to post the solution and mark it as an answer.

    if your issue is still exist then I suggest you to refer the suggestion given by the community members.

    if you have any further question then let us know about that, we will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 30, 2017 5:08 AM
    Moderator