Training | Formation [170 post(s)]

30 January 2011 - 13:18

Full outer join in Access

During a course a participant informed about Access' possibility to create a full outer join.
If you've gone into the topic of joins, you might have realised too there isn't really a feature doing that - there's an inner join, a left and right join, but no way in one go to get a full outer join.

Of course I didn't needed more to get my curiosity taking off, and I found a really well written and clarifying article from the hand of Gregory A. Larsen.

His simple device is: if it can't be done in one time, do it in 3 + one time: create the 3 distinct queries that are possible and with a 4th union query you just combine those results.
Cool !

Here's another example from the Northwind Database:

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers INNER JOIN Employees ON Customers.City = Employees.City


SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers LEFT JOIN Employees ON Customers.City = Employees.City
WHERE (((Employees.City) Is Null))


SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers RIGHT JOIN Employees ON Customers.City = Employees.City
WHERE (((Customers.City) Is Null))
(1) Comments ·
29 January 2011 - 01:48

Handling multi-selection lists in Access

One of the new features in Access 2007 was the possibility to implement multivalued fields, but that's not always going to help out for various reasons.
So handling data from lists where multiple selections are accepted remains a interesting challenge.

Here are some links I found helpful in implementing a solution for this:

(0) Comments ·
20 January 2011 - 05:42

Office Manuals for Download

The Mouse Training group has released their Office Manuals 2007 (2003 & 2000) are still available as well.

They form a good basis for those who are in to self training and are thus worth a download:

(6) Comments ·
19 January 2011 - 06:04
09 January 2011 - 14:26

Conditional Formatting for Excel charts

If you'd like to obtain some conditional formatting in your Excel charts, you'll have to as roll up the sleeves.
As there's no immediately feature for that - unlike of course the (greatly enhanced since Excel 2007) conditional formatting possibilities for cells- you'll have to proceed through VBA.

conditional formatted chart

Here are some links and samples to get you going:

If you really and rather go for something without VBA, then Andy Pope has some interesting scenarios for some specific situations:

(1) Comments ·
25 September 2010 - 07:41
31 August 2010 - 07:34

Flash and IE … grrrr !

I was struggling lately with some Flash which I couldn't get to work on IE, no matter which version.
It finally turned out to be linked to a wrong clsid attribute value in the object tag.

This worked for me:

< object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" 
width="640" height="480" title="Program" id="progamID" >

This did not work:


Now these class attributes were generated by Dreamweaver and I don't really have good understanding what they stand for; but the puzzling thing was that this attribute apparently affects browsers differently.

On the way to this solution I passed to several sites on the matter of using Flash on websites:

On publishing Flash with the help of Javascript:

(0) Comments ·
17 July 2010 - 10:44
03 May 2010 - 15:12

personal.xlsb in Excel 2007 not loading automatically anymore

This happened to me and I was really pondering what was going on.
But I remembered that when Excel has a crash, the next time it loads it might prompt to de-activate certain add-ins. And indeed that did the trick.

personal.xlsb is gone !!!

So this might help out if you encounter the same issue:

  • Click on the Office button and go to the Excel options
  • Choose Add-ins
  • Go to the combo box at the bottom entitled 'Manage'
  • See whether you find personal.xlsb add-in and enable it again.
(0) Comments ·
28 April 2010 - 15:23

Error handling and debugging in VBA

If you're after some good explanation and highlights in terms of error handling and debugging with VBA, I would suggest these:

(0) Comments ·
15 April 2010 - 15:21

Commands from prompt, batch files and backups

I was asked recently to do a course on DOS-commands and on RUN-commands, i.e. commands you can type at the run prompt.

And although I scoped it somewhat to the most used and/or basic ones, there are people coming up with huge lists of them:

This course really felt like 'closing a cycle', as my very first IT manual ever was a DOS manual which came along with a Commodore PC - one without even a harddisk, that was, back in 1986.
And speaking of DOS commands of course brings us to batch files.
If ever you want to try some:

Actually I'm using a batch files for my daily backups to external harddisks, with log files on top of it.... All thanks to good old DOS and a double click of a shortcut on my desktop :-)
Here's what I use:

set LogPath=e:\Backups\
set LogFileExt=.txt
set LogFileName=Backup%LogFileExt%
::use set MyLogFile=%date:~4% instead to remove the day of the week
::set MyLogFile=%date%
set MyLogFile=%date%
set MyLogFile=%MyLogFile:/=-%
set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
If NOT Exist "%MyLogFile%" goto:noseparator
echo.%Date% >>"%MyLogFile%"
echo.%Time% >>"%MyLogFile%"

xcopy d:\data g:\data /d/s/c/h/r/y/f/k >>"%MyLogFile%"
xcopy d:\2013Website g:\2013Website /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy d:\Graphics g:\Graphics /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"
xcopy d:\Images g:\Images /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"

xcopy e:\Audio h:\Audio /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\Video h:\Video /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\VideoAudioTraining h:\VideoAudioTraining /d/s/c/h/r/y/f/k  >>"%MyLogFile%"

(2) Comments ·
13 April 2010 - 10:21

Excel Pivot Tables unplugged: hide items in a filter combo

Excel pivot tables are just a fantastic tool. And you can even boost it in various ways.
Just some creative ideas I encountered while working on a problem with hiding items in a filter.

Sometimes you just need to think out of the box to get where you want to get. And that may mean create an extra column with new data, you can then integrate in your pivot table analysis.
As as sample of this approach: Filter an Excel Pivot table for upcoming dates.

And did you know there are some specific shortcuts and manipulations to sort and hide information in pivottables? You'll find a good article on this at following blog topic.

What I hadn't realised yet is that in pre-2007 versions, there was an option in the 'page field zone' to hide certain items from appearing in the combo box, as you can see in this article on .
Excel 2007 uses a new concept under the name of 'report filter' and as the 'multi-selection' feature has been added, this possibility to remove items from appearing in the combo has disappeared.
So if certain data shouldn't appear, you might have to go back to the source data and hide them there and thus use a 'out of the box' solution.

(0) Comments ·
12 April 2010 - 04:50

Access development: syntax of sql queries in VBA

One of the toughest things to do when coding VBA in Access development projects is coming to terms with SQL queries.
I've found following tips and tricks helpful :

SQL2VBA form

Allen Browne, one of the main authorities on the matter, proposes a form with VBA code which actually does the hard coding into VBA of any SQL string of your choice.
You'll find the code and procedure at his site:

Another thing you might have ran into is using query parameters into SQL strings. Well you can't basically, you must 'transform' them and here is how: If you run into the error "Queries: Too few parameters, expected n" - that's exactly what I'm referring too here :-).

And this might come in handy too: some basic testing for empty recordsets - as when your SQL query doesn't return records in the recordset, you'll off into problem land as well.
I've found this one useful to solve this issue:

(0) Comments ·
03 April 2010 - 07:14

Interesting code resources during recent Access Projects

Doing lots of database development in Access brought up all kind of interesting insights and links.
Just a selection of some of these encountered goodies :

(0) Comments ·
02 April 2010 - 16:36

Random values between two figures in Access

Ingenious all those solution maths-people come up with.
Take the problem to find a random value between two integers.

Well you take the interval between the two figures you have in mind, add one, then multiply with a random value and add the minimum of the figures you had in mind. Just rendering it as an integer and you're done ! If that sounds too much like maths.... well ... it is :-)

Given that the random function in Access is rnd() ( you obtain a decimal between 0 and 1) you get something like this to obtain a number between 10 and 100

Int ((100 - 10 + 1) * Rnd + 10)
For a full explanation go to
(0) Comments ·
30 March 2010 - 13:42

How to find (the first) capital(s) in an Excel cell?

Another interesting topic coming up through one of my clients.

I've found an enlightened discussion at one of these great Excel resource sites:
Especially the breakdown of the described first function and the elaboration on arrays is worth reading.

These are the two takes I've been going for:


The trick consists in finding the ASCII value of the first capital of a text-string composed out of the cell containing the evaluated text value combined with the 26 capitals of the Alphabet.

And then also a user defined function in VBA:

Function FirstCap(Cell As Range)
   For FirstCap = 1 To Len(Cell.Value)
      If Mid(Cell.Value, FirstCap, 1) Like "[A-Z]" Then Exit For
   Next FirstCap
End Function
' And =IF(firstcap(A1)>LEN(A1),NA(),firstcap(A1))
(1) Comments ·
26 March 2010 - 17:22

Corruptions during Access development

During a quite substantial development project in Access recently, I initially started developing in Access 2007. But as downgrading to the Access 2003 version the client used posed all kind of issues, I gave up and continued the development in Access 2003.

