More programming for Microsoft Office command bars (2)

Source: Internet
Author: User
Tags exit constant goto
Programming can test the function by using code such as the following:

Public Sub Testaddcomboboxtocommandbar ()

' Purpose: Test the Addcomboboxtocommandbar function.

Dim Strchoices (4) as String

Strchoices (1) = "Vanilla"
Strchoices (2) = "Chocolate"
Strchoices (3) = "Strawberry"
Strchoices (4) = "Other"

If Addcomboboxtocommandbar ("Tools", "Favorite Ice Cream", _
strchoices) = True Then

The MsgBox combo box was successfully added. "

Else

MsgBox failed to add a combo box. "

End If

End Sub

Disabling and hiding command bars and command bar controls
When you develop Office solutions, you may want to prevent users from clicking on some of the command bars and command bar controls associated with the solution. For example, you might want to prevent users from clicking any of the controls on the Forms toolbar to avoid modifying a custom form that you create in Microsoft Word. You may also want to disable the macros command on the Tools menu for a given solution.

You can disable a command bar or command bar control by setting the Enabled property of a command bar or command bar control to False, and also enable a command bar or command bar control by setting the Enabled property of a command bar or command bar control to True.

Set the Visible property of a command bar or command bar control to False to hide a command bar or command bar control, and, similarly, set the command bar or command bar control's Visible property to True to display a command bar or command bar control.

To try this operation, enter the following line of code into the Immediate window. Each line of code switches the enabled or visible state of the specified command bar or command bar control. If you enter this code in Outlook or Microsoft Visual basic®editor, be sure to use application.activeexplorer or application.vbe terminology. To return to the enabled or visible state at the beginning, make sure that you run each row two times.

Application.CommandBars ("Tools"). Enabled = _
Not Application.CommandBars ("Tools"). Enabled
Application.CommandBars ("Tools"). Controls ("Macro"). Enabled = _
Not Application.CommandBars ("Tools"). Controls ("Macro"). Enabled
Application.CommandBars ("Tools"). Controls ("Macro"). Visible = _
Not Application.CommandBars ("Tools"). Controls ("Macro"). Visible

Position command bar
The Position property of the command bar specifies the location of the command bar in the application. The Msobarleft, msoBarTop, Msobarright, and Msobarbottom enumeration constants specify that the command bar is displayed to the left, top, right, or bottom of the application. The msoBarFloating enumeration constant specifies that the command bar is not attached to the edge of the application. The msoBarPopup enumeration constant specifies that the command bar is a pop-up menu.

The following function changes the location of the command bar that you specify.

Public Function changecommandbarposition (ByVal strcommandbarname as String, _
ByVal Msoposition as MsoBarPosition) as Boolean

' Purpose: Change the position of the command bar.
Accept
' Strcommandbarname: The name of the command bar to change the location.
' Return: ture if the command bar is moved successfully.

On Error GoTo Changecommandbarposition_err

' Replace the next line of code with the following:
' Application.ActiveExplorer.CommandBars.Item (strcommandbarname). Position = _
Msoposition <-for Outlook
' Application.VBE.CommandBars.Item (strcommandbarname). Position = _
Msoposition <-for Visual Basic Editor
Application.CommandBars.Item (Strcommandbarname). Position = Msoposition

Changecommandbarposition_end:

Changecommandbarposition = True
Exit Function

Changecommandbarposition_err:

Changecommandbarposition = False

End Function

You can test the function by using the code shown below:

Public Sub testchangecommandbarposition ()

' Purpose: Test the Changecommandbarposition function.

If changecommandbarposition ("Standard", msobarfloating) = True Then

The MsgBox command bar has been successfully moved. "

Else

MsgBox failed to move the command bar. Some command bars cannot be "& _
"Move in some way. "

End If

End Sub

Dynamically add and remove command bars
Sometimes you need to associate a command bar with a specific Office document. For example, you need to display some custom command bars when a particular Office document is open, and hide it when a particular Office document closes. To do this programmatically, using Excel is a good example. To test the operation, create a new, blank workbook, and then rename Sheet1 to Commandbarinfo. Type the following information into the Commandbarinfo workbook:



