按钮到。关闭 [英] Button to .Close

问题描述

嘿，

我有一个"关闭"调用运行一行的宏的工作表上的按钮：ActiveWorkbook.Close

I have a "Close" button on a worksheet that calls a macro that runs one line: ActiveWorkbook.Close

在工作簿关闭之前，我在'Sub Workbook_BeforeClose（取消为布尔）'过程中有一些代码要清理每个工作表上的内容，但是，我无法激活其他工作表。当代码运行时，它会粘在第一个
工作表上。我无法让它移动。

Before the workbook closes, I have some code in the 'Sub Workbook_BeforeClose(Cancel As Boolean)' procedure that's supposed to clean up stuff on each worksheet, however, I am unable to activate the other sheets. When the code runs, it sticks on the first worksheet. I can't get it to move.

我正在使用：ActiveWorkbook.Sheets（" Sheet1"）。激活

I'm using: ActiveWorkbook.Sheets("Sheet1").Activate

被困在这几天所以任何帮助表示赞赏！

Been stuck on this for a few days so any help appreciated!

推荐答案

你是绝对正确的。这正是发生的事情，并且无法从Before Close事件中激活其他工作表。我现在不知道它是否被视为一个错误或什么，但可以编写VBA代码来缓解问题。

You are absolutely correct. That is exactly what occurs and other worksheets cannot be activated from within the Before Close event. I don't now if it is regarded as a bug or what but the VBA code can be written to alleviate the problem.

几乎没有必要选择（或激活）工作表VBA代码。您可以使用VBA代码引用工作表，并执行所需的任何更改以及它的工作方式。

It is almost never necessary to select (or activate) worksheets with VBA code. You can reference the worksheets with the VBA code and perform whatever changes are required and this way it works.

在下面的简单示例中，尝试选择工作表并写入所选工作表结果在所有工作表名称中，在第一张工作表中插入单元格A1（一次一个），其余工作表将单元格A1留空。
（MsgBox只是为了停止代码，因此您可以看到工作表没有更改，并且名称将写入第一张工作表的单元格A1。）

In the following simple example of attempting to select a worksheet and write to the selected worksheet results in all of the worksheet names getting inserted in cell A1 (one at a time) on the first sheet and the remaining sheets have cell A1 blank. (The MsgBox is simply to stop the code so you can see that the worksheet does not change and the name is written to cell A1 of the first sheet.)

Private Sub Workbook_BeforeClose（取消为布尔值）

    Dim ws As Worksheet

   对于每个工作表中的ws $
        ws.Select

       范围（"A1"）= ws.Name

        MsgBox ws.Name

   下一个ws

结束子

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Select
        Range("A1") = ws.Name
        MsgBox ws.Name
    Next ws
End Sub

但是，使用以下示例，不尝试选择每个工作表，但引用通过限定参考的工作表，工作表名称将写入每个相应的工作表的单元格A1。

However, with the following example, without attempting to select each worksheet but referencing the worksheet by the qualified reference, the worksheet name is written to cell A1 of each of the corresponding worksheets.

Private Sub Workbook_BeforeClose（取消为布尔值）

    Dim ws As Worksheet

   对于每个工作表中的ws $
       使用ws

            .Range（" A1"）=。Name

       结束与$
       MsgBox ws.Name

   下一个ws

结束子

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
            .Range("A1") = .Name
        End With
        MsgBox ws.Name
    Next ws
End Sub

如果你不能转换你的代码来代替使用合格的参考选择然后发布它的副本，我会尝试为你纠正它。

If you can't convert your code to use the qualified reference in lieu of selecting then post a copy of it and I will attempt to correct it for you.

这篇关于按钮到。关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！