So far so good - well I thought, but not till rapidly ran into something I had never experienced. All kind of forms suddenly didn't want to load anymore as they became corrupted.
Randomly, apparently, although frequently linked to removing labels or moving fields with their labels.
And mostly with the laconic error message: "there isn't enough memory to perform this operation" .
It became really a desperating situation.... grrr :-(

I then found a message on which really saved me and pointed me to a method to save a form as a text file (!!!) and then retrieve it again... Although it didn't always work, this and regular (obsessional ;-) ) backups made me swim across this river.

The trick goes as follows. In the VIsual Basic Immediate Windows, use following code to save the form as a text file:
Application.SaveAsText acForm, "YourFormNameHere", "C:\Path\Filename.txt"
Then delete your corrupted form and retrieve it back using following code:
Application.LoadFromText acForm, "NewFormNameHere", "C:\Path\Filename.txt"

If the issue still continues, try doing a Compact and Repair before retrieving the form.
And a further comment on the forum pointing to a possible use of this technique:

If you have to provide documentation for your application you can use this technique to create text files that contain all the information about your forms. Then should anyone modify your form at any point or it becomes corrupt you can restore it from the text file.
You can be really clever and create a support database that would be similar to the MSysObjects table that contains a list of all your objects and store the contents of the text files into a memo field.

To close: you'll find a database corruption repair guide at
And some good tips to prevent corruption at the famous Allen Browns corner:

(0) Comments ·
24 March 2010 - 17:25

Find the cell with the lowest value in an Excel sheet

The beauty of Excel is that there are many ways to achieve the same thing.
Take this topic e.g. : how to find the cell with the lowest value in an Excel sheet?

If you're after the exact location of that cell, and you can limit your search to a specific column or row with results, you could use a combination of the ADDRESS() and the MATCH() function which would give you something like:


Or you might be more picky as to which type of information you'd like to withdraw from that cell containing the minimum value -
it could be the range address, but also it's content, or it's column position, it's formatting or it's locked status. That's something you can do with the CELL() function -
it's first argument determines the type of info you'll get as a result. With our two examples:

to obtain the cell addresses::

to obtain the cell values::

to obtain the cell lock status::

If you need this approach regularly, you could also take the VBA road and insert a module with your own user defined function.
Like the one here found at :

Function MinCellRef(dsRange As Range)
Dim c As Range
Dim MinCell As Single

MinCell = Application.WorksheetFunction.Min(dsRange)

For Each c In dsRange
If c.Value = MinCell Then
MinCellRef = c.Address
Exit Function
End If
Next c
End Function

And if you want some VBA solution which highlights and formats the cell containing the minimum value, that's possible too :

(0) Comments ·
20 March 2010 - 13:14

How to download flash from a web site

You can download flash videos from a website either with dedicated tools for that purpose or by going into the source code and tracking the path allowing you to then manually download the flash file e.g.
Obviously the latter requires a bit more technical experience. And even if you have that, it can still be a time consuming job, as many flash files are often encapsulated in all kind of codings.

A good place to start probably is wikiHow: .
In terms of the dedicated tools I've personally good experiences with Replay Media Catcher - amongst others I've tried are Fast Video Download, a Firefox plugin and Flash Saver (

On the coding side: you'll find a very good description and approach with Firefox, free but a bit more technical, on following website:

ps: Regarding Fast Video Download: although it's a great tool, you might run into the problem of dialog boxes starting to open extremely slow:

(0) Comments ·
15 March 2010 - 17:10

Autosave in Excel 2007

For those who are searching the specific AutoSave feature in Excel: search no longer - it is really gone.
Well yes it was replaced by the AutoRecover feature, but it's behavior is sligthly different.

You'll need to use an add-in to get exactly what you had before, i.e. possiblity to autosave your Excel file at a specific interval on a specific place (under a specific name ?).
I've found one here:

(0) Comments ·
10 March 2010 - 17:31

Templates location configuration in office 2007

Template location properties

If you're using a lot of templates in Office, it's a good idea to organize things a bit. This means changing the default template location and putting your templates in subfolders.
Because if you don't specify a specific location for your templates yourself, Office saves them in a very hard to tackle place in the profile folder of the user.
And if you have created sub-folders in the main template folder, Office is then going to show or represent these as separate tabs in the template dialog window.

In Office 2007 this still happens as in the older versions of the suite: rather mysteriously that is !
Because you have to go into the Word options to find the locations button - and only there ! - as it's unavailable in Excel or Powerpoint ...
So just go to the Advanced Options in the Word options dialog window, at the very bottom, you'll find the button File Locations giving you access to the default folders for the User Templates, as well as the Work group templates. Just choose the parent folder for you templates and presto.

Template location dialog box

All you have to do next is saving your Excel, Powerpoint or Word templates in this folder, or in sub-folders of this folder, and they will then automatically appear in the Template window.

(0) Comments ·
03 March 2010 - 14:57

Mailmerge with groups

Ever wondered or needed to do a mailmerge in MS Word with multiple group levels?
It is possible indeed; it involves customizing the merge fields a bit, but if that's what you need, you'll find the whole procedure through following links:

(0) Comments ·
26 February 2010 - 13:41

Video editing

I had to go into the topic of video editing recently and come across this nice overview of tools for the matter:

I got the best results with AVS Video Converter in terms of reframing, cutting and merging parts of video materials. But I really liked the user interface and the friedliness of a tool not listed in this article: FLV Editor Pro - I only had problems in properly merging the pieces as this didn't happened precisely enough for what I needed... in my experience and with my limited skills in this area, that is :-)

Two other tools which I've used and tried out are Replay Converter, the best tool in my experience in terms of allowing recording from web pages - this tool really allowed recording flash from some sites where all other tools failed.
And also Super, which I've found the best tool for converting purposes; not the most user friendly, but a really Super one !

(0) Comments ·
24 February 2010 - 07:28

Transparency on iframes in IE7

If you work with iframes, you might have realised already there's an issue with the transparency settings.

Especially in the IE browsers even if you give specific CSS instructions to have the body background colour rendered transparent, you won't get what you're after - the background is still going to remain opaque.
Following article describes the issue and solution nicely : - in essence: you need to add allowtransparency="true" to the iframe HTML ; and of course also set the background transparency to the body CSS tagselector :-)

(0) Comments ·
10 February 2010 - 05:23

How to prevent editing when grouping sheets in Excel ?

This question came up during one of my Excel courses - clearly something to be done with VBA, so I googled it and found following code at

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     If ActiveWindow.SelectedSheets.Count > 1 Then
     With Application

          .EnableEvents = False
          MsgBox "Oh, please don't group the sheets..."
          ActiveWindow.SelectedSheets(1).Select 'Ungroups
          .EnableEvents = True
          End With
     End If
End Sub

ps: for some interesting elaboration on Events go here: and here :

(1) Comments ·
08 February 2010 - 11:04

Difference between div and span element when coding CSS

I remember having prepared an blog post covering this topic, after a question during one of my courses... However it never saw the light because there's always so many things to accomplish at the same given time.
But never despair as it says: recently an article was published on Sitepoint on this matter. Which triggered also an interesting debate.

Nice reading if you're in the CSS business.

(0) Comments ·
03 January 2010 - 13:59

Merge many word documents into one single document

This sounds like something fundamental, but for some reason I never really needed something to merge many word documents into one new document.
Till recently that is.

You can of course go the direction of the Master/Child documents approach in Outline view.
For more information on this you could try these links:

But if you really envisage one flat document, apparently you need a macro to do this properly - you'll need to finetune it with the specific path containing your Word documents as well as pay attention to the specific extensions the documents have :

Sub MergeDocs()
    Dim rng As Range
    Dim MainDoc As Document
    Dim strFile As String
    Const strFolder = "C:\Courses\Word\" 'change asper your need
    Set MainDoc = Documents.Add
    strFile = Dir$(strFolder & "*.doc") ' can change to *.docx or *.rtf
    Do Until strFile = ""
        Set rng = MainDoc.Range
        rng.Collapse wdCollapseEnd
        rng.InsertFile strFolder & strFile
        strFile = Dir$()
End Sub
(0) Comments ·
30 December 2009 - 13:26

Adding a public folder to Outlook favorites

Add to Favorites

If you right click a public folder in Outlook, and choose the option to 'Add to favorites', you'll add this favorite to the Public folder favorites only.
But if you thought or wanted it to appear in the main mailbox favorites, you'll notice or have noticed already that nothing was actually happened.

No need to feel defeated any more, as the solution is really simple, although unfortunately not 'obvious' at all:
simply right click the Public folder favorites folder shortcut that you created earlier, and then select 'Add to favorites' again and hey presto, the favorite is now going to show up in your main mailbox favorites area.

(0) Comments ·
29 December 2009 - 08:05

On the topic of manuals, books, training and … pedagogy

When doing trainings, mostly IT trainings, a couple of questions pop-up from time to time.
Like : do you have some suggestions for a good book? Or which book do you use during your training? Or how to you organize your training?

Well firstly I have to say I don't really follow or use a manual or book when I teach.
At several occasions over the years, people personally expressed their gratitude for that, so this only fortified this conscious choice of course.

Of course this doesn't mean that I don't use a structure and/or the structure doesn't or couldn't match a manual or book.
I actually work with detailed mind-mapped structures for all of my course contents - this gives me great flexibility and the possibility to concentrate fully on the learning process itself.
I always start a course by feeling the pulse and asking participants what they want to learn. I then present the overall course contents - tailored and adapted to the level and specific questions or desiderata.
But I keep a great amount of margin to be able to surf and adapt to what the group dynamics actually bring in - in my personal view it's exactly here the magic of the pedagogical process hides.

Even when examples and structure may be identical, I always inspire questions, reflections and exchange to a maximum possible and address them with mind-mapped content.
Pedagogically that implies that I envisage to focus energy on the learning process itself more and that yet another input source as a book, manual or powerpoint content generally isn't really appropriate.
So practically every course is rather or completely different as every group and group dynamic process 'of course' always differentiates.

Now I do understand the (psychological?) need of having a written manual or book and generally refer to those as an 'after course implementation' resource.
But then it always seems hard to me to come up with suggestions which fully satisfy me - what is a good book anyway?
But even if haven't fully figured out what a 'perfect IT Manual or book' for me would be I know in which direction I'm looking: resources which focus on conceptual learning and transfer of knowledge, which don't contain overloads of written text, and use a lot of schemes and mappings, as this is the way we program our brains basically ...

Some miscellaneous links in the margin of this topic

(0) Comments ·
28 December 2009 - 07:14

Reversed Engineering on a pivot table in Excel

When you've created a pivot table in Excel, you can drill down on the results by double clicking on them - Excel then creates a new worksheet with the individual lines behind the result.
If you do so with the grand totals, all the lines, i.e. the full source sheet will be inserted into the new worksheet.

results in a pivot table like format without being a pivot table

So far so good.
But what if you have to do it the other way? I.e. if you have the results in a pivot table like format, e.g. provided by your database administrator in a simple Excel sheet - you can't do the drill down as you don't really have a pivot table. And you need to recompose the individual line behind the construction?
A simple reconstruction with some images of the starting and end situation will probably make the point clearer.

end result of the operation

To get to the desired result you'll need to do some 'reversed engineering' which is possible via the Pivot Table and Chart Wizard (which is not directly available any-more from the ribbons in Excel 2007, so if you use this version, you need to add this feature through the Customized Option for the Quick Access Toolbar). You'll find more information on this feature from the Microsoft Office site.

The wizard provides in an option to create a Pivot Table from a 'Multiple Consolidated range' of cells - even if you select just one range of data (with row and column labels), Excel is going to treat the data very much like in the Data/Consolidate feature. Each column become a data series, which is then used to compute a new pivot table. And even it's very near or exactly the same as the original data list, you know have a pivot table with access to the grand totals which you can double click to.... obtain the envisage individual results.

The Spreadsheet Page describes the procedure with screenshots for older Excel versions, but also provides in a macro automatising the whole procedure, at the following link.

Sub ReversePivotTable()
'Before running this, make sure you have a summary table with column headers.
'The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long

On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
	MsgBox "Select a cell within the summary table.", vbCritical
	Exit Sub
End If
Set OutputRange = Application.InputBox(prompt:="Select a cell for the output", Type:=8)
'Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
For r = 2 To SummaryTable.Rows.Count
  For c = 2 To SummaryTable.Columns.Count
   OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
   OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
   OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
   OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
   OutRow = OutRow + 1
  Next c
Next r
End Sub
(0) Comments ·
23 December 2009 - 03:38

How to extract images from mail messages and / or PowerPoint slideshows?

This is actually very simple, although perhaps not that obvious at first sight (or trial).
The answer is : Save As Html.

save as html

And that's valid for any Office document basically.
Have a mail message with some nice images you'd like to use? Just go to the File menu or Office button and choose to 'Save as' - then change the option 'Save as type' to Web page - html. The system is going to produce an Html file in the folder you selected as well as a subfolder with all the 'dependant' files for the html page to render properly, like CSS files and graphic files. And that's it !

Same procedure for other programs like e.g. PowerPoint slideshows: just save it to you harddisk, open it in PowerPoint, then save as Html and presto.

(0) Comments ·
15 December 2009 - 04:16

Import WordPerfect 4 Files into Microsoft Word

In the prolongation of a previously published entree on text conversion problems with Word, I received a mail from someone who was searching for the old Wordperfect 4.2 conversion file, not available anymore in the last MS Office releases (WPFT432.CNV).

The place to be is an article called 'How to Import WordPerfect Files into Microsoft Word', published on
Somewhere within that page, you can download A replacement set of conversion filters for use by Microsoft Word.
There are the filters for the WP 5.X version, but also for WP 4.0 - the procedure described for WP 4.2 is the same, so you only need to copy the files to the described location (in a typical install under Win XP: C:\Program Files\Common Files\Microsoft Shared\TextConv).

However to get the converter presented in Word along with the other converters, you need to tweak the registry. To ease the process, you could safe following lines to a text file, save it as wp42update.reg or something; just double click the file will insert these lines in the appropriate places in the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\WrdPrfct4Dos]
"Path"="C:\\Program Files\\Common Files\\Microsoft Shared\\Textconv\\wpft432.cnv"
"Name"="WordPerfect 4.x"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Text Converters\Import\WrdPrfct4Dos\Options]
"Name"="WordPerfect 4.x"

