Excel VBA 窗体批量创建插件(按钮,标签,单选......)

在excel 文件工作表中创建按钮插件，按钮名称为CommandButton1，如下所示：

粘贴下面的代码至创建按钮工作表后台书写代码处，点击按钮如下所示：

Private Sub CheckBox1_Click()

End Sub

Private Sub CommandButton1_Click()

Dim TempForm

Dim LeftPos As Integer

Dim X As Integer

Dim i As Integer

Dim TopPos As Integer

'创建窗体

Set TempForm = ActiveWorkbook.VBProject.VBComponents.Add(3)

'声明创建窗体插件

Dim NewOptionButton As MSForms.OptionButton

Dim newCommandButtonn As MSForms.CommandButton

Dim newCheckBox As MSForms.CheckBox

Dim newLabel As MSForms.Label

LeftPos = 4

k = 1

TopPos = 5

'循环创建单选框个数（可根据实际情况而定）

For i = 1 To 10

LeftPos = 4

'创建单选框

Set NewOptionButton =

TempForm.Designer.Controls.Add(\"forms.OptionButton.1\")

'设置单选框属性

With NewOptionButton

.Width = 60

.Caption = k & \"℃\"

.Height = 15

.Left = LeftPos

.Top = TopPos

.Tag = k & \"℃\"

.AutoSize = True

End With

LeftPos = LeftPos + 30

k = k + 1

TopPos = i * 20 + 5

'创建单选框宏代码

With TempForm.CodeModule

X = .CountOfLines

.InsertLines X + 1, \"Private Sub OptionButton\" & i & \"_Click()\"

.InsertLines X + 2, \" me.Label1.caption=\"\"\" + \"你的选择:\" + \"\" + CStr(i) + \"\" + \"℃\" + \"\"\"\"

.InsertLines X + 3, \"End Sub\"

End With

Next i

'创建按钮（可以批量创建）

Set newCommandButtonn =

TempForm.Designer.Controls.Add(\"Forms.CommandButton.1\")

'设置按钮属性

newCommandButtonn.Name = \"MyCommandButton\"

newCommandButtonn.Object.Caption = \"确定\"

With newCommandButtonn

.Width = 60

.Height = 20

.Left = LeftPos

.Top = TopPos + 40

.AutoSize = False

Visible = True

End With

'创建标签（可以批量创建）

Set newLabel = TempForm.Designer.Controls.Add(\"Forms.Label.1\")

'设置标签属性

newLabel.Caption = \"\"

With newLabel

.Width = 120

.Height = 20

.Left = LeftPos

.Top = TopPos

.AutoSize = False

Visible = True

End With

'设置创建按钮宏代码

With TempForm.CodeModule

X = .CountOfLines

.InsertLines X + 1, \"Private Sub MyCommandButton_Click()\"

.InsertLines X + 2, \"msgbox Me.Label1.Caption + me.check.caption \"

.InsertLines X + 3, \"me.hide\"

.InsertLines X + 4, \"end sub\"

End With

Set newCheckBox = TempForm.Designer.Controls.Add(\"Forms.CheckBox.1\")

newCheckBox.Caption = \"java\"

newCheckBox.Name = \"check\"

With newCheckBox

.Width = 120

.Height = 20

.Left = LeftPos

.Top = TopPos + 20

.AutoSize = False

Visible = True

End With

'设置窗体属性

With TempForm

.Properties(\"Caption\") = \"窗体界面\"

.Properties(\"Width\") = LeftPos + 200

.Properties(\"Height\") = TopPos + 100

.Properties(\"Left\") = 160

.Properties(\"Top\") = 100

End With

'显示窗体

VBA.UserForms.Add(TempForm.Name).Show

'点击窗体关闭打叉图标，则关闭窗体

ActiveWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

End Sub

点击事件如图所示：

小例子，有兴趣者不妨看看，其实程序都是相通的，所谓条条大路通罗马，(*^__^*) 嘻嘻……