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.
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)
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).
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.
