Creating GRaphical Indicators in MS Project

Hi again
Have a look at my latest posting , below I ahve placed a link to TechTrax website that explains the different types of graphical indicators within Project and how to set them up.
You can have "Traffic Light Indicators" for tasks overbudget or slipping dates, etc and Drop down menus (combo boxes) to allow the end user to only choose from the given list, e.g. Task Name , Dept ( Custom Field) etc.


http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=403

Thanks

Jamie
PS have a Merry Xmas and New Year to all the bloggers out there !

Blogging via Word 2007 Blog Editor !!!

Yes I created this using the Blog option now within Word 2007 and then setup my Blogger account within Word to Publish directly to my Blog site.

Give it a try as you then get much better editing and formatting options supported by Microsoft Word 2007.

Thanks
Jamie

MS ACCESS - Input mask syntax and examples

Many people struggle with input mask special characters so here is the help file from MS Access:

Valid input mask characters

Microsoft Access interprets characters in the InputMask property definition as shown in the following table. To define a literal character, enter any character other than those shown in the table, including spaces and symbols. To define one of the following characters as a literal character, precede that character with a backslash (\).

Character Description
0 Digit (0 through 9, entry required; plus [+] and minus [-] signs not allowed).
9 Digit or space (entry not required; plus and minus signs not allowed).
# Digit or space (entry not required; blank positions converted to spaces, plus and minus signs allowed).
L Letter (A through Z, entry required).
? Letter (A through Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
. , : ; - / Decimal placeholder and thousands, date, and time separators.
(The actual character used depends on the regional settings
< Causes all characters that follow to be converted to lowercase.
> Causes all characters that follow to be converted to uppercase.
! Causes the input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
\ Causes the character that follows to be displayed as a literal character. Used to display any of the characters listed in this table as literal characters (for example, \A is displayed as just A).

Password
Setting the InputMask property to the word Password creates a password entry text box. Any character typed in the text box is stored as the character but is displayed as an asterisk (*).
Input mask examples
The following table shows some useful input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) definitions and examples of values you can enter into them.
Input mask definition

Examples of values
(000) 000-0000
(206) 555-0248

(999) 999-9999!
(206) 555-0248
( ) 555-0248

(000) AAA-AAAA
(206) 555-TELE

#999
-20
2000

>L????L?000L0
GREENGR339M3
MAY R 452B7

>L0L 0L0
T2F 8M4

00000-9999
98115-
98115-3007

>LMaria
Pierre

ISBN 0-&&&&&&&&&-0
ISBN 1-55615-507-7
ISBN 0-13-964262-5

>LL00000-0000
DB51392-0493

Getting data into your Oulook Calendar from EXCEL !!!

I now use this to import my weekly/monthly calendar data across into my Outlook Calendar at home to keep me up to date with my latest whereababouts, thanks to Allister for the loan of this information .....

Wednesday, May 11, 2005 3:35 PM by Allister_Frost


Another handy way to add appointments to your calendar is by importing from Excel. To do this you first need to create an Excel file containing the appointment information Outlook needs. Your first row should contain columns titles which match Outlook's appointment fields. You don't need many fields to get started, just try the following for a basic import of all day appointments:

Cell A1: Subject
Cell B1: Start Date

Now add the data you'd like to import into your Outlook calendar under each of these column headings, for example: cell A2 could be "Bob's Birthday" and cell B2 could be "11/05/2005"
Now, here's the important bit. Highlight the entire range of cells that you've used (in my example this would be A1:B2) and give this range a name under Insert, Name, Define (or just type into the Name Box just above the top of column A). Outlook needs this range name so it knows where to find the data to import. Save your Excel file and close it.

Now just pop into Outlook and choose File, Import and Export. Then select Import from another program or file, hit Next, then choose Microsoft Excel and hit Next again. Now locate the Excel file you created earlier, hit Next, select your Calendar, press Next again and check that Outlook finds the correct Range Name in your Excel file. Now press Map Custom Fields to verify how Outlook is mapping your Excel column headers to its own Calendar Appointment fields, changing if necessary, then hit OK, then Finish.

If you've done everything right, your Outlook Calendar will now contain the appointments you created in Excel.

There are 22 fields you can import into Outlook in this way. These are: Subject, Start Date, Start Time, End Date, End Time, All day event, Reminder on/off, Reminder Date, Reminder Time, Meeting Organizer, Required Attendees, Optional Attendees, Meeting Resources, Billing Information, Categories, Description, Location, Mileage, Priority, Private, Sensitivity, Show time as.

Top tip: try importing in this way to your Task folder rather than your Calendar. This is a great way to move a list of 'to do' items from Excel or Project directly into your Outlook calendar.

SQL Query Criteria

Hi all,
See this link below for the Microsoft guide to Query criteria and SQL commands :

http://office.microsoft.com/en-gb/access/HP051885161033.aspx?pid=CH063608261033

Thanks

Jamie

next simple code sample !

'Code to set the current active cell value to something

Sub SetValue()
Worksheets("Sheet1").Activate
ActiveCell.Value = 35
End Sub

More Office Manuals ...

Dont forget to check out www.interquadbooks.co.uk for a now updated library of Computer Reference Books !


Jamie

Get your Code into Action ...

I almost forgot ....

Copy and Paste the code into the VB Editor window maybe on the first sheet or the This Workbook page, Save it !!! and then test it out by running the Code from the Run icon.


PS you could create an icon on the Toolbar to run the Macro later if you are feeling brave !!

Jamie

VBA for EXCEL Code Snippets

Hi all ,
I will be adding a little bit of code here and there to the BLOG in the form of Excel Code as I find out a little more on the subject as I go along the path to becoming a VB person !! [anorak not included]

Heres some code that uses the InputBox Function to then add a number of required additional worksheets to your Spreadsheet.

' Code to create additional Worksheets
Sub AddXWorksheets()
Dim myNum As String

myNum = Application.InputBox("Enter the number of additional WorkSheets required")
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=myNum
End Sub

Thankyou !

Need some sample data to play around with Excel 2007 ?

Then try Mr Excel ...

http://www.mrexcel.com/miraclesfiles.html

These files are from the Book : Excel 2007 Miracles Made Easy by Bill Jelen.

Download a Sudoku Solver here !

"Yes you can solve Sudoku in seconds with MS Excel !"

thanks to Mikes spreadsheet of VB magic !

download from :
http://www.mikeoldroyd.com/

PowerPoint Links

Need some professional backgrounds and templates, then check out these links below :
obviously a lot of these sites you will have to pay for the goods !!

http://www.ppted.com/

http://www.powerfinish.com/

Also check out Microsoft Office Online site for some free templates !

http://office.microsoft.com/en-gb/templates/results.aspx?qu=powerpoint&av=TPL000

And also check out :

http://www.presentationhelper.co.uk/free_powerpoint_template.htm

free templates !

Note in PowerPoint 2007 you can have access to a wide range of pre set templates and colour combinations, it makes a lot of the older templates look "out-dated" by comparison.

Thanks

Jamie

Microsoft At Work Website

Useful tips and tricks for Office lovers everywhere !

http://www.microsoft.com/uk/atwork/default.mspx

First look at Office 2007

Try this website for a first look at the advances with MS Office 2007 !
Its a huge new version with lots of great time saving and impressive features across all the applications :
http://www.microsoft.com/uk/office/newday/default.mspx?nav=v1xcsubmenuxi0&WT.mc_id=Online

Good luck

Office Books anyone ?

Web page detailing MS Office books available !

http://www.mousetrax.com/books.html#office

Monthly Training Journal

Hi try MouseTRax for training resources, I found some good help here on all MS Office related apps. You can subscribe for free to get a monthly magazine !

http://www.mousetrax.com/TechPage.html

Cheers
Have a nice Summer break for those of you going on holiday in 2007 !! :)

