ExcelVBA窗體批量創(chuàng)建插件按鈕,標(biāo)簽,單選

1、在excel文件工作表中創(chuàng)建按鈕插件，按鈕名稱為CommandButtonl,如下所示:yHici-csoHExcelyHici-csoHExcel- -窗體事件一社窗體事件一社3當(dāng)當(dāng)文件文件0 0編輯編輯視圖視圖 9 9 插入插入Q Q）格式格式Q Q）工具豆）數(shù)據(jù)如窗口工具豆）數(shù)據(jù)如窗口5 5幫助幫助月島月島。收蔻夾。收蔻夾，前往前往&A&AC C：DoDo *ntssndSettingsAinini*ntssndSettingsAininiA AB BC CD DE EF FG G2345678|CommandBu910111213141516171819粘貼下面的代碼至

2、創(chuàng)建按鈕工作表后臺書寫代碼處，點(diǎn)擊按鈕如下所示:PrivateSubCheckBox1_Click()EndSubPrivateSubCommandButton1_Click()DimTempFormDimLeftPosAsIntegerDimXAsIntegerDimiAsIntegerDimTopPosAsInteger創(chuàng)建窗體SetTempForm=ActiveWorkbook.VBProject.VBComponents.Add(3)聲明創(chuàng)建窗體插件DimNewOptionButtonAsMSForms.OptionButtonDimnewCommandButtonnAsMSForms

3、.CommandButtonDimnewCheckBoxAsMSForms.CheckBoxDimnewLabelAsMSForms.LabelLeftPos=4k=1TopPos=5循環(huán)創(chuàng)建單選框個(gè)數(shù)(可根據(jù)實(shí)際情況而定)Fori=1To10LeftPos=4創(chuàng)建單選框SetNewOptionButton=TempForm.Designer.Controls.Add(forms.OptionButton.1)設(shè)置單選框?qū)傩訵ithNewOptionButton.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創(chuàng)建單選框宏代碼WithTempForm.CodeModuleX=.CountOfLines.InsertLinesX+1,PrivateSubOptionButton&i&_Click()”.InsertLinesX+2,me.Label1.caption=+你的選擇：+CStr(i)+C+.InsertLinesX+3,EndSubEndWithNexti創(chuàng)建按鈕(可以批量創(chuàng)建)SetnewCommandButtonn=TempForm.Designer

5、.Controls.Add(Forms.CommandButton.1)設(shè)置按鈕屬性newCommandButtonn.Name=MyCommandButtonnewCommandButtonn.Object.Caption=確定WithnewCommandButtonn.Width=60.Height=20.Left=LeftPos.Top=TopPos+40.AutoSize=FalseVisible=TrueEndWith創(chuàng)建標(biāo)簽(可以批量創(chuàng)建)SetnewLabel=TempForm.Designer.Controls.Add(Forms.Label.1)設(shè)置標(biāo)簽屬性newLabel.

6、Caption=WithnewLabel.Width=120.Height=20.Left=LeftPos.Top=TopPos.AutoSize=FalseVisible=TrueEndWith設(shè)置創(chuàng)建按鈕宏代碼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設(shè)置窗體屬性WithTempForm.Properties(Caption)=窗體界面.Properties(Width)=LeftPos+200.Properties(Height)=TopPos+100.Properties(Left)=160.Properties(Top)=100EndWith顯示窗體VBA.UserForms.Add(TempForm.Name).Show點(diǎn)擊窗體關(guān)閉打叉圖標(biāo)，則關(guān)閉窗體Active