PRINT ALL EXCEL KEYBOARD SHORTCUTS

5.In Excel, type Hello and press Ctrl+Enter.

6.Stop the macro recorder using View, Macros, Stop Recording.

7.Press Alt+F11 to open VBA.

8.Press Ctrl+R to display the Project Explorer in VBA.

9.In the Project pane, click on VBAProject (PERSONAL.XLSB).

10.Use the + icon to expand PERSONAL.XLSB.

11.Use the + icon to expand Modules.

12.Double-click on Module1.

13.You will see your Macro1 code on the right. This is where you will type new macro code in the future.

14.Click the Save icon in the toolbar. If you skip this step, I can predict what will happen. At 5 o’clock, when you close Excel, you will see and answer “Do you want to Save?” questions about open work- books. You might be in a mood to close everything without saving. Personal.xlsb is a hidden work- book and will be the last “Do you want to Save” message that you see. Since you are not seeing any workbook on the screen, there is a tendency to say No and lose the macros you added.

1

Figure 143 You can type future macros in the code pane on the right.

Note: This Macro1 is not meant to be useful. You can actually delete the lines Sub Macro1() through End Sub. The goal here is to allow Excel to create Personal.xlsb in the correct location so you can use the mac- ros that follow.

MACRO TO TOGGLE POSITIVE TO NEGATIVE

Problem: My debits came in as credits. I need to quickly change the sign on all numbers in a range.

Strategy: Put a -1 in an empty cell. Copy that cell. Select the range of cells to toggle. Right-click, Paste Special. In the Paste Special dialog, use Values, Multiply, OK. Or - add this macro to your Personal Macro

Workbook:

Sub ToggleSign()

On Error Resume Next

For Each cell In Selection cell.Value = -cell.Value

Next cell

On Error GoTo 0

End Sub

Assign the macro to a shortcut key or a toolbar icon using one of the next two topics.

To use the macro, select the range that you want to toggle. Press a shortcut key to run the macro. Any text in the range will generate an error, but the code above will ignore errors, so text cells will be unchanged.

Problem: I need a fast way to run a macro that I’ve added to the Personal Macro Workbook.

Strategy: You can assign up to 26 macros to Ctrl+Shift+A through Ctrl+Shift+Z. Follow these steps.

1. Press Alt+F8 to display a list of macros.

2. Click on the desired macro name.

3. Click the options button. In the Options dialog, type a shortcut key. Note to assign a macro to

Ctrl+Shift+S, you simply type Shift+S into the box. Click OK.

Additional Details: You can assign a macro to the unshifted shortcut keys. But, if you assign the Toggle- Sign macro to Ctrl+S, you will not be able to Save using Ctrl+S anymore. Plus, any time that you try to Save with Ctrl+S, you will be toggling the sign of selected cells.

ASSIGN A MACRO TO A TOOLBAR ICON

Problem: I want to run a macro from an icon in the Ribbon or the Quick Access Toolbar.

Strategy: This is similar to "Make Your Most-Used Icons Always Visible" on page 12, with a couple of extra steps.

1. Right-click on the Quick Access Toolbar and choose Customize Quick Access Toolbar.

2. In the top-left dropdown, choose Macros.

3. In the left list box, choose the desired macro. Click the Add >> icon in the center of the screen. 4. Click on the macro in the right list box. Click Modify... at the bottom.

5. Choose one of the 180 available icons. Fondly recall the 4096 icons available in Excel 2003. 6. Type a meaningful tool tip in the box for Display Name.

7. Click OK for each of the open dialog boxes.

Figure 144 Choose an icon to appear in the toolbar and give the icon a good tool tip.

Result: the icon will be available in the Quick Access Toolbar.

Figure 145 Oddly, in Excel 2016, the icons are converted to monochrome.

USE A MACRO TO CHANGE CASE TO UPPER, LOWER, OR PROPER

Problem: Microsoft Word offers a command to convert text to UPPER or lower case. How can you do that in Excel?

Strategy: While you could add a new column with =UPPER(), =LOWER(), or =PROPER(), these three macros will make it easy to change the case for all of the selected cells.

Sub ApplyLowerCase()

For Each cell In Selection cell.Value = LCase(cell.Value)

Next cell End Sub

Sub ApplyUpperCase()

For Each cell In Selection cell.Value = UCase(cell.Value)

Next cell

End Sub

Sub ApplyProperCase()

Set WF = Application.WorksheetFunction For Each cell In Selection

cell.Value = WF.Proper(cell.Value) Next cell

End Sub

See "Create a Personal Macro Workbook" on page 64 and the previous topic for how to use these macros.

GET FREE EXCEL HELP

Problem: I have a question which is not answered in this book.

Strategy: A large community of Excel fans are available at the MrExcel.com message board. Collectively, they answer over 30,000 questions each year. Follow these steps:

Figure 146 Choose Excel Questions.

6. Click the New Thread.

Figure 147 Start a new question thread.

7. Use a meaningful subject. “Pivot Table Calculated Fields” is a good subject. “Help” or “Please Help” is not a good subject.

8. Type your question. Read over the question to make sure that someone who has not been dealing with your spreadsheet for the last hour can get a good sense of the meaning.

9. Click the Submit button at the bottom of the form.

Within a minute, your question will appear in the forum. Other readers will check out the question. Usu- ally within 15 minutes, someone will either post a clarifying question or a suggestion on how to proceed. If someone asks a question, provide the best answer that you can.

Usually, within a few iterations of questions, you will have your answer. Sometimes, if the question is particularly interesting, a discussion will break out over the best way to solve the problem.

Additional Details: Over 900,000 answers are archived and searchable. Before posting your question, spend a few minutes searching to see if the problem has already been solved.