Jamie

Red RAG to a bull [ Red Amber Green Graphic Indicators]

Hi all
Just done a Project 2003 course and we discovered you can set up graphical indicators for your Project Tasks or overall Project status such as a red dot for Warning, Amber and green dot aswell !

I will document the process in a later Blog, but its a New Custom Field then choose Graphical indicators, when the user selects a drop down option such as R, A or G it uses a graphic instead of text.

A more interactive approach lets you copy a Number column such as Cost or Variance and Paste link it over the top of the indicator column so it keeps changing dependant on the numbers linked to it.

more to follow ...

Jamie

Outlook Keyboard Shortcuts

In a hurry with E-Mail , then peruse the shortcuts below :

Function keys

F1 -- Open Help
F3 -- Open Find dialog or Find pane.
F4 -- Expand dropdown menus, use arrow keys to select from list
F5 -- Refresh the current window (Outlook 2003) - Send/Receive in older versions
F6 -- Move to next window element
F7 -- Run spell check F9 -- Send/Receive


Number Keys

Ctrl+1 -- Inbox
Ctrl+2 -- Calendar
Ctrl+3 -- Contacts
Ctrl+4 -- Tasks
Ctrl+5 -- Notes
Ctrl+6 -- Folder list
Ctrl+7 -- Shortcuts
Ctrl+8 -- Journal
Ctrl+Y -- Folder picker dialog

