EXCEL VBA初步(6)

Commandbars.add(name:=\

Set cmbnewbar = CommandBars.Add(Name:=\

Public Sub listcommandbars() Dim objcmdbar As CommandBar Dim i As Long i = 1

For Each objcmdbar In Application.CommandBars i = i + 1

With objcmdbar

ActiveSheet.Cells(i, 1) = .Index ActiveSheet.Cells(i, 2) = .Enabled ActiveSheet.Cells(i, 3) = .Visible ActiveSheet.Cells(i, 4) = .Type ActiveSheet.Cells(i, 5) = .Name End With

Next objcmdbar End Sub

Commandbarbutton对象的CLICKED事件 类模块

Private WithEvents eventcmdbutton As CommandBarButton

Private Sub eventcmdbutton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

MsgBox Ctrl.Caption & \End Sub 模块

Public Sub eventtest()

Dim cmbnewbar As CommandBar Dim objbutton As CommandBarButton

Set cmbnewbar = CommandBars.Add(\cmbnewbar.Visible = True

Set objbutton = cmbnewbar.Controls.Add(msoControlButton) objbutton.Style = msoButtonCaption objbutton.Caption = \文字按钮\Set eventscmdbutton = objbutton End Sub

自定义工具栏

Public Sub createtoolbar()

Dim objcmdbar As CommandBar

Dim objbutton As CommandBarButton

Set objcmbar = CommandBars.Add(\objcmdbar.Visible = True

Set objbutton = objcmbar.Controls.Add(msoControlButton, 2) objbutton .Style = msoButtonCaption

objbutton.Caption = \文字按钮\objbutton.OnAction = \

Set objbutton = objcmbar.Controls.Add(msoControlButton, 3) objbutton .Style = msoButtonIcon

objbutton.Caption = \图标按钮\objbutton.OnAction = \

Set objbutton = objcmbar.Controls.Add(msoControlButton, 4) objbutton .Style = msoButtonIconAndCaption objbutton.Caption = \文字和图标\objbutton.OnAction = \End Sub

Public Sub mymethod1() MsgBox \调用过程1\End Sub

Public Sub mymethod2()

MsgBox \调用过程2\End Sub

Public Sub mymethod3() MsgBox \调用过程3\End Sub 组合框控件

Commadbarcombobox

Public Sub addcombobox()

Dim objcmdbar As CommandBar

Dim objcmb As CommandBarComboBox

Set objcmdbar = CommandBars.Add(\

Set objcmb = objcmdbar.Controls.Add(Type:=msoControlComboBox, ID:=1) With objcmb

.AddItem \

.AddItem \.DropDownLines = 3 .DropDownWidth = 75 .Style = msoComboNormal End With

objcmdbar.Visible = True End Sub

给现有菜单增加菜单项

Public Sub addmenuitem()

Dim objcmdbar As CommandBar

Dim objcmdbutton As CommandBarButton Dim objcmdpop As CommandBarPopup

Set objcmdbar = CommandBars(“worksheet menu bar\ If Not objcmdbar Is Nothing Then

Set objcmdpop = objcmdbar.Controls(\工具（&t）\ If Not objcmdpop Is Nothing Then

Set objcmdbutton = objcmdpop.Controls.Add(msoControlButton) objcmdbutton.Style = msoButtonCaption objcmdbutton.Caption = \我的工具\ objcmdbutton.OnAction = \End If End If

End Sub

Inserttoolbar

Deletcommandbarbyerrortrapping

用户窗体

For each control in userform1.controls Control.visible=false Next control 组合框 列表框 复选框

EXCEL内置对话框

Application,dialogs,dialog

Application.dialogs(xldialogalignment).show

Application.dialogs(xldialogsaveas).show”文件名”