auto increment (auto_increment) in Excel with VBA (auto generate line numbers)

I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)

I knocked up a quick macro that seems to do the job for the user

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 1 And Target.Column <= 7 Then
        If Target.Row - 1 > 0 Then
            Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1
        End If
    End If
End Sub


PowerPoint 2010 video problems "Microsoft Office PowerPoint Has Stopped Working"

I encountered a frustrating problem today, I’ve still not really worked out a “final” solution but here’s what I discovered.

A user has a number of power point presentations with videos linked (possibly embedded within) the videos are of mixed format.

The videos and presentations run perfectly on their laptop but on their assistants they do not. In fact on their assistance laptop Microsoft PowerPoint crashes when you try and open the presentation with the following error message “Microsoft Office PowerPoint Has Stopped Working”

I was able to open the presentations with PowerPoint 2003 (using Microsoft Compatibility pack), the base files are in 2007 format (.pptx), if PowerPoint 2003 won’t open the pptx file and you have the following error “No text converter is installed for this file type” simply download service pack 3 for Office 2003.

So people have suggested that once open in 2003 that you could try saving the file as a ppt, this works however I am concerned about what video functionality we may lose by doing this.

I found that by uninstalling VLC media player that I could now open the presentations in PowerPoint 2010 however I am unable to view any of the videos nor am I able to view any of the video properties. I managed to fix this by installing an earlier version of VLC media player (I opted for version 1.0.0 available from VLC’s ftp site http://download.videolan.org/pub/videolan/vlc/)

It seems that the videos in this presentation are “linked” a new feature in 2010 is embedded videos, I think I will investigate this and see if embedding the media resolves this issue.


Follow Up: Exchange / Outlook Nickname (NK2) contacts syncing to iPhone

OK, so in my last blog posted I talked about an annoying problem with setting up contacts with the iPhone and Exchange 2010 and while the work around I posted hid a lot of unnecessary contacts they are still actually on the device. This causes a problem for a lot of my mobile users as a number of them sync their contacts via blue tooth with their in car systems.

The first time I had one of them do this they reported that they had 1000+ contacts listed on their in car system.

A previous work around I had used for the BlackBerry was to move all the contacts to a secondary address book and then have them store only telephone numbers in their main 'Contacts' address book within Outlook. On the iPhone this doesn't work.

So here's how I solved this problem (or at least this is my work around)

Within Outlook export all the contacts from the address book to a CSV file.

Go to GMail and import the CSV

From GMail export the contacts to a vCard format.

Go to iCloud and import the vCard

Now setup your iPhone to only sync contacts from iCloud and not Exchange.

Seems a little messy really, Apple should really come up with a fix for this, as when my users add new contacts they will need to do it via the device/iCloud and then also in Outlook.


Exchange / Outlook Nickname (NK2) contacts syncing to iPhone

So I'm fairly new to iPhone (having had a BlackBerry for 4 years). Since setting my iPhone 5 to sync contacts, email etc with Exchange 2010 I've had a rather annoying issue, it seems that the iPhone picks up the "Suggested Contacts" folder, this means that my iPhone now has a contact for every single person that I've ever send an email to.

I read a couple of articles which suggest the way to fix the issue is to disable the suggest contacts, for me this would work fine, however the majority of my users use the suggested contacts. The solution is actually rather simple.

On the iPhone go to Contacts
Go to Groups
Check all the address books that you don't want to sync.