Learn how to keep an Excel macro button in sight and how to intervene when Word converts characters into something you never intended.
This month, two readers are suffering from visual problems. Brian has a disappearing problem and Clare has a mysterious quick-change problem. Specifically, Brian wants to know how to keep a macro button visible and available, regardless of where the active cell is. Clare wants Word to stop usurping a two-character keystroke combination. Follow along to learn both solutions.
I'm using Office 365's desktop Excel on a Windows 10 64-bit system. You can work with your own data or download the demonstration .xlsx and .xls files for Brian's solutions. Keep in mind that macros aren't supported in the browser, so a macro button is useless there. Clare's problem doesn't require a demonstration file.
SEE: 20 pro tips to make Windows 10 work the way you want (TechRepublic download)
Freeze macro buttons
Brian's question is more frequent that you might think. Users like to embed a macro button adjacent to a sheet, which works fine until you scroll off screen. When you scroll to the right or down, you lose sight of the button—now you see it, now you don't. Excel's Freeze Panes option (on the View tab) allows you to "freeze" rows and columns so that they're always visible, regardless of how far you scroll. It's an easy-to-use feature that makes a busy sheet much easier to use. Position the macro button in the frozen area, and its also frozen. It still works, it just won't disappear off screen.
Figure A illustrates the problem nicely. Let's suppose the Delete Member button in row 1 deletes data in the current row. (The button doesn't actually do that—the article is about placement, not function.) In this case, the solution is simple—freeze the columns to the left of the button as follows:
- Select column D. To select an entire column, click the column header cell.
- Click the View tab.
In the Window group, click Freeze Panes, and then choose Freeze Panes (Figure B) from the drop-down list. That's it.
When you return to the sheet, you will notice a bolder borderline between columns C and D. This is your visual cue that everything to the left of that bold line is frozen. When you scroll to the right, columns A, B, and C remain visible, as you can see in Figure C: Columns D, E, F, and G and are off screen, but you can still see columns A, B, and C, and more importantly the button, because you froze those columns.
You may have noticed that there are's three options on the dropdown. The first one, Freeze Panes uses the selection to determine which rows and columns are frozen. In this case, we selected column D to freeze columns A through C (all the columns to the left of D). The second and third options freeze the row 1 and column A, respectively. If scrolling down is the problem, freeze row 1 by selecting Freeze Top Row from the Freeze Panes dropdown.
Positioning the button just right can be a bit tricky. You might have to move an existing button to make this trick work to your advantage.
Stop AutoCorrect madness
Clare has a mysterious problem with the combined characters, 'c. When she types these two characters, Word converts them into a special character, ç. Anytime Word usurps your intentions in this way, you should check the AutoCorrect feature for an entry.
Out of the box, this feature automatically corrects several universally misspelled words, such as the for teh. You can add custom entries to the feature to work more efficiently. For instance, I might create an entry that converts ssh to Susan Harkins. Most likely, someone has created an AutoCorrect entry for 'c on Clare's system.
The quickest way to check is to type 'c (in Clare's case). When Word converts it to ç, press Ctrl+z. If it's an AutoCorrect entry, pressing Ctrl+z should undo the conversion, leaving the original characters, 'c. When this happens, you have two choices. You can use Ctrl+z to undo the conversion, or you can delete the AutoCorrect entry as follows:
- Click the File tab.
- In the left pane, select Options. (If not available, click Home first.)
- In the left pane, choose Proofing. Then, click AutoCorrect Options in the AutoCorrect options section.
- In the resulting dialog, type the characters in question—in this case 'c.
- If there's an AutoCorrect entry, it will sort to the top of the list (Figure D). With this item selected, click Delete, and then click OK twice to return to your document.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- How to use basic PowerPoint skills to make pictures fit (TechRepublic)
- Excel tip: Three cell formats that will make header text fit (TechRepublic)
- How to use Word mail-merge (TechRepublic)
- Office Q&A: How to use a macro to set Find defaults (TechRepublic)
- System update policy template download (Tech Pro Research)
- Microsoft Office 365 for business: Everything you need to know (ZDNet)
- It takes work to keep your data private online. These apps can help (CNET)
- Programming languages and developer career resources coverage (TechRepublic on Flipboard)