Print a list of shortcut keys from within Word ..

Ever wondered how to get your own list of keyboard shortcuts ???

Read on .....

1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macros in box, click Word commands.
3. In the Macro name box, click ListCommands.
4. Click Run.
5. In the List Commands dialog box, click Current menu and keyboard settings.
6. Click OK.
On the File menu, click Print.

MOS SOS !!

Become MOS qualified and be the best !!

http://www.microsoft.com/learning/mcp/officespecialist/default.mspx

JJSURF

Demystify a few computer terms and get on with your life

Crabby to the rescue to bust some jargon !
http://office.microsoft.com/en-gb/help/HA010429491033.aspx

JJSURF and Crabby Office Lady.

Put Word to work for you!

Five handy tips for use with MS Word courtesy of Microsoft :
http://office.microsoft.com/en-gb/word/HA101154421033.aspx

JJSURF

Pretty Ribbons ....

Here's the Ribbon. Note the expandable Groups such as Clipboard and Font. The Office button at top left gives you access to the File Open, Save, Print commands plus others.

Home , Insert Page Layout etc are positioned across the top of the Ribbon.

Dont worry you will soon get used to it !!!

JJSURF

The Crabby Office Lady is alive and well !!!

Yes folks she's still around dishing out advise, like it or not check out her latest Office ravings below :

http://office.microsoft.com/en-gb/help/FX101679371033.aspx

JJSURF

You can use Shift Control + H to open a Word, Excel or PowerPoint document from within MS Outlook.. give it a try !!

JJSURF

Looking to Office 2007 .... the keyboard !

Keyboard shortcuts in the 2007 Office System Whatever your reason for preferring keyboard shortcuts, take this training course and get the low-down on new and and easier ways to use the keyboard with Microsoft Office 2007.

Downloads from Pearson Site for Excel

All the downloads, half the strain !!!

http://www.cpearson.com/excel/download.htm

Date Arithmetics !

Another link from C Pearson below for date calcs !

http://www.cpearson.com/excel/datearith.htm

VBA code for date related calcs

Try this link below if you are looking into Date calcs within Excel using VB Code, there is some useful stuff in here to try out !!

http://www.cpearson.com/excel/DateTimeVBA.htm


Jamie

From Project to PowerPoint !

click here for this add-in for Project 2003 you can quickly and easily create a PowerPoint 2003 presentation using information from a Project 2003 file.

Shortcuts for Internet Explorer 7

Hi
try the following to quickly get to things in IE7 :