Enjoy the nostalgia, as yes indeed where are the amazing times of the blue screened function keys driven interface of WP 4.2 and WP 5 :-)

(5) Comments ·
14 December 2009 - 16:04

Powerpoint Handouts

Handouts in PowerPoint can be printed through the print dialog box and settings - you have a certain control over their formatting through the handouts master.

If you're looking out for more flexibility to print out handouts, your best first step probably is creating handouts in Word.
To do so all you need is the SendTo option in the File Menu for pre-Powerpoint 2007 users or the Publish option from Powerpoint 2007 onwards. The wizard actually offers a series of options as how to layout your pages too.
You'll find a series of screenshots on these options at

And If you want even more control then have a look at the add-on available from
The site offers a handout wizard allowing you to create your own customized layouts and more.

But coming back on the PowerPoint - Word wizard, it actually produces rather small thumbnails of the slide in the final Word document.
I've found a macro on to resize the graphics and tweaked it a bit as to make it work on the whole document for any number of slides.
Just copy the code into a module behind the document or into the 'Normal' project or template of your current software version.

custom handouts [Read more on Powerpoint Handouts...]
09 December 2009 - 16:12

Propagation of Custom Animation in Powerpoint

Once you have created a custom animation in PowerPoint, you might want to apply the same animation on the other slides. But... there is actually no way to propagate a custom animation in PowerPoint. No despair however, you can avoid having to redo the animation on each of your slides by applying your animation to a placeholder on the master.

Here's the procedure in a nutshell:

  • Open the Master View and select the body text placeholder (or placeholder of your choice depending on the Powerpoint version)
  • Apply the Custom Animation your choice, e.g. making text fly in and ensuring each of the bulleted topics flies in 'after previous' to then 'dim out' when going to the next one.
  • Close the Master View and save.
  • Remove all Custom Animations applied on individual slides now having become redundant.
  • Now play the slide show and check if all slides behave as wanted - if not try to 're-apply' the chosen layout to the slide or choose the correct layout.
propagate animations in PWP

Ps1 : You cannot delete animations applied by the Master slide through the custom animation features on a slide.
If you have PowerPoint 2002 or later you can take benefit of the multiple master feature to scope the animations throughout the slides and work with different masters to apply different or no custom animation asper the need.
And from Powerpoint 2007 onwards, there are much more placeholders available on the masters, so that gives margin for even more flexible solutions by choosing animation for specific placeholders.

Ps 2 For more information on this topic :

(0) Comments ·
06 December 2009 - 15:12

On windows explorer tooltips and thumbnails

It's amazing how many different settings can be encountered on computers ... and how confusing or complex it can all turn out for the 'normal' end-user.

Like recently during a PowerPoint course in a course room with identical computers. When browsing images to insert into the slides, some participants see the thumbnails of the images, as well as the pixel dimensions, others see thumbnails without the pixel dimensions, and still others don't seem to be able to activate the thumbnails altogether.

To start with the last one: if you don't see the thumbnails for images when you should, it's most likely linked to a setting in the folder options Windows Explorer - in the View Options, you have to make sure the option 'Always show icons, never thumbnails' is un-checked.
Remark that the word 'Thumbnails is not used anymore in Vista, but Large and Small Miniatures should then just do what they say.
If ever this is not the cause of not seeing thumbnails in your folders, you could try-out the procedure as described by the Microsoft Knowledgebase, as indeed there are issues with thumbnails:

The specific information (size, pixel dimensions, file type, etc) appearing in the tooltips are actually dependant on some registry settings; it seems a bit of a mystery how some of the settings sometimes get updated and sometimes not, probably to installation of certain software, but if you want to master it yourself you'll find the keys in following articles:

Happy tooltipping !

(0) Comments ·
05 December 2009 - 03:45

What is a browser?

I've always considered asking questions as a fundamental key in any teaching work.
Good questions have tremendous potential in induce insight.

And the most basic questions may sometimes be very well the most powerful triggers to insight.
That's what some people from Google have proven by going out and asking this very simple question to the general public on the street :

what is a browser?

The experiment was done in New York, but taken over in some other cities. They all proved to be very enlightening as to how few people really can answer this question properly.
An a eye-opener for the webdeveloper indeed, as in the light of this experiment, the daily concerns of browser compatibility, the decision making process towards CSS, the strive for webstandards, and so on seem suddenly to become so ... well 'futile'...
And even if all those of us, in our efforts to take our work to high standards, know these issues aren't futile at all, it's always good to practice empathy and stay in tune with the 'end user' - isn't ? :-)

browsers anno 2009

But see for yourself if you haven't done so:

Sitepoint elaborates on this experiment through one of it's Ezines.
Google itself was so struck by the results that it decided to launch a website explaining the answer and especially what's the difference between a browser and a search engine. The site has some interesting links to a browser historical line and other browser stuff.

(0) Comments ·
02 December 2009 - 09:04

Problems with printing backgrounds in Powerpoint

printing in PWP

When printing slides in PowerPoint, you might have encountered problems with the backgrounds - mostly being too dark or so and making text unreadable.
You might have realised also there's not such an option (anymore ?) in PowerPoint as 'omitting the background graphics' from printing.

The first way out in this situation is always to try print in 'grey tones' (this should inverse difficult to read text also) - the option is available from the print dialog box, in the 'print what section'.
If still in problem try it again with the 'pure black and white' option - available at the same place in the print dialog box.

You could of course also create a special master for printing purposes only, where you omit the background and/or choose a different colour scheme. You would then have to save the presentation twice, once for the screen (eg presentation.pptx) and for printing (eg presentation_prn.pptx) or if you use PWP 2003 +, create custom shows within your presentation, one for printing and one for doing the presentation as such. As from PWP 2003 onwards, there's the possibility to use multiple masters in the same presentation.

(1) Comments ·
01 December 2009 - 05:25

Copy an Outlook folder structure

To copy an Outlook folder structure, e.g. for back-up and filing purposes, do the following:

  1. Export the folder with subfolders and content to an pst file (file > import/export to pst file)
  2. Load the file (File open/ outlook data file)
  3. Archive the folder (right click - Auto archive) by setting the condition to 'items older than today, or one day' and by ticking the remove permanently box
  4. compact the pst file to reduce it's size to normal proportions again

For more details see:

(0) Comments ·
13 November 2009 - 15:17

Newness in MS Project 2007 and Task driver issue

MS Project 2007 hasn't "ribonized" yet and hasn't really fundamental newness. The concepts of this software have been outlined since many years now.

Amongst the new features I can appreciate though in this version are:

