Tuesday, September 7th, 2010
Visual Basic 2008 Express – select a textbox contents
When you hand control back to a textbox in Visual Basic it’s useful if you also select the textbox contents so a user can just start typing over the current contents. Here’s how to do this – place the code in the Textbox_gotfocus event handler:
TextBox.SelectionStart = 0
TextBox.SelectionLength = Len(TextBox.Text)
How easy is that?
Labels: got focus event, gotfocus, select, textbox, Visual Basic
Saturday, August 21st, 2010
PowerPoint Save to CD doesn’t include the viewer
If you’ve tried to save a presentation to a CD from PowerPoint 2010, you might be misled by the onscreen instructions into believing that the PowerPoint Viewer application will be added to your CD.
This is anything but the case. The PowerPoint Viewer is not added to your CD so you won’t have it with you and if you take your PowerPoint presentation to a computer that doesn’t have the viewer on it and you don’t have an internet connection, you’re SOL. Poor show Microsoft!
What the presentation CD will contain is a HTML file that will launch when you place the CD in a computer. This contains a link to download the PowerPoint Viewer from the web. You have to go to the web site and then download and install it on the target machine – the Viewer cannot be run from the CD.
While most people won’t have a huge problem with this, it would be nice if Microsoft actually told the truth about the process and warned you what you need to have and to do. Otherwise many hapless folk are going to assume that it all works as it has in the past and arrive at presentation time without PowerPoint on the computer they are presenting on and having to download and install the viewer before they can begin. The situation will be worse still if an internet connection isn’t available.
So, as always, make sure to test your presentation thoroughly before you start. Know that you may need extra time to download the viewer or go ahead and download it ahead of time and take a copy with you that you can install on the computer you will be presenting on.
Labels: Microsoft PowerPoint 2010, package for CD, PowerPoint Viewer
Monday, August 2nd, 2010
Line feed in VBA textbox
When you need to create a line feed (new line) in a VBA text box you can do so in the string that you’re using to assemble your message using any one of a number of methods.
You can use Chr(13) which is an old style character conversion of ASCII character 13 which is the carriage return and line feed character. Or you can use vbNewLine or even vbCrLf.
But, try as you might, all you will get in your textbox is a silly paragraph marker and not a new line if you don’t set your textbox up as a multiline text box in its Properties. Don’t say I didn’t warn you!
Labels: carriage return, line feed, Microsoft, VBA
Thursday, July 22nd, 2010
Excel – cell reference in SumIF
I love SUMIF. It’s a handy tool for summarizing data. So, today I was checking how much I had invoiced a client – I have actual charges for stories I write in one column and the invoice numbers that these were charged under in another. I wanted a summary so I know the total of invoice 1 was $x and invoice 2 was $y.
SumIF is the tool to use. It goes like this:
=sumif(G1:G200,”=1″,H1:H200)
this sums the values in the range H1:H200 only if the corresponding values in the range G1:G200 is 1. So, it does the deed for Invoice #1. Repeat as required for invoices 2-20 – no way!
It is much easier if I place the numbers 1, 2, 3 and so on in cells of the worksheet and reference them in a single formula that can be copied rather than writing this 20 times… or 100 times… you get the picture… it’s simply not on to do this.
Problem is – how to refer to a cell in the SumIF function. This DOES NOT WORK! =sumif(G1:G200,”=N1″,H1:H200). Excel doesn’t see the N1 as a cell reference - it sees it as a value to match.
So, you have to write it differently:
=sumif($G$1:$G$200,”=”&N1,$H$1:$H$200
the “=”&N1 references the value in N1 as the one to match and the $ symbols are needed so the formula can be copied.
Labels: cell reference, Excel, formula, sumif
Monday, July 19th, 2010
Format Hyperlinks in PowerPoint 2010
If you’ve added an email link or a website URL to a PowerPoint slide, you’ll know that PowerPoint 2007 and PowerPoint 2010 automatically format the hyperlinks for you.
Because PowerPoint doesn’t use styles, it’s not obvious exactly how the formatting gets applied and how you control it.
The first thing to know is that you simply can’t control the underlining, it’s there and it’s there to stay. You can, however, change the colors used.
Click the Design tab > Colors > Create New Theme Colors, locate the Hyperlink color and change it to something different if desired. To control the Followed Hyperlink color, select that option and change it.
You now need to save your changes as a custom color scheme, so type a name for your color scheme and click Save.
Now the chosen hyperlink color and followed hyperlink color will be used to format the hyperlinks in your PowerPoint presentation.
Labels: formatting, Hyperlinks, PowerPoint, PowerPoint 2007, PowerPoint 2010
Monday, July 5th, 2010
Units of measure in PowerPoint
Ok, so its easy to change units of measure in Word by setting the Word Options to measure in inches or cm – depending on where you live. However, if you’re using PowerPoint don’t waste your time looking in PowerPoint for the setting – it ain’t there.
Instead, the measurements in PowerPoint are tied to your geography. If you live in the US you get inches, if you fess up to living else where you get your local units of measure. To change where you live, launch the Control Panel and look for a Regional settings option and set your location there.
For those of us who live in the US but who think imperial measurements suck big time and who yearn for the metrics of our childhood in the far off land of Aus, thanks to Microsoft we are s*** out of luck. You see, if I set my region as Australia or the Uk to get metrics, everything else goes pear shaped and Google starts serving up UK or Australian pages in preference to US ones, or I get £ by default in Excel. It’s all round not a good choice. So, I’ll have to suck it up and learn to embrace feet and inches – but provided you still call the land of your birth home chances are you’ll be just fine.
Labels: PowerPoint 2007, PowerPoint 2010, Units of measure, Word 2007, Word 2010
Thursday, July 1st, 2010
Don’t spell check this – Word 2010
I write a lot of code in Word documents, Excel spreadsheets and in PowerPoint slide shows. I’m not totally unusual in including macros in my files as well as detailing the macro code within the various documents.
However, as you can imagine, Office applications spit the dummy when they see code and everything gets squiggly lines under it. Which makes your documents look horrible when anyone who has Spell Check as you type enabled opens one of these documents.
In Word 2003, 2007 & 2010 I can stop proofing by selecting the code, double click on the proofing language in the program Status bar and reselect the language to use and click Do not check spelling or grammar. For some reason if you don’t reselect your language in Word 2007 and 2010 the Do not check spelling or grammar option isn’t applied – go figure. It took me a while to work this one out.
In PowerPoint 2010 and 2007 just choose Do not check spelling and it works fine. In PowerPoint 2003 where I spent this morning, there’s actually a language entry for No Proofing.. so select that and your text won’t be spell checked.
Labels: no proofing, PowerPoint, spell check, Word
Sunday, June 20th, 2010
Word 2010 – print the current page
If you’re used to using Word you might remember that, when you want to print just the current page you need to click in the page and then choose to print it. The current page isn’t the one you are looking at – necessarily – it is the one where the insertion point is located.
Fast forward to Word 2010 – all of a sudden – all bets are off. When you click File to move to the backstage view and click Print you see the print preview to your right. Whatever page shows there is the page that will be printed if you choose to print the current page. If that’s not the page you want to print, use the navigation tools to move to the page to print and then click Print.
It’s smarter and it really is how it should work. It won’t cause problems for new users because they don’t know how Word used to work, it’s us old users who need to rethink the logic here. Lucky for us though the page where the insertion point was located is the page that shows in Print preview by default.
Labels: print current page, print preview, Word 2010
Wednesday, June 16th, 2010
Problems with Excel formulas?
If you are having difficulty understanding how a formula is calculating in Excel – perhaps because it appears to give you the wrong results - you can step through it to see how it is working.
To do this, select the cell containing the formula and choose Tools > Formula Auditing > Evaluate Formula – in Excel 2007 find the Evaluate Formula option on the Formulas tab.
Click Evaluate and each time you do this, a portion of the formula will be evaluated and you can see it at work.
Use the Step In and Step Out options to see the actual values in place of any appropriate cell references.
This step by step processing should show you what is happening in your formula allowing you to troubleshoot any difficulties with it.
Labels: Excel 2003, Excel 2007, Excel 2010, Excel formulas, formula auditing, troubleshoot
Saturday, June 12th, 2010
Create your own Excel startup page
Here’s how to create a cool startup page for Excel – it has links to all your favorite workbooks so you can click on any of them to immediately open that file.
Step 1
To a new workbook, add one picture for every workbook you want to link to your startup page by choosing Insert > Picture > ClipArt. Size and arrange the images neatly. Choose Tools > Options > View tab and disable Gridlines, Row & Column Headers, Horizontal Scroll bar, Vertical Scroll bar, and Sheet Tabs.
Step 2
Right click one image, choose Hyperlink and link to a workbook you work with regularly. Type a description of the workbook in the Screentip dialog. Link each image to a workbook. Protect the worksheet by choosing Tools > Protection > Protect Sheet and click Ok.
Step 3
Choose File > Save As > and select your XLSTART folder which is likely to be: C:\Documents and Settings\<username> \Application Data\Microsoft\Excel\XLStart. Name your file menu.xls, close it, exit and restart Excel. The menu workbook will appear every time you launch Excel – giving you one-click access to your favourite workbooks.
Labels: default workbook, Excel, splash page, xlstart
Tuesday, June 8th, 2010
Inserting text in an Outlook 2007 email
The other day I came across an issue with Outlook which took quite a while to resolve and which really shouldn’t have done so.
In Outlook 2007, what I wanted to do was to insert an HTML page as a new email message. This is something that any organization running its own email mailing list may want to do.
Unfortunately, in Outlook 2007 there is no insert text option. You can attach things to outgoing emails, but on the face of it, you can’t insert them in the body of the email itself.
The solution is simple but, as I said, anything but obvious. To insert a file inside the body of an Outlook email message:
- from the Insert tab select Attach file.
- Browse to locate your file but don’t insert it at this stage. Instead, click the down-pointing arrow to the right of the Insert button.
- Look for the Insert As Text Option. This is what you need, click it and the file will be inserted as a text object in your Outlook email rather than being attached to it.
It’s a problem which should be far easier than this to solve.
Labels: insert HTML, new message, Outlook 2007 Insert text
Thursday, May 20th, 2010
Word 2010 opens in compatibility mode
New version of Word 2010 – same old WordArt. Yep, if you’ve previously altered your Normal.dotm file for Word 2007, chances are it is affecting your brand new 2010 version and all you can see is documents created in Compatibility mode.
To solve the problem search for Normal.dotm and either delete it or rename it. Then restart Word 2010 – nice huh? No more compatibility mode and all the cool features of word are available to you.
Labels: compatibility mode, Normal.dotm, Word 2010
Tuesday, May 4th, 2010
Custom bullet layouts in Microsoft Word 2007
I don’t like Word’s basic bullet design and generally I’ll want bullets of my own liking. For example I’ll use bullet lists to create checkbox lists for things I need to do.
Bullet style
To get a checkbox list working in Word 2007 requires a little bit of list knowhow.If you’ve created your list items you can simply select the list and, from the Home tab, select the Multilevel list button and choose Define new list style.
You need to use this rather than the Bullet Lists > Define new bullet options because otherwise you don’t get control over the tabs and Word’s tab settings for bullets don’t always make good sense.
The Define New List Style option gives you the ability to control all of this. Start by typing a name for your list so that you can find it easily again later on. The select Bullets which is a small button to switch from numbers to bullet style. Click Insert symbol and select Wingdings as your font.
You can then select a bullet style such as a shadowed box, select it and click Ok.This is applied as the bullet style.
Spacing
To adjust the spacing between the edge of the page and the bullet and the bullet and the text, click the Format button and choose Numbering. Set the Aligned at value to the distance that you want between the left edge of the page and the bullet itself. I set this to 0 inches.
Set the text indent at a value to the distance you want between the bullet and the text. This is a hanging indent so it ensures that all lines of text are wrapped automatically to the value you set. I like to set this to .75 inches if I’m using large bullets. Click Ok. You can then click Ok again to set this as your bullet style.
You can ignore the rest of the numbering if you’re simply using a one level bullet. Click Ok.
To apply this bullet style to your document click the multilevel list and select the list style from the list styles area of the dialog. If you hold your mouse over it, you’ll see the list style appear in a small dialog with the name that you gave it showing. Click it to apply it to your list.
Labels: bullet list, Custom, list, list style, Word 2007
Monday, March 29th, 2010
Flash movies and Windows 7
Today I had a particularly annoying half hour. It was a simple enough scenario, I wanted to play a Flash videos on my 64 Bit Windows 7 computer. Internet Explorer – Nada! Firefox – Nada! My Conclusion – this is not good…
For reasons better known to the folks at Adobe, the company has not yet updated its Flash player to support 64-bit browsers on 64-bit operating systems. The key to the problem is the combination operating system and browser you’re using.
A 64-bit browser on a 64-bit operating system won’t play Flash movies, but a 32-bit browser running on a 64-bit operating system will – with some tweaking.
So what do you do? The solution with a Windows 7 machine is deceptively simple. Select the Start button, choose Programs and then look for Internet Explorer. You will find there are two versions of Internet Explorer installed: Internet Explorer and Internet Explorer (64-Bit). Internet Explorer is the 32-bit browser so, if you run that and not the 64 bit version you’re half way to the solution.
What I’ve done is to replace the link to the Internet Explorer (64-Bit) browser on my system everywhere it appears such as in the Start menu and the Quick Launch Bar with the 32-bit version. I can live without the ‘benefits’ of a 64-bit browser if I can view Flash movies the way I expect them to play.
If you find the movies do not play or stop part way through, as I did, right click the movie and see which version of Flash Player you’re using. If it is version 10 then uninstall it (using Control Panel > Programs), and go find version 9 and install it in its place. You can download version 9 from here: http://kb2.adobe.com/cps/142/tn_14266.html – this is a source of archived older Flash Players.
So, if anyone from Adobe is listening, please can you get us a 64-bit Flash Player? While there is one available at labs.adobe.com for Linux there is nothing yet available for 64-bit browsers running on 64-bit operating systems for Windows or Mac. Given that so many people are switching to 64-bit operating systems, if only because it allows you to address great wads of memory, it really is time that the big name companies came to the party and provided basic tools compatible with these systems.
Labels: 32 bit browser, 64 bit browser, Flash movies, Internet Explorer, Windows 7
Monday, March 1st, 2010
How to install fonts in Windows 7
Ok. It should be dead easy to install fonts into Windows if you have been doing it since Windows 3.1. Until Windows 7 you went to the Control Panel, chose Fonts then installed them using the handy dandy dialog. Ok. Not so simple but it worked and it has done so for years.
Fast forward to Windows 7. No Font dialog as I remember it and no font install option. Ok, step backward, think, investigate, light bulb moment encountered.
In Windows 7 there are multiple ways to install fonts but seriously only one I’d bother using. Open the folder containing the font file, unzip it to see the TTF file if it is zipped then double click the font’s ttf file name. A dialog opens showing you what the font looks like and, at the top, you will find an Install button. Click it and seconds later the font is installed. Really? It’s how it always should have been done.
You can drag and drop the .ttf file into the fonts list via the Control Panel but seriously – why bother going to the effort. You can also right click on a font’s .ttf file and choose Install from the menu too but I like to check the font before installing so it is method #1 for me.
Labels: Control Panel, font, install, windows, Windows 7