vba 工作表_新工作表的激活码
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2113008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Worksheet_Activate Code for New Sheets
提问by Ryan B
I have three questions about VBA and controlling/manipulating new windows.
我有三个关于 VBA 和控制/操作新窗口的问题。
I have several sheets set up.
我设置了几张纸。
Master | Worksheet1 | Worksheet2 | Notes | Work Orders | Contact Info
硕士 | 工作表1 | 工作表2 | 笔记 | 工单 | 联系方式
1) I have WorkSheet_Activate functions set up on Notes, Work Orders, Contact Info that open up all three sheets in seperate windows and arrange them vertically.
1) 我在备注、工单、联系信息上设置了 WorkSheet_Activate 功能,可以在单独的窗口中打开所有三个工作表并垂直排列它们。
Private Sub WorkSheet_Activate()
ActiveWindow.NewWindow
ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=xlVertical
Sheets("Notes").Select
Windows("Mastersheet.xlsm:2").Activate
Sheets("Work Orders").Select
Windows("Mastersheet.xlsm:1").Activate
Sheets("Contact Info").Select
End Sub
The problem with it is that if I can activate these sheets again, it will open more windows. I would like the code to detect if the windows are already open and break if it is.
它的问题在于,如果我可以再次激活这些工作表,它将打开更多窗口。我希望代码能够检测窗户是否已经打开并打破。
2) Now, when I navigate to a different sheet, such as Master, I would like the extra windows to close and for the Master sheet to be active. I was using the following code on the Master sheet.
2) 现在,当我导航到不同的工作表(例如 Master)时,我希望关闭额外的窗口并使 Master 工作表处于活动状态。我在主表上使用了以下代码。
Private Sub WorkSheet_Activate()
Windows("Mastersheet.xlsm:2").Activate
ActiveWindow.Close
Windows("Mastersheet.xlsm:1").Activate
ActiveWindow.Close
ActiveWindow.WindowState = xlMaximized
End Sub
The problem with this code is that if the extra windows aren't open then it will error out. Can I do a logic check of some sort to get this to work? I don't know what values to check...
这段代码的问题是,如果额外的窗口没有打开,那么它就会出错。我可以进行某种逻辑检查以使其正常工作吗?我不知道要检查什么值...
3) The last problem is that there are new sheets generated dynamically by macros within the workbook. Those new worksheets won't carry the above code that closes multiple windows and focuses on the activesheet. Is there a different object that I should be putting the code to so that it applies to the Master | Worksheet1 | Worksheet2 sheets and any new sheets?
3)最后一个问题是工作簿中的宏动态生成了新的工作表。那些新的工作表不会携带上述关闭多个窗口并专注于活动表的代码。是否有不同的对象我应该将代码放入其中以便它适用于 Master | 工作表1 | Worksheet2 工作表和任何新工作表?
回答by Dick Kusleika
That's a lot of questions. :) For 3, you need to move your events out of where they are and into a custom class module that handles application level events. Start by inserting a new class module into your project (Insert - Class Module). Name that module CAppEvents (F4 to show the property sheet where you can change the name). Then paste this code into the class module
这是很多问题。:) 对于 3,您需要将您的事件移出它们所在的位置并移入处理应用程序级事件的自定义类模块中。首先在您的项目中插入一个新的类模块(插入 - 类模块)。将该模块命名为 CAppEvents(F4 以显示可在其中更改名称的属性表)。然后将此代码粘贴到类模块中
Option Explicit
Private WithEvents mobjWb As Workbook
Private Sub Class_Terminate()
Set mobjWb = Nothing
End Sub
Public Property Get wb() As Workbook
Set wb = mobjWb
End Property
Public Property Set wb(objwb As Workbook)
Set mobjWb = objwb
End Property
Private Sub mobjWb_SheetActivate(ByVal Sh As Object)
Dim wn As Window
If IsSplitSheet(Sh) Then
If Not IsSplit(Sh) Then
CreateSplitSheets Sh
End If
Else
If IsSplit(Sh) Then
For Each wn In Me.wb.Windows
If wn.Caption Like Me.wb.Name & ":#" Then
wn.Close
End If
Next wn
ActiveWindow.WindowState = xlMaximized
Sh.Activate
End If
End If
End Sub
Private Function IsSplitSheet(Sh As Object) As Boolean
Dim vaNames As Variant
Dim i As Long
IsSplitSheet = False
vaNames = GetSplitSheetNames
For i = LBound(vaNames) To UBound(vaNames)
If vaNames(i) = Sh.Name Then
IsSplitSheet = True
Exit For
End If
Next i
End Function
Private Function IsSplit(Sh As Object) As Boolean
Dim wn As Window
IsSplit = False
For Each wn In Me.wb.Windows
If wn.Caption Like Sh.Parent.Name & ":#" Then
IsSplit = True
Exit For
End If
Next wn
End Function
Private Sub CreateSplitSheets(Sh As Object)
Dim vaNames As Variant
Dim i As Long
Dim wn As Window
Dim wnActive As Window
vaNames = GetSplitSheetNames
Set wnActive = ActiveWindow
For i = LBound(vaNames) To UBound(vaNames)
If vaNames(i) <> Sh.Name Then
Set wn = Me.wb.NewWindow
wn.Activate
On Error Resume Next
wn.Parent.Sheets(vaNames(i)).Activate
On Error GoTo 0
End If
Next i
Sh.Parent.Windows.Arrange xlVertical
wnActive.Activate
Sh.Activate
End Sub
Private Function GetSplitSheetNames() As Variant
GetSplitSheetNames = Array("Notes", "Work Orders", "Contact Info")
End Function
Then insert a standard module (Insert - Module) and paste this code
然后插入一个标准模块(Insert - Module)并粘贴此代码
Option Explicit
Public gclsAppEvents As CAppEvents
Sub Auto_Open()
Set gclsAppEvents = New CAppEvents
Set gclsAppEvents.wb = ThisWorkbook
End Sub
Here's what's happening: When you open the workbook, Auto_Open will run and it will create a new instance of your CAppEvents object. Since gclsAppEvents is public (aka global) it won't lose scope for as long as the workbook is open. It will sit there listening for events (because we used the WithEvents keyword in the class).
发生的情况如下:当您打开工作簿时,Auto_Open 将运行并创建 CAppEvents 对象的新实例。由于 gclsAppEvents 是公开的(又名全局),只要工作簿处于打开状态,它就不会失去作用域。它将坐在那里监听事件(因为我们在类中使用了 WithEvents 关键字)。
In the class there's a sub called mobjWb_SheetActivate. This is what will fire whenever any sheet in this workbook is activated. First it checks if the sheet you just activated (the Sh variable) is one of the ones you want to split (using IsSplitSheet). If it is, it then checks to see if it already has been split. If not, it splits them.
在这个类中有一个叫做 mobjWb_SheetActivate 的子类。这是激活此工作簿中的任何工作表时将触发的内容。首先,它检查您刚刚激活的工作表(Sh 变量)是否是您要拆分的工作表之一(使用 IsSplitSheet)。如果是,则检查它是否已经被拆分。如果没有,它会将它们分开。
If Sh (the sheet you just activated) is not one of the 'split sheets', then it checks to see if a split has been done (IsSplit). IF it has, it closes all the split windows.
如果 Sh(您刚刚激活的工作表)不是“拆分工作表”之一,则它会检查是否已完成拆分(IsSplit)。如果有,它将关闭所有拆分窗口。
If you even want to add, change, or delete sheets that cause a split, you go to the GetSplitSheetNames function and change the Array arguments.
如果您甚至想添加、更改或删除导致拆分的工作表,请转到 GetSplitSheetNames 函数并更改数组参数。
Because we're using a custom class and sniffing for events at the workbook level, you can add and delete sheets all you want.
因为我们使用自定义类并在工作簿级别嗅探事件,所以您可以随意添加和删除工作表。
回答by Doc Brown
1) To test if a window is already open, use this function
1) 要测试窗口是否已经打开,请使用此函数
Function IsWindowOpen(windowTitle As String) As Boolean
Dim i As Long
For i = 1 To Windows.Count
If Windows(i).Caption = windowTitle Then
IsWindowOpen = True
Exit Function
End If
Next
IsWindowOpen = False
End Function
For example:
例如:
if not IsWindowOpen("Mastersheet.xlsm:2") then
' code to open windows
end if
2) You can reuse the function again, same idea:
2)您可以再次重用该功能,同样的想法:
if IsWindowOpen("Mastersheet.xlsm:2") then
' code to close windows
end if
3) Add your code to a module, not to a sheet. Then call the routine from the macro which adds the new sheets after it has done this. If this macro is in a different module, you may have to make sure your Sub is public.
3) 将您的代码添加到模块中,而不是工作表中。然后从宏调用例程,在完成此操作后添加新工作表。如果此宏在不同的模块中,您可能必须确保您的 Sub 是公开的。