task drivers
  • Highlighting changes: the program highlights any changing values instantaneously as you type
  • Multiple Edit Undo: long time awaited for and finally implemented, this option is a real relief
  • Visual reports: the old 'reports' are still available and have only been lightly adapted, however MS Project now comes with a nice feature to create pivot table reports and charts with the click of the mouse
  • Task drivers : a task pane showing you the driving factors or tasks for any selected tasks, especially handy in larger and complexer projects - there is a kind of bug though which can result in task drivers become unavailable - through this forum thread I discovered the solution is to simply delete the global.mpt (it is automatically recreated when the program restarts)

You'll find a full list of the new features on Microsoft's Tech site.

visual reports
(0) Comments ·
10 November 2009 - 15:13

Connection speed between front-end and back-end in Access

If you're experiencing connection speed issues or weird behaviour between your Access front-end and the back-end system, be it an Access, SQL-server or another database, here are some basic points to check out:

  • Is there a primary key set on all tables in BE?
  • Aren't there too many indexes set on some of the tables used ?
  • Can the number of queries and requests be reduced? Comboboxes & listboxes are a good place to start from.
  • Have main and subforms been linked through the master - child link interface from the form builder ? If so you try making the linking directly through SQL / recordset in the subform data source property
  • Did you try to Compact & Repair?
  • Has the autocorrect feature been disabled?
  • Have all service packs been installed?
  • Did you try it out with materialized tables in stead of (sub) queries?
  • Have you tried to see the difference when you remove subdatasheets feature in tabels?
  • Have you checked latest ODBC driver and/or some configuration options
  • Did you use Option Explicit in the VBA modules?

Just to warm up really :-)
As usual this is just the beginning, you'll find more tips and hints over here:

(6) Comments ·
04 November 2009 - 10:23

Including null values in Access

If ever you've used the 'not' operator (also '<>') in Access you might have realised you'll get unaccepted results if you're not including a specific search for 'null' values.
So always include 'OR Null' as follows:

<> "beverages" OR null

You'll find some screenshots on following site.

For certain expressions where you want to include null values, you bump against issues triggered off by these null values. In that case you can use the Nz () function.
From the Microsoft support site - you 'll find some examples there too.

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

Click here for another example.

And if you're after some VBA code to evaluate for Null values, here you go.

(0) Comments ·
02 November 2009 - 15:38

Refresh ODBC Linked Tables in Access

For an Access development job, I needed some code to make sure the linked tables in the backend should be updated automatically - and if the back-end or front-end had been moved, the users could then be prompted for a new location.

This is the code I used from Stackoverflow, the question and answer site for programmers, where Renaud Bompuis proposed a couple of solutions.

Here is the link : I just had to add some coding to exclude some funny temporary system table which triggered the code into an error and thus debuggin mode.

Function fGetLinkedTables() As Collection 'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As Database
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        With tdf
             ' exlude wrong SysTable SYSObjects ~TMPCLP322681
             If Len(.Connect) > 0 And Left$(.Name, 1) <> "~" Then
               If Left$(.Connect, 4) = "ODBC" Then
                'collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                End If
            End If
        End With
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function
Read on if you want more solutions. [Read more on Refresh ODBC Linked Tables in Access...]
(2) Comments ·

Mail merge with Word and Access 2007 issue

Recently I bumped into weird problem with Office 2007, whereby Word didn't recognize data from an Access database during a mailmerge.
Fairly basic but... no way.

What would we do without the exchange on the internet ?
Peter Jamieson from published the answer on, a free online training platform I consult regularly:

There are several types of query that Word 2007 cannot "see", or does not execute as you might expect, when you try to use them as a Mail Merge data source using the default connection method for Access (which is OLE DB), including:

  • a. parameter queries
  • b. queries that invoke user-defined Access VBA functions
  • c. queries that invoke some of the financial series functions and one or two other "non-user defined" VBA functions such as replace()
  • d. queries that use LIKE and wildcards

In most cases, the quickest way to get around these problems is to change the connection method of the connection to DDE - in Word 2007 - check Office button->Word options->Advanced->Confirm file format conversion on open (near the bottom), go through the connection process again, and you should see an additional Confirm Data Source dialog box. If you see an entry like "MS Access databases via DDE", select it. If you only see "OLE DB database files" (typically if you are using a .accdb rather than a .mdb source), check Show all, then locate and select that DDE option, and continue.

(3) Comments ·
30 October 2009 - 14:11

Datepicker in Access

One of the newnesses in Access 2007 is the fact that a datepicker functionality has been integrated for date/time fields.

If you don't use this version of Access and/or if you're after something with just a bit more functionalities (like an option for easy scrolling through years | months asper the need), then you're still bound to use some VBA coding.
You'll find a nice module with code over here at:

(0) Comments ·
10 October 2009 - 15:35

Customizing the Ribbon in Access 2007 and Office 2007

If you wondered how to customize the ribbons in either Office 2007 or especially also Access 2007, here are some links which could be clarifying.
Ribbons are based on XML now, and so that makes the process more technical then e.g. customizing a toolbar before.



(0) Comments ·
28 September 2009 - 13:46

Limitations in MS Project

There are a couple of limitations in MS Project which probably will never 'limit' you, till the day of course you really need to go beyond.

One such example could be the latest day till which you can schedule. That hasn't changed in MS Project 2007 and still is 31 dec 2049 (and indeed people who need such days for good reasons exist, as I discovered today through a course participant working on dismantling nuclear power stations !).

A limitation which I always found, well .. limiting, is the maximum space of about 10 to 12cm you can code for the legend text box, or the limitation of using 3 lines in this text box.
Just another example... so if ever you want a nice overview of some limitations in MS Project 2003, you'll find them at Microsoft Office Online. Luka Gospodnetic lists simular, but not always matching figures on his blog for MS Project 2007.

(0) Comments ·
20 September 2009 - 12:12

Centering horizontal menu in CSS

Rob Glazebrook recently posted a sample of a super simple horizontal navigation bar with CSS.
It's always great to have simplicity - something which isn't always the case, especially with CSS - so I'm just promoting the approach here as well :-)

Along the post someone asked for a solution to center the horizontal menu in CSS.
Well centering in CSS is also something simple generally; you basically put the object to center in a container which you style with auto-left and right margins. Something like

#menuHolder {margin:0 auto;}

Of course with menus you can always expect 'unexpected' behavior; so for some robust techniques on the matter Stu Nicholls is your man - he has been tireless in publishing huge amounts of code on his site; You'll find two very simple implementations of this technique over here:

(0) Comments ·
14 September 2009 - 12:07

Shared mode in an Access database - who’s in?

If you're using Access in shared mode, you might be wondering who's currently using the database.

Well this information is actually stored in the *.ldb file (pre Access 2007) or *.laccdb file (Access 2007).
This file automatically opens when you open your Access database in shared mode (this is the default - you can open a database in Exclusive Mode, through the options of the File/Open window) - it manages users and record locking in shared mode.
And you can actually read this file, you only need a more advanced text editor. However, Microsoft offers a special tool, called Jet utilities, containing a LDB viewer which withdraws more exact information about your connected users; you can download it here : It also seems to working under Access 2007.

For any problems with the lockfile, you might go through this article:

And if you want to list users and access through VBA code, this is possible to; for a nice module on this topic go here:

(1) Comments ·
09 September 2009 - 04:39

Default database & templates in Access 2007

When you'd like to create a default database in Access 2007, containing your preferences, settings, tables, forms, modules and so on of your choice, you can do so more or less as before in previous versions of the program.
Basically you'll have to create a new database, save it in the system's or your profiles' Templates folder for Access and just make sure you've made all changes in preferences and imported all objects you want by default.

You'll find the procedure nicely described on the Office Online Section for Access or also at Allen Brown's pages:

templates folder and database in Access

But new in Access and a really cool feature is the possibility to use Database Templates (accdt files). Those Templates files are available from the Access Getting Started Page and are presented in different Template categories in the left navigation area.
To create such Database Templates files you'll need the Access Developer Extension tools (ADE), which is not included in the default install files. You can download this package at the Microsoft Download center.

Once installed you'll find a link on the Office Button at the bottom: Developers / Save As Template - once you've saved your template in one of the dedicated Templates folders (see previous section), your templates become automatically available from the Getting Started window.

(1) Comments ·
07 September 2009 - 04:03

Deploy Trusted Locations in Office 2007 system

If ever you need to deploy Trusted Locations in one of the Office 2007 programs and / or want to understand a bit more about the 'Group Policy Object Editor' here's the official Technet link:

And following discussion might throw some light as well:
(0) Comments ·
09 August 2009 - 15:38

Using wildcards in IIf() statements in Access objects

If you want to use criteria against one or more fields, depending on different scenarios, be it in queries or forms, the most logical step is of course to think in terms of using the IIf() function in Access.

This might be a solution, but it's not always necessary or the best solution. One thing and pitfall is definitely NOT to use any operators like 'Like' or 'Is not null' inside the arguments of your function.


E.g. these are some examples which are NOT going to work if used as Query criteria:

IIf([Forms]![Simple Selector]![OptGrpMemType]=1;"MRD";
IIf([Forms]![Simple Selector]![OptGrpMemType]=2;"NRD";Like "?RD" ))

The last argument has the Like operator

IIf([strPassedFunder] Is Null;Is Not Null;[strPassedFunder])

Is Not Null as a standalone argument is not going to work

IIf (checkbox=true;listbox value; "Like *")

Again the Like operator, moreover in between quotes

