自动刷新/计算工作表问题 [英] Auto Refresh/Calculate Worksheet Issue

问题描述

Hello all

我正在尝试创建一个工作表2来跟踪工作表1上工作名单的更改。

通过填写不同的颜色背景来管理名单，以指示该人员的日期/离开类型。

我们需要在单独的工作表上跟踪这些日子，所以目前我正在使用这个： / p>

颜色函数：

函数Colorfunction（ rColor作为范围，rRange作为范围，可选SUM作为布尔值）

Dim rCell As Range

Dim lCol As Long

Dim vResult < br style ="color：＃222222; FONT-FAMILY：宋体; font-size：small">
lCol = rColor.Interior.ColorIndex

如果SUM = True那么

每个rCell in rRange

如果rCell.Interior.ColorIndex = lCol那么

vResult = WorksheetFunction.SUM（rCell，vResult）

结束如果

下一个rCell

Else

每个rCell in rRange

如果rCell.Interior.ColorIndex = lCol那么

vResult = 1 + vResult

结束如果

下一个rCell

结束如果

Colorfunction = vResult

计算

结束函数

当我用新颜色更新工作表1（名单）然后移动到工作表2它不会自动更新时，这工作非常棒。我必须单击单元格然后关闭它然后
它会重新计算。

我尝试了诸如（和许多变化）之类的东西，但没有任何作用：

Private Sub Workbook_SheetChange（ByVal Target As Range）

如果Target.Address =" Sheet1！AS29：IV59"那么

    If Target.Value< Range（" Sheet1！AS29：IV59"）。 价值
然后

        ActiveSheet.EnableCalculation = True

        Application.Calculate

End Sub

有人可以告诉我要编写什么代码，如果我将其创建为模块或只是将其放在工作簿屏幕本身。

如果只修改了Sheet1！AS29：IV59的范围（作为矩形），工作表2也会更新。

如果有人可以帮忙的话，非常感谢！

Dan

解决方案

这将是好的，如果工作表2仅在Sheet1！AS29：IV59的范围被修改（作为矩形）时才更新。

无法使用该功能，更改颜色不会强制计算。

A dd"Application.Volatile"作为第一行。参见：

https://msdn.microsoft.com/VBA/Excel-VBA/articles/application-volatile-method-excel

Andreas。

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

解决方案

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.

这篇关于自动刷新/计算工作表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！