Programming and Application(编程与应用)

Content(目录)

Linux

MySQL
Office

Generic Code for Inserting Munuitems to Excel Menubar

Zhanshan Dong

After you create a handy form in Excel by using VBA, how od you integrate the form to Excel. Actually you have several choices: put into menu bar and toolbar, provide a public mocro that can be called from macro dailog. But if you can integrate the function into Excel menubar, it will facilate the user. In order to do that, you should write some extra code to do that. The following piece of code can do that. You can copy the following code to your program and use it. The only thing that you should do is modify assignMI subroutine and add the corresponding private subroutines to link your forms.

Option Explicit

Private miarr(1 To 2, 0 To 100) As String
Private nSubMenu As Integer

Private Sub assignMI()
    miarr(1, 0) = "Data Quality Control"
    miarr(1, 1) = "&Growth Curve"
    miarr(1, 2) = "&Pairwise Cross Check"
    miarr(2, 0) = ""
    miarr(2, 1) = "GrowthCurve"
    miarr(2, 2) = "CrossCheck"
    nSubMenu = 2
End Sub

Private Sub GrowthCurve()
    frmGrowthCurve.Show
End Sub

Private Sub CrossCheck()
    frmPairwiseCrossCheck.Show
End Sub

Private Sub auto_open()
    Dim i As Integer
    Dim ToolsBar As CommandBarPopup
    Dim newPopButton As CommandBarPopup
    Dim newButton As CommandBarButton
    assignMI
    Set ToolsBar = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set newPopButton = ToolsBar.Controls.Add(msoControlPopup)
    With newPopButton
        .Caption = miarr(1, 0)
    End With
    For i = 1 To nSubMenu
        Set newButton = newPopButton.Controls.Add(msoControlButton)
        With newButton
            .Caption = miarr(1, i)
            .OnAction = miarr(2, i)
        End With
    Next i
    Set newPopButton = Nothing
    Set ToolsBar = Nothing
    Set newButton = Nothing
End Sub

Private Sub Auto_Close()
    On Error Resume Next
    Dim i As Integer
    Dim ToolsBar As CommandBarPopup
    Dim newButton As CommandBarButton
    Dim newPopButton As CommandBarPopup
    assignMI
    Set ToolsBar = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
    Set newPopButton = ToolsBar.Controls(miarr(1, 0))
    For i = 1 To nSubMenu
        Set newButton = newPopButton.Controls(miarr(1, i))
        newButton.Delete
    Next
    newPopButton.Delete
    Set ToolsBar = Nothing
    Set newPopButton = Nothing
    Set newButton = Nothing
End Sub

Post comments(留言)

由Google提供