Create A Shortcut

You can create a shortcut menu that displays when a user performs a specific action that contains commands related to VBA macro and Excel commands. A shortcut menu is a pop-up menu that displays when you right-click a particular location within Excel. You can create new shortcut menus or modify existing Excel shortcut menus, and you perform all shortcut menu creation and modification within the Visual Basic Editor.

A shortcut menu is similar to a toolbar in that both are actually CommandBar objects, but each has different controls. Typically a toolbar only contains icons whereas a shortcut menu can contain a combination of text descriptions and icons.

You can create a new shortcut menu by using the Add method associated with the CommandBars collection. Although the Add method has four different parameters —

Name, Position, MenuBar, and Temporary — they are all

MENU

optional. You specify a name for the new shortcut menu using the Name parameter. You assign a value of msoBarPopup to the Position parameter to create a pop-up menu. If you only want the toolbar to display after running the associated procedure, use the Temporary parameter.

See the section "Working with CommandBars" for more information about the Add method properties.

After creating the shortcut menu, you need to add menu items. To do this, you create a menu item by using the Add method associated with the CommandBarControls collection. With the Add method, you use the Type parameter with a value of msoControlButton.

See the section "Add Items to a Menu" for more information about adding items to a menu and setting the menu item properties.

CREATE A SHORTCUT MENU

CREATE A SHORTCUT MENU

Msobarpopup Access 2007 Form

□ Create a new subroutine.

0 Type Dim SItem1 As CommandBarControl, replacing SItemI with the first menu item variable.

L-0 Type Set SC = CommandBars.Add (Name:="TestBar", Position:=msoBarPopup), replacing "TestBar" with the name for the shortcut menu.

0 Type Set SItem1 = SC.Controls.Add(Type: msoControlButton).

□ Create a new subroutine.

'-0 Type Dim SC As CommandBar, replacing SC with the menu variable.

0 Type Dim SItem1 As CommandBarControl, replacing SItemI with the first menu item variable.

'-0 Type Dim SItem2 As CommandBarControl, replacing SItem2 with the second menu item variable.

L-0 Type Set SC = CommandBars.Add (Name:="TestBar", Position:=msoBarPopup), replacing "TestBar" with the name for the shortcut menu.

0 Type Set SItem1 = SC.Controls.Add(Type: msoControlButton).

'-0 Type With SItem1.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

RESULT:

The subroutine uses the Intersect method to determine if the clicked cell is part of the target range of A1:A10. If you click a cell in the target range, the TestBar shortcut menu displays.

TYPE THIS:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("a1:a10")) Is Nothing Then CommandBars( "Shortcut" ).ShowPopup

End If End Sub

I Type End With.

— Switch to Excel and run the macro.

■ The shortcut menu displays for the appropriate event.

Range", replacing "Bold Range" with the menu item caption.

—0 Type .OnAction = "BoldRange", replacing "BoldRange" with the name of the macro to launch.

I Type End With.

■ Repeat steps 6 through 10 for each menu item.

— Switch to Excel and run the macro.

■ The shortcut menu displays for the appropriate event.

DELETE CUSTOM TOOLBARS ,ND SHORTCUT MENUS

You can delete any of the custom toolbar or shortcut menus you no longer need to keep them current. Because all toolbars and shortcut menus are CommandBar objects, to delete one, you need to remove the associated object. Excel does not allow you to delete the built-in CommandBar objects. When you delete a custom CommandBar object, you can no longer access it within Excel. If you want to use the toolbar or shortcut menu again, you need to run the corresponding macro to re-create it.

One of the most important reasons for deleting a CommandBar object is to allow you to reload it. You generally reload when you make changes to the CommandBar object and want to reflect these changes in the currently loaded CommandBar object. Excel does not allow you to load a CommandBar object with the same name as an existing object. Therefore, if you decide to modify a menu or toolbar you need to delete the existing version to load the new one.

