Worksheet_Deactivate()无法使用活动工作表功能 [英] Worksheet_Deactivate() Cannot Use Active Sheet functions

问题描述

我在项目中有50个数据表，没有人记得去另一个表时运行save宏.一个聪明的主意是，当他们选择另一个工作表时，使用私有子Worksheet_Deactivate进行必要的计算.除了50个数据表外，工作簿中还有另外两个工作表，不能对其进行计算.如果可以将子项放置在工作表"中，而不是在单个工作表中复制50次，那就很好了，但另外两个工作表需要从处理中排除.

I have 50 datasheets in the project, and nobody remembers to run the save macro when going to another sheet. The bright idea is to use a private sub Worksheet_Deactivate to do the necessary calculations when they select another worksheet. In addition to the 50 datasheets, there are two more worksheets in the workbook for which the calculation must not run. It would be nice if the sub could be put in "Worksheets" rather than replicated 50 times in individual worksheets, but the two other worksheets need to be excluded from processing.

问题是，该子项默认为停用的工作表(例如宏代码中不合格的"Range.Value =)，但是活动的工作表现在是导航到的工作表.因此，任何ActiveXXXXX语句都指向错误的工作表.Worksheet.Name不允许.

Problem is, the sub defaults to the deactivating worksheet (such as an unqualified "Range.Value =" in the macro code), but the active worksheet is now the worksheet being navigated TO. So any ActiveXXXXX statement directs to the wrong worksheet. Worksheet.Name is disallowed.

数据表的编号为1到50.需要在deactivate子句中的早期声明类似于

Datasheets are numbered 1 to 50. What is needed is a statement early in the deactivate sub similar to

如果DeactivatingWorksheet(X)="BasicInfo"或"Constants"，则转到EndSub其中X是停用工作表的值.当然，X仅在处理时才被Excel知道.

If DeactivatingWorksheet(X) = "BasicInfo" Or "Constants" Then GoTo EndSub where X is the value of the deactivating worksheet. Of course, X is known only to Excel at the moment of processing.

我似乎无法弄清楚如何在宏的IF语句中引用停用的工作表.有什么想法吗?

I can't seem to figure out how to refer to the deactivating worksheet in the macro's IF statement. Any ideas?

推荐答案

使用 Worksheet_Deactivate() .即使在两种情况下，当事件触发时， ActiveSheet 都已更改，Workbook级事件提供了要离开的工作表的名称.像工作表变量一样使用 sh - sh.Name ， sh.ProtectionMode 等

Use Workbook_SheetDeactivate(ByVal sh as Object) instead of Worksheet_Deactivate(). The Workbook-level event supplies the name of the sheet being departed, even though in both cases the ActiveSheet has already changed when when event fires. Use sh just like a worksheet variable - sh.Name, sh.ProtectionMode, etc.

现在您不需要50个潜艇了；只有一个.这允许的另一件事是，您可以通过 sh.Activate 到旧的中止"对现在的 ActiveSheet 的更改(但是请关闭事件，否则您将拥有一个可爱的无限循环).

Now you don't need 50 subs; just one. Another thing that this allows is, you can "abort" the change to the now ActiveSheet by sh.Activate to the old one (but turn off events or you'll have a lovely infinite loop).

Me 还会给出旧的工作表名称，并适用于工作表事件，如果您仍然想这样做的话. Me 是旧版本， ActiveSheet 是新版本.

Me also gives the old sheetname and works for the worksheet event, if you still want to go that way. Me is the old one, ActiveSheet is the new one.

这篇关于Worksheet_Deactivate()无法使用活动工作表功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！