ExcelVBA窗体批量创建插件按钮,标签,单选

1、在excel文件工作表中创建按钮插件，按钮名称为CommandButtonl,如下所示:yHici-csoHExcelyHici-csoHExcel- -窗体事件一社窗体事件一社3当当文件文件0 0编辑编辑视图视图 9 9 插入插入Q Q）格式格式Q Q）工具豆）数据如窗口工具豆）数据如窗口5 5帮助帮助月岛月岛。收蔻夹。收蔻夹，前往前往&A&AC C：DoDo *ntssndSettingsAinini*ntssndSettingsAininiA AB BC CD DE EF FG G2345678|CommandBu910111213141516171819粘贴下面的代码至

2、创建按钮工作表后台书写代码处，点击按钮如下所示:PrivateSubCheckBox1_Click()EndSubPrivateSubCommandButton1_Click()DimTempFormDimLeftPosAsIntegerDimXAsIntegerDimiAsIntegerDimTopPosAsInteger创建窗体SetTempForm=ActiveWorkbook.VBProject.VBComponents.Add(3)声明创建窗体插件DimNewOptionButtonAsMSForms.OptionButtonDimnewCommandButtonnAsMSForms

3、.CommandButtonDimnewCheckBoxAsMSForms.CheckBoxDimnewLabelAsMSForms.LabelLeftPos=4k=1TopPos=5循环创建单选框个数(可根据实际情况而定)Fori=1To10LeftPos=4创建单选框SetNewOptionButton=TempForm.Designer.Controls.Add(forms.OptionButton.1)设置单选框属性WithNewOptionButton.Width=60.Caption=k&C.Height=15.Left=LeftPos.Top=TopPos.Tag=k&a

4、mp;C.AutoSize=TrueEndWithLeftPos=LeftPos+30k=k+1TopPos=i*20+5创建单选框宏代码WithTempForm.CodeModuleX=.CountOfLines.InsertLinesX+1,PrivateSubOptionButton&i&_Click()”.InsertLinesX+2,me.Label1.caption=+你的选择：+CStr(i)+C+.InsertLinesX+3,EndSubEndWithNexti创建按钮(可以批量创建)SetnewCommandButtonn=TempForm.Designer

5、.Controls.Add(Forms.CommandButton.1)设置按钮属性newCommandButtonn.Name=MyCommandButtonnewCommandButtonn.Object.Caption=确定WithnewCommandButtonn.Width=60.Height=20.Left=LeftPos.Top=TopPos+40.AutoSize=FalseVisible=TrueEndWith创建标签(可以批量创建)SetnewLabel=TempForm.Designer.Controls.Add(Forms.Label.1)设置标签属性newLabel.

6、Caption=WithnewLabel.Width=120.Height=20.Left=LeftPos.Top=TopPos.AutoSize=FalseVisible=TrueEndWith设置创建按钮宏代码WithTempForm.CodeModuleX=.CountOfLines.InsertLinesX+1,PrivateSubMyCommandButton_Click()”.InsertLinesX+2,msgboxMe.Label1.Caption+me.check.caption.InsertLinesX+3,me.hide.InsertLinesX+4,endsubEndW

7、ithSetnewCheckBox=TempForm.Designer.Controls.Add(Forms.CheckBox.1)newCheckBox.Caption=javanewCheckBox.Name=checkWithnewCheckBox.Width=120.Height=20.Left=LeftPos.Top=TopPos+20.AutoSize=FalseVisible=TrueEndWith设置窗体属性WithTempForm.Properties(Caption)=窗体界面.Properties(Width)=LeftPos+200.Properties(Height)=TopPos+100.Properties(Left)=160.Properties(Top)=100EndWith显示窗体VBA.UserForms.Add(TempForm.Name).Show点击窗体关闭打叉图标，则关闭窗体Active