Kay ewbank Database developer and productivity expert Kay Ewbank offers help with office
applications. Can I clear a document list? Q How do you remove and delete items from the Recent Documents list to stop them appearing in Word document? John Jaap A The easiest way to clear the list of documents is to set the number of documents displayed to zero and then back up to the number you want to show. This clears the current list. If you're using Word 2007 or later, click the Microsoft Office Button and choose Word Options. Then choose Advanced and, in the Display area, set Show this number of Recent Documents list' to 0. Click OK and go back to the usual document display. Repeat the process, setting the number back to however many you'd like to see. A more selective method is to use Word's pushpin' option. In the recently opened documents list, look at the right-hand of the pane next to the document names; you should see an icon that looks like a map pin. If you click on this, the document will remain on the recently opened documents list for as long as the pin stays pushed in. This is a useful option if you have documents you want to open regularly. To clear a few documents from the list, push the pins of all the others, then open as many documents as necessary to clear the unwanted documents from the list. If you're using Word 2003, there's a shortcut key combination that should let you remove a single document from the list. It should still work in Word 2007, although we have encountered problems with it. If you press Ctrl-Alt at the same time as the -' key (the one next to 0 and above the P key), the mouse pointer will change to a horizontal line. Then choose the File menu if you're using Office 2003, or the Office button in 2007, and click the document you want to remove from the list and it should disappear. Finally, you could use the following macro, which tells you all your recent documents one once, and gives you the chance to remove them by pressing the y' key: Sub recentdocs() Dim rf As RecentFile For Each rf In Application.RecentFiles ask = InputBox(rf.Name & " Clear file Y/N") If UCase(ask) = "Y" Then rf.Delete End If Next rf End Sub As with all the options discussed, this is a shortcut to the document that's removed, not the actual file. If you're doing this for privacy, pay attention to the Recent Documents list that appears when you carry out actions such as inserting a file to attach to an Outlook email message. There are several options for clearing this, depending on whether you're running Windows XP, Vista or 7, and also whether you want the full list emptied or just certain items from it. If you want to remove all items from the list and you're running Windows 7 or Vista, you can empty the list by selecting the Recent Items item on the Start menu. Right-click on it and choose the Clear Recent Items List' option. If you're using Windows XP and want to clear the entire list, right-click on the Start button and click Properties. Select Customize, then choose the Advanced tab. If you've got the Classic view selected, you'll see an option to clear your recently used documents. Hiding columns in Access forms Q I would like to give my users the option of hiding and showing columns on a subform in Microsoft Access. For example, sometimes they want to be able to show the discount being offered, but other times they want to hide it if they're passing the information on to their customers. Is it possible to do this? A Wrigley A One fairly easy way to do this is to add a command button to your main form that, when clicked, opens the UnHide Columns' dialog box. Then your users can choose which columns to hide or unhide. All the command button needs is the following simple code on its OnClick event: Private Sub UnhideHide_Click() Me.[orders subform].SetFocus RunCommand acCmdUnhideColumns End Sub This opens the orders subform. Then just replace that portion with the name of your actual subform. How do I get Outlook to autocomplete? Q When I'm replying to customers emails, I often find that I'm typing the same message repeatedly. Can I have some sort of autocomplete, so that Outlook finishes the sentence for me once I've started it? I'm using Outlook 2007. Jon Ball A You can get more or less the facility you want using Outlook Quick Parts. These are pre-defined sections of text that you can include in your email messages just as long as you also have Word 2007 installed. You define a Quick Part by beginning a new mail message, and then typing the text you'd like to have available. You can apply formatting if you wish. Highlight the text portion you want to have available for reuse. Then choose Insert from the Office Ribbon. On the Insert tab, you'll see Quick Parts in the Text group. Click on that and then choose Save Selection to Quick Part gallery'. You need to give your Quick Part a name, and you can optionally specify a category and a description. When you want to reuse your Quick Part, there are two options available to you. You could go via the Office ribbon and click Insert, Quick Parts. Then choose the appropriate text snippet from the Preview list. However, if you've named your Quick Part cleverly, you can type the first few letters of the name you gave to the text snippet and then press function key F3 to insert the entire text portion. For this to work, the letters you've typed must uniquely identify a particular text block. Improving Access forms Q I'm trying to improve the appearance of the forms I have in my Access database. I use quite a few list boxes where users can choose items without having to type the details. Several of my list boxes have multiple columns product name and price, for example. Is there a way to set different colours for the various columns? I've only been able to find a Fore Color property, but that sets the colour for all the columns. Is there a way to set the colours separately? I'd be happy to use some VBA code if necessary Ian Harrison A As you've found out, the Fore Color property sets the colour for both the headers in the list box and all the columns. A less well-used property of Access is the ability to set the colours for individual columns within tables and queries. In Access 2003 and 2007, if you set the Format property for a field in table Design view, that format is used to data in datasheets. It is also used on forms and reports. If you define the format of a number field as 0.00, it will be displayed with two decimal places, irrespective of where in your database it appears. There is nothing to stop you defining it as 0.00[Blue] and also having it appear in blue. You may not want to have your product names always showing in blue and the prices in red. Fortunately, you can specify a colour with queries, and this is where the idea becomes really useful. If you base your list box on a query and set the colours for the columns there, you'll get the effect you want. Open your query in Design view and choose to view the Properties box. Move to each column in turn and, in the Properties box, set the format to the colour you've chosen using the syntax [Blue];@', [Green];@' and so on. You can use the normal Access colours, such as red, green, blue, cyan, yellow and so on. This technique doesn't work on ID columns they'll have to stay black. Still, if you use this query as the basis for your list box, the colours should appear the way you want them to. Excel can't add up Q I'm using Excel to construct a set of data that runs in equal increments from 1 upwards, jumping by 0.1 each time. I want to go from each integer to 0.7 above it, then skip .8 and .9 and begin at the next integer. So from 1.0 to 2.0, it would go: 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 2.0. I'm using =IF(B1-INT(B1)=0.7,B1+0.3,B1+0.1)'. However, Excel is apparently not very good at arithmetic, because if I try the equation =B8-INT(B8)' (where B8 contains 1.7) then the answer is 0.700000000000001'. Why is this happening, and how can I make it better? Amy Woods A The problem lies in the amount of memory Excel uses to store real' numbers with a fractional element such as 0.1. It has to use an approximation, and small errors in the approximation build up. There are two ways around your problem. You could alter your test to use =IF(B1-INT(B1)>=0.7,B1+0.3,B1+0.1)'. However, the safest solution is to remove the unwanted parts. The ROUND function rounds a number to the specified number of decimal places. Say you change your formula to read: =IF(ROUND(B1,1)-INT(B1)=0.7,B1+0.3,B1+0.1) You tell Excel to ignore all but the first digit after the decimal point, and your function will work as you want it to. Calculating overtime Q I've got an Excel spreadsheet where I store details of the hours I work in my painting and decorating business. I then invoice the people I work for based on the hours I worked, whether it was on a weekday or at the weekend, and whether I was working outside my normal hours. What I want is a formula that looks at both columns, identifies if the day was at a weekend or if it fell during the working week, and shows how many minutes of overtime I worked after 5pm. Phil White A Assuming your start time and date is stored in column A, and your end time and date is stored in column B, the following formulae will give you the answers you need. We haven't taken account of what happens if you work after midnight. To work out whether the day you worked was a weekday, you will need to use the Weekday function =IF(WEEKDAY(A5,2)>5,"Weekend","Weekday"). This looks at the value in A5, and returns Weekend if the day number is greater than five, or Weekday otherwise. You then need to work out how many overtime minutes you've worked. It may be that there are more elegant solutions than this, but without writing a macro to make use of the extra date functions this makes available, you need a formula like this: =IF(AND(C5="Weekday",HOUR(B5)>=17), ((HOUR(B5)-(MAX(HOUR(A5),17)))*60+ (IF(HOUR(A5)>=17,(MINUTE(B5)-MINUTE (A5)),MINUTE(B5))))) This says that if the answer in cell C5 has Weekday, and the hour part of the time you stop work is greater than 17, you are then working overtime. In this case, there are two parts to work out. First, the number of hours. If you do a couple of hours overtime in the evening starting at 7pm, say, then you don't want to charge as though you started the overtime portion at 5pm, so you need to find the number of hours between the start and end times. However, if you just carried on working from 5pm, you want to charge overtime from that portion onwards. To find this out, we use HOUR(B5)-(MAX(HOUR(A5),17). In other words, we take the ending time (which we already know is greater than 17, so after 5pm) and subtract from it whichever is the greater, 17 or the hour portion of the start time. This figure is then multiplied by 60 to get the number of minutes. Then we look at the actual minutes element. What we need to do here is to first check whether the hour portion of the start time is greater than 17, or 5pm. If it is, then you started work after 5pm, so the overtime minutes is given by the minute part of the end time minus the minute part of the start time. However, if you started before 5pm, you don't want to subtract the starting minutes because that would be diddling you out of some overtime, so in this case you just want the ending minutes. The minutes part is therefore worked out using: IF(HOUR(A5)>=17,(MINUTE(B5)-MINUTE(A5)), MINUTE(B5)) Putting text on top of an image in Word 2007 Q I'm using Word 2007, and I want to put words on top of a picture so the picture still shows through. Is this possible? Al Cooke A There are three ways of doig this. The first is to right-click on the picture. In the dialog that appears, choose Format Picture, and on the Layout tab select the 'Behind text' option. If your layout has the picture 'In line with text' but with a text box in front of it for the words, you need to work on the text box format. Right-click on the box, choose Format Text Box and, on the Colors and Lines tab, choose No Color for the Fill effect and Line color. A final option is set the image as a watermark, in which case it appears on every page. To do this, select Watermark from the Page Background group on the Page Layout tab. Select Custom Watermark, Picture Watermark, then click the Select Picture button. Choose the image you want to use and click Insert. You can then select a percentage for the Scale to insert the image at the correct size. If the picture is likely to be too bright, select the Washout option. Formatting a letter in Word Q I'm using Office Word 2007 on a Windows 7 PC to write letters. How do I move the cursor to the top right-hand side of a blank document to key in my own address then move it back to key in the letter? John Jaaps A You need to use a tab character that's far enough across the page so you get the effect you want, but still have enough room to enter your address laid out in the style you want. Just make sure the ruler is showing at the top of your document, then click on it at the point you want your address to start. If the ruler isn't showing, click on the View menu and put a tick into Ruler in the Show/Hide section. Now when you want to begin typing your address, press the Tab key on your keyboard and your cursor will move across the page to the point you entered the tab character. You then type the first line of your address. Press the Enter key, then the Tab key to move across for the second line, and so on. You'll probably find the lines of your address are separated by blank lines; if this happens and isn't what you want, select the whole address and click on the small arrow at the right-hand edge of the Paragraph area of the Home tab on the Office ribbon. You'll see the Paragraph dialog appear, from where you want the Lines and Page Breaks tab. On this tab there's an option 'Keep lines together'. Click this to get the effect you want. You can alter the position of your address tab simply by dragging it across the ruler to the place you want it to move to - if you want to move all the lines of the address, you need to select them all first. As this is all a bit of a faff, we'd be tempted to set up a blank letter containing just the address and save it as a template called 'personal letter'. You'll then be able to choose this as your document type when you want to write a new letter. To save a template, simply choose Save As and, from the options you're offered, choose Word Template. For the location, select Trusted Templates. When you want to use the template, you'll need to look in the My Templates group of the overall templates you're offered. Who's creating Outlook appointments? Q In order to manage our conference rooms, we've created accounts for each one. Everyone in the office can add appointments to each conference room's Outlook calendar. Is there a way to see who actually created a particular event? Andy Holmes A There are two ways you can do this. First, if you open the appointment and look on the Scheduling page, the person who set up the event will appear as its creator. If it's a meeting, they'll appear as the Organizer. Alternatively, you can create a custom List view showing the 'From' field, which will be the person who sent the request. Strikethrough in PowerPoint Q I'm using PowerPoint 2003 for a presentation, and I want to put some text into it that uses a strikethrough text effect. How can I do this? Chris Gardner A The short answer is you can't if you're using PowerPoint 2003. If you move to PowerPoint 2007, you get the effect you want, as strikethrough appears as one of the font buttons in the Font area of the Office ribbon. As you're using PowerPoint 2003, one choice would be to draw the lines through yourself using the PowerPoint drawing tools. A slightly better solution would be to write your text in Word and format it there as strikethrough text, then copy and paste it into PowerPoint as a Word object. To do this, choose Paste Special rather than Paste, and choose Microsoft Office Word Document Object as the type of what you're pasting. Garbled text in Access Q I've a problem with a report I've created in Access 2007. The report is based on a query, and contains a text field. When I preview the report, the text shows as foreign characters. I've checked the text, put a limit of 250 characters on the length, and messed around with the format, but it's still unreadable. What's wrong? Lucy B A There is a known problem with Access that may be causing this. You say the field is a text field, but if it is in fact (or has been) a memo field, the text will display oddly if you group by a memo field and your query has a JOIN on a field that's not indexed. Try altering the grouping so you don't group by the memo field. On the query window, change the Totals row for the memo column from Group By to First. You could also try adding an index to the fields you're using for the JOIN between the two tables. Technical telepathy: 09969636745 I am more inspired by Newton's apple tree than Adam's forbidden apple. Get numbers right this time, help the census with correct disability info!