IIf([Forms]![frmLookup]![TypeSelect]=1;[Security TypeID]; 

'<>3' is the problem here

[Read more on Using wildcards in IIf() statements in Access objects...]
(0) Comments ·
03 August 2009 - 03:13

playing with 22, 7, 142857 and precision

In a reflection and research on Excels precision handling, I came across this magic number 142857. From the Wikipedia definition (

142857 is the six repeating digits of 1/7, 0.142857, and is the best-known cyclic number in base 10.
If you multiply the number by 2, 3, 4, 5, or 6, the answer will be a cyclic permutation of itself, and equivalent to 2/7, 3/7, 4/7 ,5/7 , or 6/7, respectively.

So in other words try this:
142857 * 2
142857 * 3
142857 * 4
142857 * 5
142857 * 6

But also this:

I hadn't realized that pi, or the famous 22/7 has this exact cyclic number too as decimals: 3,1428571428571428571428571428571...

22 and 7 being numbers which for all kind of reasons have always been predominant to me.
Makes you wonder ...
about the matrix of life e.g.

Oh, I almost forgot the point where it all started, the 'floating point', the maximum precision Excel can handle.

ps you'll find a 142857 calculator at Douglas Twitchell blog.

(0) Comments ·
01 August 2009 - 06:00

Dynamically sorting and filtering html tabular data on the client side

Now that javascript has re-incarnated into all kind of libraries and frameworks like Ajax and JQuery, It's amazing what can be done today on the client (browser) side when it comes to dynamic web pages.

I've been looking into a couple of solutions for a client who needs to provide web pages with sorting and filtering possibilities for html tables basically, but all triggered from the client's browser. In case you want to experiment too, have a look a the following.

sample of dynamic data filtering from client side
  • Firstly there's the use of the Spry, Adobe's open source javascript framework. Nicely embedded into Dreamweaver now (since CS3), it allows you to nicely access structured data, be it e.g. xml but also html tables !
    There's a whole bunch of possibilities and samples demonstrated on this page:
  • Then there are all these wonderful programmers and developers making all kind of solutions available. Matt Kruse proposes his Javascript Toolbox with all kind of libraries and the one to sort and filter tabular data is really very nice:
  • Toby Von Loesch is another one. He developed a very simple but powerful Javascript to filter a standard html table:

Amazing evolutions indeed ! And although limited (JavaScript, no database, functionalities etc.) really worth looking into as they bring speed and versatility to the client side of the application.

(1) Comments ·
31 July 2009 - 16:30

Delving in the treasure house of Pivot tables: calculated fields with if and more of those jewels

Delving after a solution to use functions, more precisely an IF() function, in calculated fields within Excels' pivot table tool, brought me about a couple of interesting insights and resources.

if() function in calculated fields

Firstly for the use of an if() function in calculated fields: that's a tricky one; I couldn't find a lot of useful links - I did find some descriptions of people struggling with the same issue: is one example of this. The conclusion of my research here was summarized by one of the comments:

PivotTables always apply the summary function to any field used in the calculated item. So effectively, your calculated field is evaluating =IF('Cost per Order'<35,SUM(Cost),0).
Since the Grand Total of Cost of not less than 35 it will return 0. Grand Total always operate on the data list contents not what's displayed in the PivotTable.

Or in other words: avoid the if function in calculated fields, implement it in the data table itself as a separate column, and use those values as column headers in your Pivot table - you can always hide columns which you don't want.

An example please !

Pivottable with prepared if()

To get the illustrated Pivot table, I first added a column to the data table with following code for a simple evaluation whether the line result goes over 1000 or not.


I've then used the ok/nok field (column) as a column label/field and filtered on 'ok' values only to get what I wanted - remark that I've added another field, total turnover, and kept the subtotals. By simply hiding the non wanted columns I can then have the number of 'oks' along with the Total Turnover (for all oks and no-oks).

(0) Comments ·
28 July 2009 - 15:32


There has been a lot of discussion lately on the net regarding the topic of HTML 5 -
As an illustration this post on Jeffrey Zeldman's blog can count: (see the comments too).

Just two blog links here which made a lot of sense to me and clarified the issue:

(0) Comments ·
23 July 2009 - 14:28

Mind Map on web development

I've been working lately on a first version of a mind map on web development.
A kind of a blueprint really for some of my courses, based on my experiences, on what I've been reading and studying myself, on all kind of resources and what I happen to process as 'highlights'.
And of course all excuses making me go through the tons of materials spread all over my office are good indeed - so I'm happy too having allocated some time to this as well :-)

The map covers a whole range of fields or themes linked to web development and web design, with all kind of resources - I intend to develop it further when doing my courses, with time, but as I know some people have requested for a download is version 1... as it says a very first version :-)

mind map on web development


All feedback, suggestions and input of course more than welcome !

20 July 2009 - 07:11

php in Expression Engine’s Entries

If ever you're an Expression Engine user (great ! me too :-) ) and if ever you wandered if you can actually use php within the blog entries you post, the answer is yes. And yes there are some security issues to consider.

Here is where I found the how:

(0) Comments ·
18 July 2009 - 05:48

Quality resources for web development and web design

During the last past months, my dear web developer friend Jaydev - currently much into the topic of usability - has kept on forwarding me so many links on the topic of web development and web design, that I felt I had to do something with it. Actually I was wondering even at the first place where he keeps on finding the time to go through all these resources !

So as I'm into the preparation of some web development courses, I grabbed the occasion and went through them once again, categorized them a bit and so for whoever may take benefit from or find them useful too: a nice collection of quality resources to improve the quality of your web development and web design works...

ps now who knows when I'll take or find the time to add all those nice ones hidden in my own bookmarks... but then also : who would have the time and the need to go through them anyway :-)

ps2 Well i've started adding some of my own resources too I noticed... :-)

(0) Comments ·
09 July 2009 - 16:21

How To Extract Flash or SWF from Office files

If ever you're after a nice procedure, here it I've found one:

And the other way round: to embed SWF in Microsoft Office documents: Excel - Word

(0) Comments ·
03 July 2009 - 16:53

Should I or should I not …. install IE8 of course !

The beginning of July brought an interesting choice when logging on to my PC: windows update apparently wanted to install Internet Explorer 8 (IE8) !

IE8 logo

Ha ! There it was - as of course I had been following the topic since a couple of months and witnessed the problems with the upgrades to IE8 - and, of course, I didn't allow automatic installation. Luckily we're still offered the choice - after all :-)

Now I must say immediately I can only be in favor of IE8, as in terms of CSS, it's definitely adhering standards better than all the earlier versions all together. So I can only be happy with its venue.
Nevertheless some care is definitely on its place here, as there seem to be all kind of possible pitfalls during the installation process, there are performance issues to consider too, but most importantly I have to consider testing, as I basically use IE for testing only - and yes indeed: we now have this wonderful transition period of IE6, IE7 and IE8.

So this is what I've decided to do: nothing basically, that is on my main PC with Windows XP - I'll give IE8 a tryout a bit later this summer on my Vista driven portable.
And I'll use IE Tester for my (CSS) development, along with Multiple IE.

If ever you're after some materials to make up your strategy:

Some experiences and/or tips


(0) Comments ·
28 June 2009 - 07:00

Using dates in stead of date-references in Excel formulas

If you're calculating with dates in Excel and you want to refer to a hard-coded date in stead of using references to your dates, you'll be in for a surprise.
In Access you can refer to a literal date using the hashes, e.g. #30/06/2009#, but Excel does not understand that.

So following formulas aren't going to do what you intend it for or will throw out errors:

You need to use the Datevalue () function which transforms a date-string into a real date Excel can understand and handle. So don't forget to put the hard-coded dates between quotes " " :-)
(0) Comments ·
21 June 2009 - 01:58

Automising updates for items in Outlook

attribute categories quickly

Here are two pathways to increase productivity in Outlook.

Suppose you wanted to apply the same category to multiple Outlook items eg, or you wanted to set a whole range of tasks as completed. Or change the due date for a whole bunch of tasks.
U see the point?

One interesting approach is that in Outlook you can always drag and drop many items onto one grouped header.
Make sure you have one item attributed to the category you envisage, use the group by view, then simply select all the items you wanted to categorize and drag and drop them on top of the category header.
Or in the tasks folder, group by Due Date. Then open and change the Due date of 1 Task so that you get a destination group. Then drag the tasks for which you want to change the Due Date on top of the group header.

You can of course also go the VBA path.
[Read more on Automising updates for items in Outlook...]

(0) Comments ·
17 June 2009 - 11:56

New webfonts on the firmament?

If you're in for some experimenting with web fonts and/or you want a change from the sIFR approach, then you could consider using the Typekit service.

Kevin Yank from Sitepoint recently addressed an article on this topic under the title: Get Real with Typekit.

Typekit introduced Typekit recently and that immediately triggered of an ocean of comments.
So that might be an inspiration.
If you prefer to evaluate a practical implementation: is your place to be.

(0) Comments ·

Change the default protection message in an Excel sheet

Is there a way to change the message that is displayed when the user tries to modify a locked cell on a protected sheet?
That's exactly the question I was asked during a course.

The answer apparently is : not really - following the discussion on this thread:, the conclusion probably is: prevent the use of being able to go into the cell at the first place.
Just make sure the option 'select locked cells' is DE-activated when you activate the protection on the worksheet

Then also you could add this VBA code to your sheet tab

Private Sub Worksheet_Activate()
If ActiveSheet.ProtectContents Then
  MsgBox "Watch out, you're going to have some funny messages" _
  & "if you try to change some protected cells in this sheet"
  MsgBox "Sheet is not protected"
End If

End Sub
(0) Comments ·
16 June 2009 - 16:11

To CSS or not to CSS: Object Oriented CSS

I knew it, but couldn't really put the exact wordings on it.
There must be another way to CSS.

Because looking at the talk Nicole Sullivan did for the Yahoo Developer Network Theatre, I have to admit : I did it too - I am co-responsible too in having contributed to following observations regarding CSS:

  • CSS requires quite a bit of expertise : yes I've been writing good semantic stuff, but web developers over there had hard time to understand with basic CSS knowledge...
  • File size just keeps getting bigger: yes to solve issues, match design requirement and so on, I've seen the sizes of my CSS files growing...
  • Code reuse is just not happening: yes it's true, I haven't made that extra effort to ensure that code for returning parts would be completely and easily reusable...
  • Code is too fragile : well indeed I was already happy if I could get it all together in a semantic way for all browsers...