You remove a custom CommandBar object using the Delete method associated with the object. To use this method, you simply indicate the CommandBar object to delete followed by the Delete method. For example, to delete a toolbar named TestBar you would type CommandBar("TestBar").Delete.

Excel returns an error if you attempt to remove a built-in toolbar or menu. You can make sure a menu or toolbar is custom by looking at the value of the BuiltIn property for the CommandBar control. If the BuiltIn property has a value of True, the associated CommandBar control is one of the standard ones that comes with Excel, and you cannot delete it.

You can create a procedure that removes all of the custom toolbars and shortcut menus you have created by checking the value of the BuiltIn property. All custom menus and toolbars have a BuiltIn property value of False.

DELETE CUSTOM TOOLBARS AND SHORTCUT MENUS

DELETE CUSTOM TOOLBARS AND SHORTCUT MENUS

—n Create a new subroutine.

0 Type Dim CBar As CommandBar, replacing CBar with the command bar variable.

'—0 Type For Each CBar in CommandBars.

False

—n Create a new subroutine.

0 Type Dim CBar As CommandBar, replacing CBar with the command bar variable.

'—0 Type For Each CBar in CommandBars.

False

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

You can also modify toolbars and menus from within Excel by clicking ToolsO Customize. To prevent a user from modifying a menu or toolbar within Excel, you can set the msoBarProtection constant value for the Protection property. The example shows code that protects a toolbar so that a user cannot add or remove buttons. You can specify the constant values using the table.

Example:

CommandBars("MacroToolbar").Protection = msoBarNoCustomize

CONSTANT

DESCRIPTION

msoBarNoChangeDock

Cannot change docking of command bar.

msoBarNoChangeVisible

Cannot change Visible property.

msoBarNoCustomize

Cannot add or remove buttons.

msoBarNoHorizontalDock

Cannot dock on left or right side of window.

msoBarNoMove

Cannot move command bar object.

msoBarNoProtection

Removes all protection.

msoBarNoResize

Cannot resize command bar object.

msoBarNoVerticalDock

Cannot dock on top or bottom of window.

Writing Mru Worksheet

S Type CBar.Delete. L-0 Type End If.

Q Type Next.

■ Excel removes all custom toolbars.

S Type CBar.Delete. L-0 Type End If.

Q Type Next.

■ Excel removes all custom toolbars.

WORKING WITH CHARTS

CHART OBJECT CHILDREN OBJECTS (CONTINUED)

OBJECT

DESCRIPTION

Axes Collection

Collection of the Axis objects including AxisTitle, Border, Gridlines, and TickLabels objects.

ChartArea

Chart area including the Border, Font, and Interior objects.

ChartGroups Collection

Collection of ChartGroup objects representing each group of data on the chart.

ChartObjects Collection

Collection of the Chart objects on the sheet.

ChartTitle

Represents the chart title. Includes Border, Characters, Font, and Interior objects.

Corners

Represents the corners of a 3-D chart.

DataTable

Represents the chart data table. Includes a Border object.

Floor

Represents the floor of a 3-D chart. Includes Border and Interior objects.

Hyperlinks Collection

Contains one Hyperlink object for each hyperlink in the range of data.

Legend

Represents the legend of the chart. Includes Font, Border, Interior, and LegendEntries objects.

OLEObjects Collection

Collection of OLEObjects in the sheet. Includes Border and Interior objects.

PageSetup

Contains the page setup information including margin settings, paper size, and so on.

SeriesCollection Collection

Contains Series objects representing the data in the chart. Includes Border, Points, and Interior objects.

Shapes Collection

Collection of the shapes within the chart.

Tab

Represents a tab on a chart.

Walls

Represents the walls of a 3-D chart. Includes Border and Interior objects.

If you look at the Chart Type dialog box within Excel, you can see that Excel offers an enormous number of different charts. Although Excel has 14 standard types of charts, each chart type has at least two different subtypes that you can select. Excel provides additional customized charts on the Custom Types page. You select the chart type by specifying an xlChartType constant value for the ChartType property. The actual list of available chart types is rather extensive because it includes all of the chart sub-types. See Appendix A for the available xlChartType constant values.

