ecel的工具欄和菜單欄vba源碼實(shí)例

1、工具欄和菜單欄工具欄和菜單欄的運(yùn)用更多時(shí)候是伴隨著加載宏和個(gè)性Excel界面的出現(xiàn)而出現(xiàn).在不斷加深對(duì)Excel VBA的理解和運(yùn)用,我們編程的思路漸漸會(huì)轉(zhuǎn)到考慮代碼的通用性和應(yīng)用方案上,將代碼和Excel數(shù)據(jù)源分開.因此,制作更多具有通用功能的加載宏不管是xla加載宏,還是 Com加載宏,可以最大極限的發(fā)揮 VBA編程的魅力,而不是要求用戶強(qiáng)制啟用宏.也正是由于這個(gè)原因,在我們?nèi)W(xué)習(xí)工具欄 和菜單欄時(shí),要明白的一個(gè)道理是,制作工具欄僅僅是為了加載宏等具體運(yùn)用的實(shí)現(xiàn),不要一味地去追 求工具欄的花哨.一、幾個(gè)根本概念在開始本節(jié)之前,先理解什么是命令欄？命令欄CommandBars:是工具欄、菜單

2、欄和快捷菜單的統(tǒng)稱.工具欄：帶有按鈕和選項(xiàng)的工具條,使用這些按鈕和選項(xiàng)可執(zhí)行命令.如下列圖：菜單欄：標(biāo)題欄下的水平欄,包括菜單名稱.如下列圖 快捷菜單：又叫彈出式菜單,鼠標(biāo)右鍵單擊.如下列圖. 二、CommandBars 集合對(duì)象通過(guò)上面幾幅圖片的直觀概念之后,我們接下來(lái)理解CommandBar集合.所有的工具欄和菜單欄代碼都是圍繞Commandbars集合展開的. CommandBarControls集合包含三種類型控件.CommandBarButton :代表命令欄中的一個(gè)按鈕控件按鈕控件：工具欄上的按鈕,或菜單、子菜單或快捷菜單上的菜單項(xiàng),當(dāng)單擊它們時(shí)會(huì)運(yùn)行一條命令.工具欄按鈕和菜單項(xiàng)共

3、享相同的屬性和方法.該 控件的 Type屬性必須是 msoControlButton.CommandBarComboBox :代表命令欄中的一個(gè)組合框控件組合框控件：菜單欄、工具欄、菜單、子菜 單或快捷菜單上的自定義編輯框、下拉列表框或組合框.當(dāng)工具欄垂直停靠時(shí),它所包含的任何自定義 組合框控件都不 可見.該控件的 Type屬性必須是 msoControlEdit、msoControlDropdown、 msoControlComboBox 、 msoControlButtonDropdown 、 msoControlSplitDropdown 、 msoControlOCXDropdown

4、、msoControlGraphicCombo 或 msoControlGraphicDropdown . CommandBarPopup :代表命令欄中的一個(gè)彈出式控件彈出式控件：是菜單欄或工具欄上的內(nèi)置或自定 義控件,當(dāng)單擊它時(shí)顯示菜單,或者是菜單、子菜單、或快捷菜單上的內(nèi)置或自定義菜單項(xiàng),當(dāng)指針?lè)?在其上時(shí)顯示子菜單.該控件 的 Type 屬 性 必須是 msoControlPopup、msoControlGraphicPopup、msoControlButtonPopup、 msoControlSplitButtonPopup 或 msoControlSplitButtonMRUPop

5、up .幾種常見屬性,參數(shù)和方法：VisibleNameTypePostionTemporaryCaptionOnActionFaceIDStyleEnableTop/Left/Width/Hight BeginGroupControlsAdd方法Findcontrols 方法 下面將通過(guò)實(shí)例來(lái)解釋上述屬性、參數(shù)和方法的運(yùn)用. 三、實(shí)例代碼1、建立一命令欄 即建立了一個(gè)工具欄.一般的,我們會(huì)相應(yīng)的定義一個(gè)Commandbar對(duì)象來(lái)操作這個(gè)自定義工具欄,如下代碼：Sub AddCommandBar1(),添加一自定義工具欄Dim cmdBar As CommandBarEnd Sub但,Exce

6、l好似任何變化,這是由于自定義工具欄的默認(rèn)Visible為False.Sub AddCommandBar2()'添加一自定義工具欄 ,并顯示Dim cmdBar As CommandBarcmdBar.Visible = TrueEnd Sub2、Position 例如Position:默認(rèn)值為 msoBarFloating 常量說(shuō)明msoBarLeft、msoBarTop、msoBarRight和 msoBarBottom 指定新命令欄的左側(cè)、頂部、右側(cè)和底部坐標(biāo) msoBarFloating指定新命令欄不固定msoBarPopup指定新命令欄為快捷菜單 msoBarMenuBar

