且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

使用VBIDE从Excel用户窗体中删除控件

更新时间:2023-12-06 10:19:34

也许我没有达到您的确切目标,但以下内容应该至少可以帮助您入门

maybe I didn't get your exact goal, but what follows should help you get started at least

Sub RemoveControlsFromUserForm()
Dim VBP As VBIDE.VBProject
Dim VBC As VBIDE.VBComponent
Dim cntrls As Controls
Dim cntrl As Control

Set VBP = ActiveWorkbook.VBProject
For Each VBC In VBP.VBComponents
    With VBC
        If .Type = vbext_ct_MSForm And .Name = "UserForm1" Then
            Set cntrls = .Designer.Controls
            For Each cntrl In cntrls
                If TypeName(cntrl) = "CheckBox" Then
                    If cntrl.Name = "chkNewCNV" Then cntrls.Remove cntrl.Name
                End If
            Next cntrl
        End If
    End With
Next VBC

End Sub

根据需要更改组件类型(vbext_ct_MSForm)和名称("UserForm1")以及控件("CheckBox","chkNewCNV")

just change component type (vbext_ct_MSForm) and name ("UserForm1") as well as control ones ("CheckBox", "chkNewCNV") as per your needs

对于无效的前向引用"错误,可能是您尝试更改(删除)其控件时加载了"frmSend"用户窗体.在这种情况下,您必须先解压缩它(仅将其隐藏是行不通的),然后对其进行操作并最终将其加载.或者可能是您必须在删除控件之一之前运行删除逻辑子程序或两者的结合...然后由于所有这些操作(卸载表单,处理表单及其控件/逻辑)的实际时间安排(尽管不由自主)进行了干扰,因此仍然可能存在一些问题.

as for the"Invalid forward reference" error, it could be the "frmSend" userform is loaded while you're attempting to change (delete) its controls. should that be the case, you must firts unload it (only hiding it wouldn't work) then act on it and finally load it. or it could be that you must run the removing logic sub before the removing control one or a mix of the two... and then there could still last some issues due to the actual timing of all those operations (unloading form, processing it and its controls/logic) having them unproperly (though involuntarily) interfere.

要摆脱那些(可能还有其他!)可能的副作用,一个简单的解决方案可能是简单地隐藏不需要的用户窗体控件,并且您可以在显示用户窗体之前立即在加载用户窗体的代码中进行操作.或者,如果您必须"以编程方式进行操作,则可以添加那些隐藏"代码行来处理(而不是删除)逻辑

To get rid of those (and may be other!) possible side effects a plain solution could be simply hiding unwanted userform controls, and that you can do right in your code that loads the userform just before showing it. Or, if you "must" act programmatically, you can add those "hiding" code lines processing (instead of removing) the logic