CREATE A CHART SHEET

You can use VBA to add a new chart sheet to your workbook. When you create a chart, VBA creates a new Chart object, which contains all the chart options that correspond to the chart. Each Chart object contains several objects that represent the settings for the chart. For example, the ChartTitle object contains the chart title as well as its font and border properties, and other associated attributes. See the section "Chart Basics" for more information concerning the various child objects for the Chart object.

Because you have the option of either creating a new chart sheet or embedding a chart in a worksheet, you may find the creation of a Chart object a little confusing. When creating a new chart sheet, you use the Chart object directly, whereas with an embedded chart, you use a ChartObjects object. See the section "Embed a Chart within a Worksheet" for more information on creating embedded charts.

To create a separate chart sheet, you use the Add method with the Charts object. With this method, you can use three different parameter values to specify the location of the chart sheet and the number of sheets to add. You use the Before parameter to specify the sheet before which you want to place the new chart sheet. For example, to place the new chart sheet at the beginning of the workbook, you type a value of Sheets(1). You use the After parameter to indicate the sheet after which you want to place the new sheet. If you want to create multiple chart sheets, you can use the Count parameter to indicate the number of sheets to add.

When you create the new chart, you use the various properties, methods, and a child object of the Chart object to specify the type of chart, chart title, fonts used, and so on.

CREATE A CHART SHEET

CREATE A CHART SHEET

—D Create a new subroutine.

L0 Type Dim NewChart As Type Set NewChart =

Chart, replacing NewChart ThisWorkbook.Charts.Add().

with the name of the chart variable.

—Q Type NewChart.Name = "New Chart Sheet", replacing "New Chart Sheet" with the name for the chart.

—D Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

L0 Type Dim NewChart As Type Set NewChart =

Chart, replacing NewChart ThisWorkbook.Charts.Add().

with the name of the chart variable.

—Q Type NewChart.Name = "New Chart Sheet", replacing "New Chart Sheet" with the name for the chart.

WORKING WITH CHARTS

With any chart, you must specify the chart's range of data. No matter what other information you specify for a chart you create, if you omit the data source information your chart appears blank. You use the SetSourceData method to specify the data source for your chart. The SetSourceData method has two different parameters, as illustrated in the following code:

NewChart.SetSourceData(Source, Range)

You must use the Source parameter to specify the actual data range for the chart. The Source parameter can reference any valid data range. See Chapter 11 for more on defining a range of values. Remember when working with a chart sheet that you also need to indicate the name of the worksheet containing the data as part of the range reference. For example, the following code references the range of cells contained in Sheetl in the same workbook.

Example:

NewChart.SetSourceData(Source:=Worksheets("Sheet1").Range("A1:B15")

With the SetSourceData method, you can use the PlotBy parameter, which requires that you specify one of the xlRowCol constant values to instruct Excel how to plot the data in the specified range. A value of xlColumns instructs Excel to plot the data by columns. Use the value xlRows to have Excel plot the values by rows.

ype NewChart.ChartType = xlColumnClustered, replacing xlColumnClustered with the new chart type.

—0 Type NewChart.SetSourceData Source:=Worksheets("Sheet1") .Range(''A1:A5"), replacing Worksheets("Sheet1"). Range("A1:A5") with the range of values for the chart.

□ Switch to Excel and run the macro.

ype NewChart.ChartType = xlColumnClustered, replacing xlColumnClustered with the new chart type.

Note: See Appendix A for the xlchartType constant values.

—0 Type NewChart.SetSourceData Source:=Worksheets("Sheet1") .Range(''A1:A5"), replacing Worksheets("Sheet1"). Range("A1:A5") with the range of values for the chart.

□ Switch to Excel and run the macro.

Note: See Chapter 1 for more on running a macro.

-U Excel creates a new chart sheet with the specified range of data graphed.

0 0

Responses

  • biniam
    How to create short cut for VBA macro?
    6 years ago

Post a comment