Figure 4: Dynamic creation of command bar information in Excel

In Visual Basic Editor, add the following code to the new code module that is associated with the new blank workbook:

Public Function Createcommandbarpopup () as Boolean

' Purpose: Based on the information provided in the EXCEL worksheet,
' Create a command bar pop-up control that contains menu items.

' Return: If the command bar pops up the control
' True if it was successfully added.

Dim Objworksheet as Excel.Worksheet
Dim Objcommandbarcontrol as Office.commandbarcontrol
Dim Objcommandbarpopup as Office.commandbarpopup
Dim objCommandBarButton as Office.CommandBarButton
Dim Introw as Integer

On Error GoTo Createcommandbarpopup_err

' The worksheet that contains the command bar information must be
' Named ' Commandbarinfo '.
Set Objworksheet = thisworkbook.sheets ("Commandbarinfo")

' Remove the control from the previous version of the
' All existing instances.
For each Objcommandbarcontrol in Application.CommandBars.Item ("Standard"). Controls

If objcommandbarcontrol.caption = objworksheet.cells (1, 1) Then

Objcommandbarcontrol.delete

End If

Next Objcommandbarcontrol

Set Objcommandbarpopup = _
Application.CommandBars.Item ("Standard"). Controls.Add (msoControlPopup)

Objcommandbarpopup.caption = Objworksheet.cells (1, 1)

Introw = 3

' Keep adding buttons until the title is exhausted.
Do Until objworksheet.cells (introw, 1) = ""

With Objcommandbarpopup

Set objCommandBarButton =. Controls.Add (msoControlButton)

With objCommandBarButton

. Caption = Objworksheet.cells (introw, 1)
. OnAction = Objworksheet.cells (introw, 2)

End With

End With

introw = introw + 1

Loop

Createcommandbarpopup_end:

Createcommandbarpopup = True
Exit Function

Createcommandbarpopup_err:

Createcommandbarpopup = False

End Function

Public Function Deletecommandbarpopup () as Boolean

' Purpose: Based on the information provided in the EXCEL worksheet,
' Remove the command bar pop-up control.

' Return: If the command bar pop-up control is
' True if the deletion was successful.

Dim Objworksheet as Excel.Worksheet
Dim Objcommandbarcontrol as Office.commandbarcontrol

On Error GoTo Deletecommandbarpopup_err

' The worksheet that contains the command bar information must be
' Named ' Commandbarinfo '.
Set Objworksheet = thisworkbook.sheets ("Commandbarinfo")

' Deletes all existing instances of the control.
For each Objcommandbarcontrol in Application.CommandBars.Item ("Standard"). Controls

If objcommandbarcontrol.caption = objworksheet.cells (1, 1) Then

Objcommandbarcontrol.delete

End If

Next Objcommandbarcontrol

Deletecommandbarpopup_end:

Deletecommandbarpopup = True
Exit Function

Deletecommandbarpopup_err:

Deletecommandbarpopup = False

End Function

Public Sub Testmacro ()

' Purpose: Provides an integrity Test macro.

MsgBox "This is a Test macro. "

End Sub

Add the following code to the ThisWorkbook module of the new blank workbook:

Private Sub Workbook_Open ()

If Createcommandbarpopup = False Then

MsgBox "The pop-up menu could not be added correctly. "

Else

The MsgBox pop-up menu has been successfully added. "

End If

End Sub

Private Sub workbook_beforeclose (Cancel as Boolean)

If Deletecommandbarpopup = False Then

MsgBox "Failed to remove the pop-up menu correctly. "

Else

The MsgBox pop-up menu has been successfully deleted. "

End If

End Sub

Save and close the workbook, and then reopen the workbook. Note that the housekeeping pop-up menu has been added to the Standard toolbar. Also note that when you close this workbook, the housekeeping pop-up menu disappears.

For a more detailed version of this technology, see the solution provided by the Excel Senior expert (MVP), John Walkenbach, at Http://j-walk.com/ss/excel/tips/tip53.htm (English).



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.