Ctrl D - Add to favourites
Alt Z - for the Add to favourites dropdown
Ctrl + and - for Zoom factor
Ctrl shift I - for favourites menu
Ctrl shift H - for History
Ctrl shift J - for Feeds
Ctrl N - New window
F5 - reload/refresh screen

cheers !! Jamie

Generate some random text in Word for layout checking

Hi again !
my Word tip here courtesy of Microsofts Web cast on Word is to generate some text to play about within a Word doc for alignment testing , layout checking etc.

Use :
=rand(3,3) This will generate some text 3 paragraphs with 3 lines in each paragraph !

Use your own values as required. =rand(parag, lines)

VB Macros - How to speed them up and stop screen updates

When executing a macro you can watch everything that happens in the macro. This is good but when its all working it may be slowing down your macro. It can also be annoying to the user.

Use the following to disable the macro screen updating :

Application.ScreenUpdating = False

Then = True to switch it back on again.

Accessing other users Mailboxes in Outlook 2003

To create a link to someone elses mailbox do the following :

Go to Tools Options and then Email Accounts and View or Change your existing entry. presumably exchange server, then click onto Change - More Settings - Advanced then Add another mailbox by entering its correct name. and ok all screens.
You can now see the seconadry mailbox listed below yours [ you will need permissions from the owner setup to access the mailbox aswell !]

PDF add-in for Word 2007

Use this link below to download and install the add-in to allow you to create PDF's direct from your Word documents !!!

http://www.microsoft.com/downloads/details.aspx?familyid=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en&Hash=7%2bTy7ugkpmBwPRVL1Dq%2bud8w4FvNSDiedrzTno1LEPanuBQ5lr9saQcf21CDLnfwrBkog1DAUmX5D3bL74r2kg%3d%3d

Filter for unique records

Filter for unique records :

Select the column or click a cell in the range or list you want to filter.
On the Data menu, point to Filter, and then click Advanced Filter.

Do one of the following :

To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place.
To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference.
To select a cell, click Collapse Dialog to temporarily hide the dialog box. Select the cell on the worksheet, and then press Expand Dialog .
Select the Unique records only check box.

Thanks MS Help for this one !!

Finding and marking Duplicates via conditional Formatting

This is for the frustrated people who need to find and locate duplicate entries such as invoice numbers etc. in Excel


http://office.microsoft.com/en-us/excel/HA011366161033.aspx?pid=CL100570551033

Thanks

Do you need a TAP qualification ?

Then have a click on the link below ....

http://www.trainingfoundation.com/

Running in Runtime vs. Full Access 2003

Access 2003 Runtime is, in essence, Microsoft Access 2003, but with a few key features disabled. A user with Access 2003 Runtime installed instead of full Access can open and run an Access application (either an MDB file or, with a few extra steps, a SQL Server back-end database) but does not see the Database Window and cannot switch the view of any Access object to Design view.
The following features are not included as part of Access 2003 Runtime without additional customization:
The Database window
Title bar text/icon (can be specified by the MDB instead of by Access)
Some menu items, such as Open and Save in the File menu, and the entire View, Format, and Tools menus
Shortcut menus
Built-in toolbars
Access Help
Designers (including Filter by Form)
VBA editor
The Figure above shows two instances of the same minimal MDB application. The instance on the left is running using Access Runtime, and the instance on the right is running in full Access. Not only is the difference stark, but the Access Runtime instance does provide a direct UI for opening objects as the intent is for developers to create the UI for this purpose.
Figure 1. An Access Runtime instance of a sample application compared to a full Access instance of the same application
The developer of an application destined for an Access Runtime environment must create the application in such a way that the "missing" features of Access in the Access Runtime version are not obvious to the user.

Another Office website ( with Blogs !)

http://www.officezealot.com/

Best Practices When Using Access 2003 in a Multi-user Environment

see the link below:

http://msdn2.microsoft.com/en-us/library/aa167840(office.11).aspx

Deploying an MS Access Database ?

Try using the MS Access 2003 runtime version as it will NOT include Toolbars, menus and VB Editor so users will not be able to edit and hack into your application.