And some other good practices

So her talk was on 'Object Oriented CSS' for high performance web applications and site and summarizes 2 of the main proposed principles nicely as follows -:

  1. Separate structure from skin - separate the CSS to layout, position, structure including browser compatibilities etc. from how it should look like
  2. Separate container and content - separate the CSS for the object, the placeholder, the container from what should go in, so that anything can go in
Teaching Object Oriented CSS [Read more on To CSS or not to CSS: Object Oriented CSS...]
(0) Comments ·
15 June 2009 - 14:17

Corrupted files in MS Project (2007)

During a MS Project coaching at a client (with Project 2007), we encountered something I had never had before : a corrupted and completely unusuable project file. It concerned a master project file and so one of the inserted plans became completely corrupt. No way of opening, inserting it again, exporting it to something else...
The best you could get out of it was a cryptic

"The operation could not be completed because the source file contains invalid project data or the total number of rows exceeds the limit of 1,048,000 rows in a project."

What happened ?

After some research (and as usual having tried multiple pathways, one being the fact that the files were converted from Project 2003) it seemed to be linked to a password set on one of the files and the fact of having copied the files... yes indeed: just copied.

Well IT never gets as weird as it gets (?!), but the closest explanation from Microsoft I could find, was in their knowledgebase regarding a bug in version 2002, which then got solved.

Perhaps it re-incarnated?

The solution of course still stands in its simplicity: don't use a pasword, or remove the password, then copy/move the file, and eventually put the password after the copying.
And if ever you bump onto file corruption in MS Project, you could try out the Microsoft Project MVP page's solution.

(5) Comments ·
14 June 2009 - 09:02

Check for duplicate values in Access tables

How to check doubles or duplicate values in Access tables?
Voilà another question which comes up regularly.

To start I'd like to point out to Microsoft Office Online which actually published a good article on this topic : Not only it explains about the use of Access' inbuild Find Duplicates Query Wizard, but it also shows SQL, illustrates different scenarios and clarifies the Unique Records and Unique Values properties.

The famous Allen Brown with his many tips for different users also has a strategy:

The VBA road then.

Here is a good approach to avoid duplicates being entered in a form:

The book ‘Building Access Applications’ by J. Viescas proposes a whole module on the topic of duplicates. In one chapter there's the description of a support database, Membership.mdb with a form ”frmCustomers” haing a vba module to check doubles for multiple fields.
Not that easy perhaps if you're not fluent with VBA programming, but really worth a visit and consideration. John Viescas developed a Public Function fctCheckSaved() called through an event onFormUnload – the function calls for another function soundex() which even checks for names with similar sound or pronunciation, but which can be omitted. The essence of this modules follows here as an example but you can download the database with the functions from his site.or see more code on MSDN

[Read more on Check for duplicate values in Access tables...]
(0) Comments ·
26 April 2009 - 14:45

MS Project Shortcuts

If you're looking for some MS Project shortcuts, I've found them here:

The ones I was after in particular were those to indent and outdent tasks:

Indent Task: Alt + Shift + Arrow Right
Outdent Task: Alt + Shift + Arrow Left
(1) Comments ·
20 April 2009 - 07:41


cufon sample

I haven't tried it yet, but this font face replacement technique discussed in Sitepoint Design View #58 and proposed by Cufón immediately grabbed my attention.
I've been using the sIFR (or Scalable Inman Flash Replacement) but this really seems to open some new perspectives as no Flash is needed and some issues with sIFR are being surmounted by Cufón. ...

(0) Comments ·
17 March 2009 - 13:40

Definition lists to style a multimedia listing of flash videos

I did ponder about using a definition list to provide some good x-html for a multimedia project, it, but finally gave it a go.
I came up with something which worked great in all browsers, including IE6 (yes still considering it a couple of months for now :-) )

And although I couldn't stop myself of not considering it a fully appropriated use of semantic html, I still found it very useful to consider definition lists to style this multimedia listing of flash video items.


<dl class="mmItem">
     <dt><a href="#">Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Duis ligula lorem, consequat eget, tristique nec, auctor quis, purus. </a></dt>
           <object data=";hl=en&amp;fs=1" type="application/x-shockwave-flash" width="150" height="125">
           <param name="movie" value = ";hl=en&amp;fs=1" />
          <span class="mmRating"><img src="images/stars_5.gif" width="75" height="14" alt="Rating" /></span><span class="videoItem">&nbsp;</span>


#mmItems { float:left; margin: 0; padding: 0; clear: both; width:100%; overflow:hidden; } /*IE7 */
.mmItem { float:left; position: relative; margin:0 6px 15px 6px; top: 5px; width: 325px; left: 6px; min-height: 125px; }
.mmItem object{position:absolute; left:0; top:0}
.mmItem dt, .mmItem dd { padding:0 10px 5px 160px; margin:0; }
.mmItem dd {}
.mmRating { float: left; display:block; line-height:25px; padding: 2px 0 0 0; }
.videoItem { background:url(images/videoItem.gif) no-repeat center center; width:30px; height:20px; display: block; float: right; line-height: 25px;}
.audioItem {}


Afterwards I did some research on using definition lists in such situations. Which reassured me actually, as these (older) samples confirm the use of multiple dl-elements.

(0) Comments ·
27 February 2009 - 16:26

Excel 2007: the xlsb format? And formats basically…

Have you heard already from the xlsb format?

In case I made you wonder: the 'b' stands for 'binary' and here are two links explaining a bit more about this new file format in Excel 2007:

And here is a very nice scheme and article explaining how to 'decode' the excel file formats:

file formats in Excel
(0) Comments ·
26 February 2009 - 15:56

CSS Grid Systems

Through an article in Sitepoint recently, I got into exploring some 'grid systems', which can come in handy when doing some new site layouts:

Some in and outs of these systems, so advantages and disadvantages are discussed in the annexing blog postings.

I've added these also to my overall CSS links page, which btw is regularly updated.

(0) Comments ·
20 February 2009 - 12:48

Multiple Substitute in Excel

During my excel courses I generally explain about the substitute() function.
But what if you wanted to change many characters, like e.g. many accented characters to their non-accented equivalents?

You could of course nest many substitute() functions and end up with something like this.

But here you'd probably best implement the use of a socalled user defined function, a 'udf'. Not sure where I found this one ...

Function BigSubstitute(CellToChange As Range, _
NameNumber As String) As String

  Dim X As Long
  Dim Data() As String
  Data = Split(NameNumber, ",")

  If (UBound(Data) + 1) Mod 2 Then
      BigSubstitute = "#MISMATCH!"
      Exit Function
      BigSubstitute = CellToChange.Value
      For X = 0 To UBound(Data) Step 2
      BigSubstitute = Replace(BigSubstitute, Data(X), _
      Data(X + 1), , , vbTextCompare)
   End If
End Function

So you'll end up with something like this:


P.S. The mismatch in the function is used to make sure you always have couples of data, in this case an accented and non-accented character

(2) Comments ·
17 February 2009 - 16:46

How to print a Gantt Chart view without table information?

To print a Gantt Chart view without table information, you first need to create a customized table and use that in an adapted Gantt Chart View.

  • 1. On the View menu, click Table, and then click More Tables.
  • 2. For Tables:, click Task.
  • 3. Click the New button
  • 4. In the Name box, enter "No Table Info".
  • 5. In the first row, under Field Name, enter ID, and in the first row under Width, enter a zero (0).
  • 6. Click to select Show In Menu.
  • 7. Click OK, and then click Close.

You can now use this table to print or preview a Gantt Chart view without table information as follows:

  • 1. On the View menu, click More Views.
  • 2. Select Gantt Chart, and click Apply.
  • 3. On the View menu, click Table, and then click No Table Info.
  • 4. On the File menu, click Print or Print Preview.


(0) Comments ·
07 February 2009 - 03:49

direction:rtl breaks CSS layouts in IE

Gosh, having to do a some Hebrew web sites, I was quite happy to find out that working with the {direction:rtl} actually works quite nicely. Even IE seemed to behave rather well, till of course I had to tackle some more complicated design with floats - there IE6 broke the whole design. I used every possible tool to hack IE6 I know about - there's a whole list by now - but nothing seemed to work.... Despair !! :-)

Luckily enough there's some wonderful developers out there, publishing wonderful articles and even though this one is years old, it did provide the solution: the workaround is to override direction for floats and re-apply it to elements that contain actual text. Go to Aleksandar Vaci? 's article for a full explanation.

(0) Comments ·
04 February 2009 - 14:49

Align numbers in tables with CSS

How to align numbers in tables with CSS, without having to apply a class selector on each and every td-cell?

Well luckily enough HTML provides the col- and colgroup-elements which can be used to address tables.
Suppose you have a table with 4 columns and you'd like to align the three last columns with figures to the right (leaving decimal aligning aside here as this is not really possible yet with CSS)...
You could start with following HTML coding.

<table width="90%" border="0" cellpadding="0" cellspacing="0">
<col class="tableFigures">
<col class="tableFigures">
<col class="tableFigures">
<td colspan="4" class="tableTitle"><h3>Population en Europe</h3></td>
</tr> .../...
[Read more on Align numbers in tables with CSS...]
(0) Comments ·
03 February 2009 - 07:36

Good old DOS

One of the nice things of the Windows platform is that it still runs under DOS - so to say of course -:)
Point is that you're still able to call for the command prompt window (windows key + r, or start / run, then type cmd and you're in). With a bit of prehistoric DOS knowledge you can then still do wonders.

I often need to prepare course materials - in the case of a Dreamweaver CS3 course that implies also copying those materials onto all the participants' file and/or webservers. So why not use a batch file (*.bat file, i.e. simple text file with *.bat extension) which contains several DOS copy commands and can be executed from the command prompt window doing the whole thing in one go?

the good old DOS window

Following nostalgy does the trick:

