使用VBIDE从Excel用户窗体中删除控件 [英] Remove Controls From Excel Userform With VBIDE

问题描述

我需要从75个 Excel 用户表单中删除一些控件.我得到了 VBA 代码循环遍历文件，并使用 VBIDE 删除了代码.但是，尚未能够控制控件.

I need to remove some controls from 75 Excel userforms. I got VBA code looping thorough the files and using the VBIDE, I got the code removed. However, haven't been able to get a handle on the controls.

这不是在浪费我尝试过的代码的时间，而是我一直在使用的对象:

Rather than wasting time with code I have tried, here are the object I have been using:

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim CompItem As Object
Dim objVBFrm As UserForm

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("frmSend")
Set CodeMod = VBComp.CodeModule
Set objVBFrm = VBComp.Designer

With objVBFrm
    .Controls.Remove chkNewCNV
End With

谢谢

推荐答案

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

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

这篇关于使用VBIDE从Excel用户窗体中删除控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！