Use this link for more info:

http://msdn2.microsoft.com/en-us/library/aa167800(office.11).aspx#odc_acbasicsofruntime_introduction

Modules: Disable Shift key

MSAccess :

Modules: Disable Shift key: "Modules: Disable Shift keyAuthor(s)
Dev Ashish


(Q) I want to disable the Shift key so that users cannot bypass the Autoexec macro. How can I do this?
(A) Under Access 2.0, the only way would be to use a runtime version.
Under Access 95/97, you can set the AllowBypassKey property which disables Shift. Search Access help for all the info and code you'll need.
Also see General: Securing AllowBypassKey."

http://mvps.org/access/general/gen0040.htm

Delay or Wait Subroutine - MS Access

Delay or Wait Subroutine - MS Access: "You may also want to look at the Sleep API call. Once you set the API reference for it in
a module, it is real easy to call, just

Sleep LengthOfTime

The advantage of this, is that it releases resources for other apps running on the system
to use. You can find more information about this at

http://www.mvps.org/access/api/api0021.htm

--
Wayne Morgan
MS Access MVP"

The Wow starts now !!!

Yes folks Windows Vista is launched today so go and take a look at the link below :

please visit www.windowsvista.co.uk.

John Walkenbach - Excel Guru !

Check out Johns website, he is the author of many books including The Dummies guides - Excel VBA Programming for Dummies !

His site is: htttp://j-walk.com

also author of : "The Excel 2003 Bible" and "Excel 2003 Power Programming with VBA."

Keyboard Shortcuts are back in fashion

Most people love a few keyboard shortcuts to help speed up things in Word, Excel and other applications.
I will be compiling a list for each application although quite a few work on all packages.

So get ready to be aware of what F5 does in Excel and F9.
What does CTRL + Scroll wheel do in Excel ?
WOW it zooms in to the worksheet ( very handy for a trainer with the projector).

"Shortcuts are back in fashion as many will testify, if your cordless mouse battery packs in on the train you will wish you paid attention to those keyboard shortcuts !"

Using dynamic and static dates in Excel

hi again,
We all need to put dates and times into Excel to "timestamp" orders, deliveries and sales invoices etc.

The dynamic functions:
try putting 10,000 into a cell and format it with CTRL + 1 .
Set it to a date format. What date do you get ??? Strange but true !
Excel operates using the 1900 date system, so its 10,000 days after 1st Jan 1900. Hence 18th May 1927.

So with this in mind we can put calcs into your worksheets.

Try :

=TODAY() for todays date
=NOW() for current date and time

These will update automatically each time you recalc the sheet or open it next day !

Static Dates
If you want to time stamp your sheets use the following :

CTRL + : snapshot of current Date
CTRL + SHIFT + ; snapshot of current Time

Nicknames in Outlook

If your wondering how to save the predictive typing when you type in someones email address then its called "Nicknames" in Outlook. Its saved to a file called Outlook.NK2, so search your pc for this type of file using *.nk2

Copy it to a memory stick and keep a backup or transfer it to your new PC.

Jamie

Get into the groove ....

Use this link below to have a look at Microsoft Groove, a great new collaboration tool from Microsoft :

http://www.microsoft.com/uk/atwork/office/groove2007.mspx

Thanks
Jamie

MS Office Newsletter

Hi. I receive this newsletter every month and thought you might like to take a look. The latest issue can be found here - http://www.microsoft.com/uk/atwork/newsletter. If you like what you see and want to get it yourself just sign up!

Google Docs and Spreadsheets

Don't forget other options are closing in on Microsoft !
Have a try at Google's offerings !!!

http://docs.google.com/

A Blog of MS Office tips, tricks and golden nuggets !

Hi all, welcome to my blog !

I will be compiling an ongoing blog of MS Office training "extras" to complement the training courses I deliver for QA-IQ.

This will be split into Windows, Excel, Word, Access and PowerPoint, plus other packages like Project, OneNote etc.

Thank you !

Jamie