In this month's Office Q&A column, Susan Harkins shows three readers simple solutions to problems that aren't as big as they seem.
A solution can be easy to apply, but that's not helpful if you don't know about it. This month, three readers were perplexed by Office and fortunately, the solutions were easy to apply. This month, I'll share these three easy solutions with everyone:
- Setting Outlook's default account
- Restricting Excel's scroll area
- Removing lots of hard page breaks in a Word document.
I'm using Office 2016 on a Windows 10 64-bit system, but these solutions are available back to Office 2003. There's a downloadable .xlsm and .xls demonstration file you can use with the Excel solution.
Outlook default account
Malcolm is using Outlook 2010 on Windows 7 and has three email accounts. Unfortunately, Outlook doesn't default to the account he uses the most. The good news is that setting Outlook's default account is easy:
- Click the File tab and choose Advanced in the left pane.
- In the Outlook Start And Exit section, choose the folder you want to be the default. You can see in Figure A that I chose Manning.
- Click OK.
- Click the confirmation messages and click OK twice to return to Outlook.
- Close and reopen Outlook; the default account will be selected.
Specify the account (folder) you want to be the default.
When you create a new email message, Outlook will default to this account, but you can still choose another from the From dropdown.
Limit Excel scroll area
June doesn't want users wandering beyond the work area of a sheet. Fortunately, you can limit access by specifying a scroll area. I'll show you how to do this manually and using VBA.
First, to limit the scroll area manually, you enter the allowed scroll area using the ScrollArea property. For instance, let's set the scroll area for the sheet shown in Figure B to A1 to G46 as follows:
- Click the Developer tab.
- Click Properties in the Controls group.
- In the ScrollArea property field, enter $A$1:$G$46 (Figure C).
- Close the Properties dialog by clicking the X in the top-right corner.
We'll limit the scroll area of this sheet to the working area.
Enter the allowed scroll area as a valid range reference.
When you return to the sheet, you won't be able to scroll beyond column G or row 46. Users can still see the columns and rows beyond the limited scroll area, but they can't access them.
Note: If the Developer's tab isn't visible, you can add it as follows:
- Click the File tab and choose Options.
- In the left pane, select Customize ribbon.
- In the Main Tabs list (to the right), check the Developer option (Figure D).
- Click OK to return to the sheet.
Check Developer to display the tab.
A user could accidentally unlock this property, so you might want to set it using VBA. Of course, a user could still bypass that too, but most won't know how to do that. To set the ScrollArea property using VBA, do the following:
- Open the Visual Basic Editor (VBE) by pressing [Alt]+[F11].
- From the Insert menu, choose Module.
- Enter the code in Listing A.
- Close the VBE and return to Excel. Save the file as a macro-enabled file (if you're using a Ribbon version) and close the file. When you reopen it, the Open event will run, setting the scroll area. Or press [F5] to run the procedure and set the scroll area without closing your file to trigger the Open event.
Listing A Private Sub Workbook_Open() 'Set scroll area. Sheets("Data").ScrollArea = "A1:G46" End Sub
To reset the scroll area to nothing, you can use an empty string:
Sheets("Data").ScrollArea = ""
How you call it is up to you; you may never need it, but if you have a library module, you might want to include this short snippet, just in case.
Remove unwanted page breaks in Word
Mike inherited a Word document that's full of hard page breaks. Most of them are proving difficult to work with because he has been asked to make major changes. It's a long document and finding them all is going to be tedious work. Mike's in luck because he can use Word's Find And Replace feature to remove all the hard page breaks.
First, let me explain what a hard page break is. To force a new page, you press [Ctrl]+[Enter]. As a rule, hard page breaks are a bad idea. They inhibit Word's natural flow from page to page and in truth, they're seldom necessary because there are better ways to anchor content to the top of a page. You can learn more about avoiding hard page breaks by reading the following articles:
- Troubleshoot page and section breaks in Microsoft Word
- Avoid unnecessary page breaks by using Word styles
Now, let's return to Mike's problem—deleting hundreds of hard page breaks. The quickest way is to click Show/Hide in the Paragraph group on the Home tab to display the Page Break codes. Then, select the page break code (Figure E) and press [Delete]. But in Mike's case, doing so is impractical.
You can remove hard page breaks.
Word's Find And Replace feature will quickly delete all of those hard page breaks. To accomplish this, do the following:
- Click Replace in the Editing group on the Home tab.
- Click inside the Find What control.
- Click More (if necessary) to display additional options.
- From the Special dropdown, choose Manual Page Break (Figure F), which will add ^m to the Find What control.
- Make sure the Replace With control is blank; then click Replace All.
- Confirm the replacement message and click Close to return to the document.
Choose Manual Page Break.
There is a downside to this convenient solution. There might be hard page breaks that Mike wants to retain. If there are only a few, Mike's best recourse is to delete all the hard breaks in the document and then use one of the methods I linked to earlier to break the pages where he wants. It'll take a bit of work, but far less than removing hard page breaks selectively. And he'll have the added bonus of knowing the document can be easily modified in the future.
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. 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 email@example.com.
- How to add Office macros to the QAT toolbar for quick access
- How to print one or more labels for one or more Access records
- Excel tips: How to select cells and ranges efficiently using VBA
- How to take advantage of six new functions in Excel 2016