xcopy Z:\Empty\Docs\*  Z:\Student1\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student2\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student3\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student4\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student5\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student6\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Teacher\Docs\* /E /H /I /K /Q /V /Y

xcopy Z:\Empty\CSS\*  Z:\Student1\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student2\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student3\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student4\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student5\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student6\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Teacher\CSS\* /E /H /I /K /Q /V /Y

In case you're wondering about all the parameters or switches at the end: this seems a good link.

(0) Comments ·
30 January 2009 - 15:50

Refreshing an Excel worksheet when cell content changes

Recently I sat with a chess player who had found his way through table and pivot table functionalities in Excel in order to sort, filter and display chess results for his chess association.

His next step actually consisted in automating some of his sheets presenting the chess results. One thing he wanted to achieve is an automatic sorting of the table with the main results along with the automatic update of the pivot table based on those results. So far the operation consisted out of manual resorting of the data based on 3 or 4 fields, along as clicking the refresh button in the pivot table options.

The solution here consisted out of 3 steps.

Firstly, of course a simple macro could be made to trigger of the sorting instantly. However as the results change all the time during a chess happening, the macro ideally would have to be triggered off by each entering or updating of the results in the sheet. Actually this second step is possible by using some embedded Excel VBA worksheet events. Worksheet events, just as workbook events, or a kind of prefixed or pre-named procedures which you call in the module sheet behind the worksheet object in the VBA editor:

worksheet events

Amidst the possibilities are following triggers:

Private Sub Worksheet_Activate() - when the worksheet is activated
Private Sub Worksheet_Calculate() - when the worksheet is recalculated
Private Sub Worksheet_SelectionChange(ByVal Target As Range) - when the selection is changed
Private Sub Worksheet_Change(ByVal Target As Range) - when a change is made to a cell in the worksheet

It's the last one which offers the possibility to solve the riddle here; following code triggers off the macro doing the sorting to the table whenever a cell value is updated or entered:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     mkSortCK 'macro which is being called
End Sub

Finally a simular worksheet event construction offers the possibility to refresh the pivottable automatically, when you activate the sheet it's located in:

Private Sub Worksheet_Activate()
End Sub
(0) Comments ·
28 January 2009 - 15:51


If you speak french or german and live in West-Europe, the chance is real you know about the French-German cultural TV chain Arte. Not only Arte has high standards in terms of programs they diffuse, they manage to combine vision and quality.

They also set standards in terms of internet though.

Arte TV 7+

Actually I don't have TV cable, so I'm not really able to watch ARTE nor any other TV chain at all.
Well... "was" in case of Arte.

Because of course they have a site - and moreover: they provide online TV for 7 days after emissions. This approach of re-diffusion of a whole series of qualitative programmes for one week is actually outstanding. Not only the user interface of Arte+7 is very well conceived, it's definitely also a good, if not an exceptional, example of how flash technology can enhance user interaction.
Initially looking at their programs was reserved for inhabitants of France and Germany only, but since shortly they've managed to open the frontiers totally.

Do visit their subsite Arte+7 - I'm sure you'll find it a worthwhile passage and you'll be able to grab away some interesting documentary or two.

(0) Comments ·
19 January 2009 - 16:46

Strict Doctypes, deprecated elements and XHTML soup

Once you're on the path of wanting to write compliant code, the switch to choosing strict doctypes is a logical consequence.

In case you ever wondered where to find the so-called deprecated elements and attributes, which you can't use anymore if you choose a strict DTD for doing your webpages...
well I suppose a good place to start would be the web consortium itself. They have some published some interesting lists with all kind of additional information on the html elements and attributes as well.

Now you can still go for HTML or XHTML strict, and although I'd find being dogmatic on these topics isn't really an option for me - I tend to adapt to the situations and clients - I did enjoy reading following article on the Mozilla Developer Center recently: Making your page using web standards - how to
It's not only an article on good practices but also a vivid call for HTML strict and not XHTML - there's a couple of extra links on that page contributing to that call and really worth reading too.

And some more links on the topic:

(0) Comments ·
14 January 2009 - 16:50

What to do with IE6 in 2009?

Well yes with IE8 coming up, you might wonder what to do with IE6 ; not always easy to answer as long as some major businesses or organizations still use this browser.
So a podcast and some links may be of inspiration:

(1) Comments ·
09 January 2009 - 15:39

Styling ordinary lists with CSS (combinded with IE6 and strict DTD)


In summary: in a large organisation some webmasters need to be able to add and style lists in html documents created from templates - their main browser is IE6 (yes... it does happen :-) ).
The technique of zeroing out all margins and paddings has been used here and bullets were removed by default by the main design for easiness of styling the templates.
Contextual selectors have been added to the main CSS file (see sample code), so that when html lists are created, ordinary bullets or numberings appear as desired - the only requirement for this is to apply the appropriated class selectors

there's a problem

[Read more on Styling ordinary lists with CSS (combinded with IE6 and strict DTD)...]
(1) Comments ·
31 December 2008 - 06:05

Photoshop CS4 removed contactsheet and other plugins

One of things on my todo list is the tryout of the new Adobe CS4 programs, especially Dreamweaver and Photoshop.

Trying out Photoshop first, I soon discovered that the Contactsheet plugin, amongst others, have been removed in the new version CS4. For some plugins I understand as there seems to be better functionality in the new version, but I couldn't find anything yet replacing the Contactsheet plugin. I know it's not ideal (the simple but great functionality on the matter in Paintshop Pro could be of inspiration), but I like it and so was really wondering immediately is there was a 'workaround'.

Yes of course, googling it didn't take long: you can download them here at the Adobe site.

And I found following installation instructions at The Luminous Landscape Forum.

  1. From the Photoshop CS4 install disc, drop the //#language#/Goodies/Optional Plug- Ins/Automate/ContactSheetII plug-in into the //Adobe Photoshop CS4/Plug-ins/Automate folder (this is for both Contact Sheet and Picture Package).
  2. From the Photoshop CS4 install disc, drop the //#language#/Goodies/Presets/Layouts folder into the //Adobe Photoshop CS4/Presets folder and restart Photoshop.
  3. Optional: To add this legacy functionality back into Bridge (as a menu item):
    From the Photoshop CS4 install disc, drop the //#language#/Goodies/Optional plugins/ Bridge Startup Scripts/photoshop_contact_sheet_ii folder and the photoshop_contact_sheet_ii.jsx file into following locations and then restart Bridge.
    • Macintosh: //Library/Application Support/Adobe/Startup Scripts CS4/Adobe Photoshop/
    • Windows XP: //Program Files/Common Files/Adobe/Startup Scripts CS4/Adobe Photoshop/
    • Windows Vista: //Program Files (x86)/Common Files/Adobe/Startup Scripts CS4/Adobe Photoshop/
(2) Comments ·
30 December 2008 - 06:50

Excel: Data validation, combo lists and autocompletion to search values quickly

Is it possible to type in the first letter in an Excel validation list to quickly go to some values within the list?
Another of those questions falling in between the lines of an Excel course.
The answer is : no. But of course even if the answer is right, it isn't satisfying really... or is it?

data validation [Read more on Excel: Data validation, combo lists and autocompletion to search values quickly...]
(2) Comments ·
29 December 2008 - 12:21

Accumulated subtotals in a filtered list or table in Excel

How to obtain cumulated subtotals in filtered data in Excel (be it in a list or table asper Excel 2007)?

cumulated subtotals in filtered data

It's not very difficult actually, but it seems to have a tweak ! This is how it works.

Create your list or table as usual first. Make sure you have a blank column to the right of your table to add your main formula. As you might already know we'll have to use the subtotal() formula here, as this is the only one considering filters and giving you correct results with filtered data. (Excel 2007 uses this by default in the creation of Tables by the way if you add the totals from the Table Data sub-ribbon).
If you want cumulative subtotals, just fix the first reference (make it absolute) and leave the second half relative as follows:


Copy this through and you're basically done.

If you don't leave a blank column and/or don't make sure Excel does not include your subtotals column, there seems to be a problem in the results you'll get when filtering. You get one or more extra lines which do not match the filters. This might be linked to the fact that we're referring here to the cells in a rather unusual way...
Only disadvantage of this tweak is that if you use alternated row colouring (a new feature in tables from Excel 2007 onwards), you won't be able to get this formatting in the subtotals column. You could transform your subtotals column also in a table and format it in the same way, but this doesn't seem to be a solid solution either.

Don't hesitate to drop a line with your experiences on the matter or if you've got a better solution !

(0) Comments ·
28 December 2008 - 15:25

Double Clicking format painter in Office rocks … not always

The format painter is one of the most use features in Office programs and many people also use it by double clicking on it once the source has been set to enable multiple pasting of the formatting.

Recently a course participant expressed his frustration as this feature didn't work on his PC - and he seemed to have a friend wrestling with the same issue.
Not much more is needed to invite me on the investigation path.... and indeed not much to be found by googling it and in Microsoft knowledge base only some rare confirmations of a couple of people having had or having the same issue.

format painter problem

Looking a bit more careful to the cursor behavior, brought me to the inspirational thought: "and if it would be linked to the double speed of the mouse button ?"
And indeed, soon we found out that double click speed was set to maximum and that by reducing speed a bit, the format painter rocked again...

Well the wonders of refined technology - 'wah' and 'oh', but all so often 'grrrr' before the 'aha' brings back the 'omm' .... :-)
(1) Comments ·
26 December 2008 - 08:43

Text Conversion problem with Word 2007

Hadn't noticed this before with Word 2003, but when trying to open very old text documents (made with wordperfect 4.2 and 5) I got a funny "the selected text converter is an old version" error message.
I reinstalled the text converter... in vain. Downloaded Microsoft's text converter from their site... in vain. Searched the MS knowledgebase... you got it yes... in vain.

But what a wonderful place the internet is, as you always find one or another thread leading to a breakthrough.

This time my lucky one was this one.

