Macro Posts

Copy Part of Macro to Create another
Create a Macro Using Visual Basic
Record a Macro

Quick Access Toolbar Posts

Quick Access Toolbar
Quick Access Toolbar Customization Menu
Quick Access Toolbar Customization Tab

Copy Part of a Macro to Create Another Macro

Copy part of a macro to create another macro

1. If the Developer tab is not available, do the following to display it:

1. Click the Microsoft Office Button , and then click Excel Options.

2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:
1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

 Note   To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

3. Open the workbook that contains the macro that you want to copy.
4. On the Developer tab, in the Code group, click Macros.
5. In the Macro name box, click the name of the macro that you want to copy.
6. Click Edit.
7. In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.

 Tip   To copy the whole macro, include the Sub and End Sub lines in the selection.

8. On the Edit menu, click Copy.

 Tip   You can also right-click and then click Copy, or you can press CTRL+C.

9. In the Procedure box of the code window, click the module where you want to place the code.
10. On the Edit menu, click Paste.

 Tip   You can also right-click and then click Paste, or you can press CTRL+V.

 Tip   You cannot edit your Personal Macro Workbook file (Personal.xlsb) because it is a hidden workbook that is always open. You must first unhide it by using the Unhide command. You can also open it in the Visual Basic Editor by pressing ALT+F11.

Create a Macro using Visual Basic

Create a macro by using Microsoft Visual Basic

1. If the Developer tab is not available, do the following to display it:

1. Click the Microsoft Office Button , and then click Excel Options.

2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:
1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

 Note   To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

3. On the Developer tab, in the Code group, click Visual Basic.

4. If needed, in the Visual Basic Editor, on the Insert menu, click Module.

 Note   Modules are automatically created for all sheets in the workbook.

5. In the code window of the module, type or copy the macro code that you want to use.
6. To run the macro from the module window, press F5.
7. In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when you finish writing the macro.

Record a Macro

Record a macro

When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. Navigation on the Ribbon is not included in the recorded steps.
 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

1. If the Developer tab is not available, do the following to display it:

1. Click the Microsoft Office Button , and then click Excel Options.

2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:
1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

 Note   To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros. For more information about how to change the settings, see Change macro security settings in Excel.

3. On the Developer tab, in the Code group, click Record Macro.
4. In the Macro name box, enter a name for the macro.

 Note   The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.

5. To assign a CTRL combination shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.) to run the macro, in the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.

 Note   The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel, see Excel shortcut and function keys.

6. In the Store macro in list, select the workbook where you want to store the macro.

 Tip   If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook. In Windows Vista, this workbook is saved in the C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart folder. In Microsoft Windows XP, this workbook is saved in the C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart folder. Workbooks in the XLStart folder are opened automatically whenever Excel starts. If you want a macro in the personal macro workbook to be run automatically in another workbook, you must also save that workbook in the XLStart folder so that both workbooks are opened when Excel starts.

7. In the Description box, type a description of the macro.
8. Click OK to start recording.
9. Perform the actions that you want to record.
10. On the Developer tab, in the Code group, click Stop Recording .

 Tip   You can also click Stop Recording on the left side of the status bar.

Quick Access Tooblar Customization Tab

Category Selection Box

Users can select from many different commands categories. To select which type of category of commands you display in the Available Commands Window simply select the chosen category from the Category Selection Box. Some of the categories available to choose from are Popular Commands, Commands not in Ribbon and Macros.  Users can also select from a variety of different tabs.

Available Commands Window

This window will display the commands from the category chosen within the Category Selection Box. It is important to note that if you choose to select the Not in Ribbon category or any one of the specific tabs that the list of commands may change based on what Is displayed on your ribbon or tabs.

Document Selection Box

The Document Selection Box can be used to determine if you are going to customize a specific workbook or all documents of Excel.  If you choose All Documents then anytime you open Excel on your computer you will see that same command still on your Quick Access Toolbar.  If you only choose a specific workbook then you will not see that command on any other workbook within Excel.

Selected Commands Window

This window lists which commands are currently displayed on the Quick Access Toolbar.  The top command shown within this list will be the command displayed furthest to the left on the Quick Access Toolbar. The higher up the rest of the commands the further left that command will be displayed on the Quick Access Toolbar.  For example if you want a command displayed all the way to the right of the Quick Access Toolbar then you want to have it at the bottom of the Selected Commands Window.

Add Command Button

This button adds commands to Selected Commands Window.  To perform this function simply select the command you want from the list of available commands and select Add.  If you double click on one of the available commands you will also add the command.

Remove Command Button

This button removes commands from the Selected Commands Window. To remove a command select the command you want to remove from the Selected Commands Window and select remove.  Users can also double click on the command you want to remove.

Reorder Command Buttons

There are two command buttons located on the right side of the Selected Commands Window.  One points upwards. One points downward. This allows you to move commands within the Selected Commands Window up or down, in order to readjust the commands left or right on the Quick Access Toolbar.

Quick Access Toolbar Customization Menu

Introduction
The Quick Access Toolbar Customization Menu allows users to add or remove commands to the Quick Access Toolbar, Open the Quick Access Toolbar Customization Tab or show the Quick Access Toolbar above or below the Ribbon.

Adding Commands

To add commands using the Quick Access Toolbar Customization Menu check the checkbox of the preset command located on the Quick Access Toolbar Customization Menu.

Removing Commands

To remove commands from the Quick Access Toolbar using the Quick Access Toolbar Customization Menu uncheck the command’s checkbox.

Opening the Quick Access Toolbar Customization Tab

The Quick Access Toolbar Customization Tab allows users to add all the commands in Excel instead of just the preset commands shown on the Quick Access Toolbar Customization Menu. To open the Quick Access Toolbar Customization Tab select More Commands.

Show Above the Ribbon

The Show Above the Ribbon command will move the Quick Access Toolbar above the Ribbon.

Show Below the Ribbon

The Show Below the Ribbon command will move the Quick Access Toolbar below the Ribbon.