如何通过自定义 Excel 功能区打开用户窗体

Question

我正在尝试通过自定义 excel 功能区打开用户表单。 当我单击功能区中的按钮时，它开始初始化，并在工作簿.open 函数上将代码发送到查询关闭子。显示用户表单代码如下：

Sub RemoveFixture_onAction(control As IRibbonControl)
SelectedCompType = Fixture
Set EditComp = New ufUpdateComp
With EditComp
.Top = Application.Top + 125
.Left = Application.Left + 25
.Show
End With
End Sub

当代码开始userform_Initialize代码时，它最终移动到query_close子。 代码如下：

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'If wb Is Nothing Then UserForm_Initialize
wb.Close False
End Sub

如上所示，在注释掉的部分，当代码移动到 queryclose 函数时，我尝试返回到初始化子。当它运行工作簿.open 代码时，它会移动到 queryclose 子，并说 wb 什么都不是。我尝试单独打开工作簿，然后将工作簿设置为活动工作簿。 我也试过： 趁 WB 什么都不是的时候做 设置 wb = 活动工作簿 圈 这个循环无休止地运行，直到我不得不手动取消它。 它最初被设置 wb = 工作簿.open(Test)

Private Sub UserForm_Initialize()
Workbooks.Open Test, , , , , DynoCompPassword, True
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Info")
Set ws = wb.Worksheets("Info")
Set wsC = wb.Worksheets("Calipers")
Set wsF = wb.Worksheets("Fixtures")
Set wsW = wb.Worksheets("Wheel Sims")
ws.Visible = True
wsC.Visible = True
wsF.Visible = True
btnCreate.Enabled = False
Dim rng As Range
lblLocation.Visible = False
tbLocation.Visible = False
Me.cbOut.AddItem "Sent To"
Me.cbOut.AddItem "Scrapped"
Me.cbOut.AddItem "Returned"
Me.btnCreate.Enabled = True
For Each rngprojectcode In ws.Range("ProjectCode")
Me.cbProjectCode.AddItem rngprojectcode.Value
Next rngprojectcode
Set ProjCodeDictionary = New Dictionary 'Create the dictionary
Dim i As Integer
Dim j As Integer
Dim ProjCodeString As String
Dim AssociatedCodes As ProjectCodeList
If ws Is Nothing Then Exit Sub
ProjCodeDictionary.CompareMode = vbTextCompare 'Make the .exists method case insensitive in an attempt to avoid duplicate values
Set AssociatedCodes = New ProjectCodeList 'create the class module which will split up the associated codes into individual values
i = 1
While ws.Range("F1").Offset(i, 0) <> ""
With AssociatedCodes
.SetCodes = CStr(ws.Range("F1").Offset(i, 0).Value)
For j = 1 To .NumCodes
ProjCodeDictionary.Add .ProjCode(j), i 'key, item
Next j
End With
i = i + 1
Wend
If SelectedCompType = Fixture Then
Me.lblCompNum.Caption = "Fixture ID"
Me.btnCreate.Caption = "Update Fixture"
'Automation Error occurs here
Me.Caption = "Edit Fixture"
Me.frChangeFrame.Height = 65
Me.frChangeFrame.Caption = "Bolt Circle"
Me.cbPartNum.Text = "FIX"
For Each rng In wsF.Range("FixtureNum")
Me.cbPartNum.AddItem rng.Value
Next rng
Set tbNumStuds = frChangeFrame.Controls.Add("Forms.TextBox.1", , "True")

澄清一下，只有在用户窗体中按下带有 X 的红色框时，才应激活 queryclose 子。 它是用户表单的内置功能。

Answer 1

查询关闭子应该运行的唯一时间是在用户窗体上按下 X 按钮时。

但这不是QueryClose的工作方式。每当窗体即将关闭时，都会触发UserForm.QueryClose事件，其参数为您提供取消窗体的方法，具体取决于提示它关闭的原因。

你想要的是有条件地运行wb.Close False，当CloseMode参数值vbFormControlMenu时(X 按钮 - 请参阅 QueryClose 常量)：

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
wb.Close False
End If
End Sub

当代码 [...]

别这样。事件处理程序应由 VBA 本身调用，而不是由用户代码调用。如果需要调用已在事件处理程序中实现的逻辑，请将代码从处理程序中重构并重构到其自己的过程中：

Private Sub UserForm_Initialize()
DoInitializationStuff
End Sub
Private Sub DoInitializationStuff()
'...
End Sub

最后，UserForm.Initialize事件在显示窗体之前触发。

Set EditComp = New ufUpdateComp ' <~ initialize handler runs before this instruction returns
With EditComp
.Top = Application.Top + 125
.Left = Application.Left + 25
.Show
End With

请注意，如果您只在With块中使用它，则不需要声明局部变量 - 让块保存对象引用：

With New ufUpdateComp ' <~ initialize handler runs before this instruction returns
.Top = Application.Top + 125
.Left = Application.Left + 25
.Show
End With

如果希望DoInitializationStuff在显示窗体后运行，请在Activate事件中调用它。