The problem seems indeed that the older text converters don't seem to be updated when installing Word 2007 (or even 2003 as mentioned in the post).
The solution for me was:

  • Rename the current folder with older converter files at C:\Program Files\Common Files\Microsoft Shared\TextConv
  • Reinstall the text converters - this provided the updated dll and so indeed solved the issue
21 December 2008 - 12:45

Selectoracle : English (& Spanish) translations of CSS2 and CSS3 selectors

Css Select Oracle

I've bumped on this one today, through a CSS book: an online CSS selector dictionary, so to say.
From their intro:

Ever wondered what a particularly complex CSS selector really means? Here's your chance to find out! Simply enter one or more semi-colon separated selectors or rulesets into the following "Direct Input" area, or provide the URL of a stylesheet in the "URL Input" area. Best of all, the SelectORacle will flag potential errors and other problems, and it won't choke on any actual rules. You've always wanted to know what makes those line-noise posers tick-- now you can.

The link:

(0) Comments ·
18 December 2008 - 12:23

Excel Shortcut Keys

excel shortcuts

A question I regularly get asked during Excel courses is: where do I find some good Excel shortcut keys?
Well, on the net of course :-)

Just two links to get you going:

And if you're after a downloadable document with shortcuts:

(2) Comments ·
11 December 2008 - 09:57

Save embedded images from an Outlook 2007 message

"Why have it simple, if you can complicate it" - one often wonders...

If you've ever wondered why you just can't copy/paste embedded images from Outlook 2007 messages... well you're definitely not the only one.
I don't really know the exact reason - I sense the Word rendering engine now used by default might behind it, but I do found the solution to the problem here.

It works through a VB macro, which you need to embed into a module and trigger off by a button. Very like much a procedure to save all attachments at once, but this one saves the embedded images as well.
Watch out, this macro needs you to activate a special reference through the VB editor (Microsoft Shell controls and automation) and saves all attachments and embedded images at once in a directory you can choose.

If you want to use something more flexible you could try the Outlook plugin from EZ Detach

(2) Comments ·
22 November 2008 - 12:49

Excel 2007 - Ribbon survey

office ribbon survey

This is the first of it's kind I've come across: an interesting survey examining users' experiences with the new ribbon GUI in Office 2007.
No comment :-)

ps. but a must read for all those doing Training Courses

(0) Comments ·
21 November 2008 - 10:24

How to find the previous or last filled cell in a column in an Excel spreadsheet?

During an Excel course yesterday, someone wondered whether it was possible to fill out a column with ascending numbers, whilst omitting the blank lines (or lines where the triggering value in a column are blank). I'm including an image here to kind of clarify the nature of the question.

filling in Excel

My initial wanderings brought me to some interesting approaches based on the use of following formula based on what I found at this site:

=MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0)) - to find the row number of the last filled cell in a column

=INDEX(A1:A100;MAX(IF(LEN(A1:A100)>0;ROW(A1:A100);0));1) - to get the content of last filled cell in a column

These are array formulas of course, to be entered with Ctrl-Shft + Enter; also bear in mind you that you need to use the commas or semicolons to separate the arguments depending on the regional settings you use.

Finding the content of the last filled cell in a column, could also be done with something like this, using the ADDRESS and INDIRECT functions:

=INDIRECT(ADDRESS(MAX((ROW(1:50)* (C1:C50<>"")));COLUMN(C:C)))

These formulas only consider the last cell of the column however, and to really solve the uttered puzzle we need to obtain the last cell of each previous block of cells, in order to be able to continue and increment the counting. So this is what I came up with :


If there's nothing in the triggering cell, then of course there's no numbering. Then if there's something in the previous cell, the numbering can just go on (+1). However when the cell is empty, in stead of looking for the last cell of the previous block, I finally decided to just go and get the number of blank cells and reduce it from the row number. That does the trick.

But yes if you have another approach or know a way to get the last cell of the previous block of cells to increment it, I'd be delighted to know !

(1) Comments ·
17 November 2008 - 16:56

Managing external links in Office 2007

If you're used to use the Paste Link functionality between PowerPoint and Excel, or similar functionality to obtain links between your different Office applications in Office 2007, then at one point you'll be wondering where the management tool went...
well you know: the dialog box which in older versions was available from Edit | Links...

Well very simple : it now sits under the Office button | Prepare | Edit links to files

Gosh... what a location I hear you thinking - yes I did so too :-)

ps : If you haven't done so, it may be a good idea to download the Reference Guides to find locations of 2003 commands in 2007.
(0) Comments ·
16 November 2008 - 16:20

Transferring the Quick Access Toolbar settings in Office 2007

The easiest way to transfer the Quick Access Toolbar from one computer to another is simply copying the *.qat files in your Application Data folder.
You'll find these here:

  • XP: C:\Documents and Settings\'username'\Local Settings\Application Data\Microsoft\Office\*.qat
  • Vista: C:\Users\'username'\AppData\Local\Microsoft\Office\*.qat

Simply copy the *.qat files for the customized quick access toolbar you're after from one computer to another.

Ps : found this one here:

[Read more on Transferring the Quick Access Toolbar settings in Office 2007...]
(0) Comments ·
14 November 2008 - 15:15

Calculate variable cumulative rates and commissions in Excel

Doing courses has the interesting side-effect that people come with all kind of questions challenging you to go beyond what you know and are able to do all the time.

So next situation was proposed by one of my participants, and although I had some initial pathways to solve the riddle, I really started wondering about better and other logics and ways to approach the question.

Suppose you have a salesperson having sold 45000 € - a cumulative bonus needs to be calculated based on a table, considering the proportional bonus for each scaling :

0 0,00%
20000 2,50%
30000 5,00%
40000 7,50%
50000 10,00%
60000 12,50%
70000 15,00%
80000 17,50%

So in other words: (2,5% * 10000) + (5% * 10000) + (7,5% * 5000) i.e.
upto 20000 there's no bonus, below 30000, there's 2,5%, below 40000 there's 5% etc. (the vlookup logic)

So I published this question on an outstanding Excel discussion forum and obtained plenty of inspirations through it.

One of the links pointed to a very broadening approach for me:

The most simple solutions to me finally seemed these:

variable cumulative rates and commissions
In the range A1:A8 enter the numbers 

B1: 0
B2: =(A2-A1)*C1+B1
Copy from B2 to B8
C1:C8 - enter the percentages 0%, 2,5%...17,5%
E1: the amount being examined: 45.000

And for the formulas as such

=SUMPRODUCT(--(B11>$A$1:$A$8); (B11-$A$1:$A$8); $D$1:$D$8)
(0) Comments ·
12 November 2008 - 16:04

Criteria ranges with functions in Excel

Yes you can use functions in Excel's criteria range when using the advanced filter.
Here's a sample.

functions and criteria ranges

Remark that in this scenario, you don't need to have something written in the criteria range's label cel; but you do need to select.
Another thing is that you need to refer to the first cell with content (below the label) to refer to a column in your expression.
Finally you need to think carefully about absolute and relative references - as always actually :-) ; the result of the function may give a 'FALSE' or a 'TRUE', even an error code, but the filtering will work.

(0) Comments ·
05 November 2008 - 15:09

Excel Resources and Links

Some interesting Excel links :

Specifically linked to VBA :

For those users who feel they need to practice more:

Some hints towards books

In french:

(1) Comments ·
04 November 2008 - 14:10

Blocked Outlook attachments

To solve problems with Outlook 2007 attachments which are blocked:

  • 1. Make sure Outlook is closed.
  • 2. Open your registry editor by opening the Run command and type regedit
  • 3. Locate the following key
    Outlook 2000 [HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security]
    Outlook 2002 [HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security]
    Outlook 2003 [HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security]
    Outlook 2007 [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security]
  • 4. Go to Edit-> New-> String Value and name the value Level1Remove (case sensitive!)
  • 5. Double-click on the newly created value and enter the extension including the “dot” that you want to open in Outlook. For instance .exe
    If you need to enter more than one extension you’ll have to type separate them by a semicolon like this; .exe;.bat;.url
  • 6. Press OK on the input box and close the registry editor
  • 7. When you open Outlook the attachments which hold those extensions aren’t blocked by Outlook anymore.
(0) Comments ·
20 October 2008 - 06:46

E-mail Obfuscators

Ever wanted to 'obfuscate' your email addresses? Ever heard of the term 'obfuscation'?

To start with the latter, Wiki says:

Obfuscation is the concealment of meaning in communication, making it confusing and harder to interpret.

In IT terms, obfuscation got a wider practice with the upcoming need to hide email addresses from search robots, email harvesters etc. It there refers to making the code obsolete or un-readable for robots or programs other than those the code is destinated for.

If you're still into search for some good tools here's just a grip out of the tools available on the net:

After obfuscation your code typically looks like this:

<a href="javascript:location='mailto:\u0069\u006e\u0066\u006f\u0040\u0065\u006d\u0061\u0069\u006c\u002e\u0063\u006f\u006d';void 0">Danny</a>


<a href="&#109;&#97;&#105;&#x6c;&#116;&#111;&#x3a;&#105;&#110; &#102;&#x6f;&#64;&#x65;&#x6d;&#97; &#105;&#x6c;&#46;&#x63;&#111;&#x6d;">Danny</a>

You can of course also use an image or a simple descriptive : danny'at' :-)


(0) Comments ·
18 October 2008 - 14:08

Median in MS Excel Pivot tables

Adding to a previous post on this blog, Excel pivottables and subtotals in % relative to row-values, I keep on receiving all kind of simular questions of things which are hard or not possible with Excel’s Pivot Table functionality .  One of these is e.g. calculating ‘median’ values or using ‘median’ functionality.

If you really need to tackle huge pivot tables and analyze lots of data, then consider using the digidb’s special add-on. It offers all kind of added value which can be use to fill the gaps in the otherwise really wonderful Pivot Table feature. And yes: one of these features consist in the possibility of calculating Medians.

(0) Comments ·
Page 1 of 2 pages  1 2 >