使用 Worksheet_Activate() 时没有屏幕更新的 Excel VBA

Question

我制作了一个宏，它根据输入表折叠一定数量的列。当用户激活工作表时，宏正在运行，如下所示。

Worksheet_Activate宏：

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect "mypassword"
Call collapsecolumns
ActiveSheet.Protect "mypassword"
End sub

称为的宏：

Public Sub collapsecolumns()
Dim ws1 As Worksheet: Set ws1 = Sheets("inputSheet")
Dim ws2 As Worksheet: Set ws2 = ActiveSheet
Dim sheetNo As Integer, colToCollapse As Integer
'number in sheet name define range for counting columns to collapse
sheetNo = Right(ws2.Name, 1) 
'input range differs depending on which sheet is chosen
colToCollapse = Application.WorksheetFunction.CountA(ws1.Range("J" & ((6 * sheetNo) - 4) & ":J" & ((6 * sheetNo) + 1)))
ws2.Range(Cells(1, 1), Cells(1, 35)).EntireColumn.Hidden = False
If colToCollapse = 0 Then
Exit Sub
End If
ws2.Range(Cells(1, colToCollapse * 6), Cells(1, 35)).EntireColumn.Hidden = True
End Sub

但是，当工作表被激活时，您会看到列折叠(或取消折叠(，具体取决于您在另一个工作表中编写的内容。我有各种组合和放置 ScreenUpdate=False 和 EntireEvents=False 来掩盖崩溃，但没有成功

有没有办法，当列折叠时，用户在使用Worksheet_Activate((时首先看到工作表？

Answer 1

我添加了两行代码，以便用户可以在列被隐藏时查看

Public Sub collapsecolumns()
Dim ws1 As Worksheet: Set ws1 = Sheets("inputSheet")
Dim ws2 As Worksheet: Set ws2 = ActiveSheet
Dim sheetNo As Integer, colToCollapse As Integer
'number in sheet name define range for counting columns to collapse
sheetNo = Right(ws2.Name, 1)
'input range differs depending on which sheet is chosen
colToCollapse = Application.WorksheetFunction.CountA(ws1.Range("J" & ((6 * sheetNo) - 4) & ":J" & ((6 * sheetNo) + 1)))
ws2.Range(Cells(1, 1), Cells(1, 35)).EntireColumn.Hidden = False
If colToCollapse = 0 Then
Exit Sub
End If
ActiveWindow.ScrollColumn = 32
Application.Wait (Now + TimeValue("0:00:1"))
ws2.Range(Cells(1, colToCollapse * 6), Cells(1, 35)).EntireColumn.Hidden = True
End Sub

它将首先将 excel 工作表移动到将隐藏的列中 然后它将等待 1 秒，然后隐藏列

我希望这就是你要找的...