7、僅適用于 Macintosh 機(jī) Sub AddCommandBar3()Dim cmdBar As CommandBarWith cmdBar.Name = "My Bar".Visible = True.Position = msoBarTopEnd WithEnd SubSub AddCommandBar4()Dim cmdBar As CommandBarcmdBar.Visible = TrueEnd Sub 為了防止出現(xiàn)重復(fù)的自定義工具欄,常規(guī)的代碼寫法是先刪除工具欄后,再添加.Sub AddCommandBar5()Dim cmdBar As CommandB

8、arCall DeleteCommandBarcmdBar.Visible = TrueEnd SubSub DeleteCommandBar()On Error Resume NextApplication.CommandBars("My Bar").DeleteEnd Sub3、 CommandBar Controls Type 例如接下來(lái)我們介紹 CommandBarControl對(duì)象CommandBarControl 對(duì)象與 CommandBarButton 、 CommandBarComboBox 以及 CommandBarPopup 對(duì)象 具有同樣的屬性和方法.

9、Sub AddCmdCtlType()Dim cmdBar As CommandBarDim cmdBtn As CommandBarButtonDim cmdCombo As CommandBarComboBoxDim cmdPop As CommandBarPopupCall DeleteCtlWith cmdBar .Visible = True Set cmdBtn = .Controls.Add(Type:=msoControlButton) With cmdBtn.Caption = "Button".Style = msoButtonCaptionEnd Wi

10、thSet cmdPop = .Controls.Add(Type:=msoControlPopup)With cmdPop.Caption = "Popup"End WithSet cmdCombo = .Controls.Add(Type:=msoControlComboBox)With cmdCombo.Caption = "Combo"End WithEnd WithEnd SubSub DeleteCtl()On Error Resume NextApplication.CommandBars("CommandControl Type

11、").DeleteEnd Sub4、 Width、Height 例如Sub AddButtonHight()Dim cmdBar As CommandBarDim cmdBtn As CommandBarButtonCall DeleteBtnWith cmdBar |.Visible = TrueSet cmdBtn = .Controls.Add(Type:=msoControlButton)With cmdBtn.Caption = "Hight Show".Style = msoButtonCaption.Height = 50End WithEnd Wi

12、thEnd SubSub DeleteBtn()On Error Resume NextApplication.CommandBars("cmdBtn Type").DeleteOn Error GoTo 0End Sub5、 內(nèi)置 FaceID、OnAction 和 Style 在 CommandBarButton 中的例如Sub AddCmdButton()Dim cmdBar As CommandBarDim cmdBtn As CommandBarButtonDim cmdBtn2 As CommandBarButtonCall DeleteBtnWith cmdB

13、ar |.Visible = TrueSet cmdBtn = .Controls.Add(Type:=msoControlButton)With cmdBtn.Caption = "Button1".FaceId = 12.OnAction = "ButtonShow1".Style = msoButtonIconAndCaptionEnd WithSet cmdBtn2 = .Controls.Add(Type:=msoControlButton)With cmdBtn2.Caption = "Button2".FaceId =

14、13.OnAction = "ButtonShow2".Style = msoButtonIconAndCaptionEnd WithEnd WithEnd SubSub ButtonShow1()MsgBox "Button1 test"End SubSub ButtonShow2()MsgBox "Button2 test"End Sub6、利用個(gè)性圖案制作自己的FaceID參考：AddCustomICO.xls,7、 CommandBar Type 例如：Popup參考： CommandBar Popup1.xls、 Comma

15、ndBar Popup2.xls、CommandBar Popup Form.xls8、建立一菜單欄Sub AddMenuBar()Dim cmdBar As CommandBarDim cmdMenu As CommandBarPopupDim cmdBtn As CommandBarButtonCall DeleteMenuBarSet cmdBar = Application.CommandBars("WorkSheet Menu Bar") |'Set cmdBar = Application.CommandBars(1)With cmdBarSet cmd

16、Menu = .Controls.Add(Type:=msoControlPopup, temporary:=True) With cmdMenu.Caption = "My Menu"Set cmdBtn = .Controls.Add(Type:=msoControlButton)With cmdBtn.Caption = "Iteml".OnAction = "ItemlAction".Faceld = 12End WithEnd WithEnd WithEnd SubSub Item1Action()MsgBox "

17、Menu Item test" |End SubSub DeleteMenuBar()On Error Resume NextApplication.CommandBars(1).Controls("My Menu").DeleteEnd Sub9、利用內(nèi)置命令制作自己的菜單Sub AddMenuBar2()Dim cmdBar As CommandBarDim cmdMenu As CommandBarPopupDim cmdBtn As CommandBarButtonDim cmdBuiltInBtn As CommandBarButtonDim cmdBu

18、iltInBtn2 As CommandBarPopupCall DeleteMenuBarSet cmdBar = Application.CommandBars("WorkSheet Menu Bar")With cmdBar |Set cmdMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True) With cmdMenu.Caption = "My Menu"Set cmdBtn = .Controls.Add(Type:=msoControlButton)With cmdBtn.Caption = "Iteml".OnAction = "ItemlAction".FaceId = 12End WithSet cmdBuiltInBtn = .Controls.Add(Type:=msoControl