通过VBA自定向Excel添加工具栏.doc

通过VBA自定义向Excel添加工具栏 Office由于提供了VBA,为大家开发一些定制功能提供了一种途径。但是如何实现工具栏中的命令与宏进行绑定，对于初学则来说是一个不小的门槛。 今天,给大家介绍一下在Excel里写完宏后,如何通过宏自动生成工具栏。 如图： 在VBA中将要用到CommandBar，CommandBarButton两个对象。 Option Explicit 定义全局变量 Private zyi_Bar As CommandBar Private zyi_ComBarBtn As CommandBarButton 增加工具栏 Sub AddToolBar() Application.CommandBars.Add(Name:=zy).Visible = True Dim strBarName As String Dim strParam As String Dim strCaption As String Dim strCommand As String Dim nIndex As Integer Dim nFaceId As Integer Dim cBar As CommandBar strBarName = ZYI_TOOL For Each cBar In Application.CommandBars If cBar.Name = strBarName Then Set zyi_Bar = cBar GoTo 20 End If Next On Error GoTo 10 Set zyi_Bar = Application.CommandBars(strBarName) If zyi_Bar.Name = strBarName Then GoTo 20 已经存在 zyi_Bar.Delete End If 10: On Error GoTo 100 Set zyi_Bar = Application.CommandBars.Add(Name:=strBarName) 20: zyi_Bar.Visible = True On Error GoTo 100 1. 复制工作表 nIndex = 1 strCaption = 复制工作表 strParam = 复制工作表的单元格内容及格式! strCommand = 复制工作表 nFaceId = 271 If zyi_Bar.Controls.Count nIndex Then AddComBarBtn strParam, strCaption, strCommand, nIndex, nFaceId ElseIf zyi_Bar.Controls(nIndex).Caption strCaption Then AddComBarBtn strParam, strCaption, strCommand, nIndex, nFaceId End If 2. 合并单元格 nIndex = 2 strCaption = 合并单元格 strParam = 合并单元格以及居中 strCommand = 合并单元格 nFaceId = 29 If zyi_Bar.Controls.Count nIndex Then AddComBarBtn strParam, strCaption, strCommand, nIndex, nFaceId ElseIf zyi_Bar.Controls(nIndex).Caption strCaption Then AddComBarBtn strParam, strCaption, strCommand, nIndex, nFaceId End If 3. 居中 nIndex = 3 strCaption = 居中 strParam = 水平垂直居中 strCommand = 居中单元格 nFaceId = 482 If zyi_Bar.Controls.Count nIndex Then AddComBarBtn strParam, strCaption, strCommand, nIndex, nFaceId ElseIf zyi_Bar.Controls(nIndex).Caption strCaption Then AddComBarBtn strParam, strCaption, strCommand, nIndex, n