VBA Express : Excel - Check for compatibility of Office versions on opening a workbook

Excel

Check for compatibility of Office versions on opening a workbook

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

On opening the book, a message box will inform you of what version of Office is installed. Another message box will then tell the user whether or not their installed version of Office is fully compatible with your program.

Discussion:

An example use of this is: You may have devised a program that is highly dependent on being able to display modeless userforms. This program will give arror messages if run on an earlier version of Office, so it is best to let the user know this before they try to do anything.

Code:

instructions for use

			Option Explicit 
 
Private Sub Workbook_Open() 
     
     'Notify what version is installed
    If Application.Version = 7 Then 
        MsgBox "You have Office '95 installed" 
    ElseIf Application.Version = 8 Then 
        MsgBox "You have Office '97 installed" 
    ElseIf Application.Version = 9 Then 
        MsgBox "You have Office 2000 installed" 
    ElseIf Application.Version = 10 Then 
        MsgBox "You have Office 2002 installed" 
    ElseIf Application.Version = 11 Then 
        MsgBox "You have Office 2003 installed" 
    ElseIf Application.Version > 11 Then 
        MsgBox "You have Office version " & Application.Version & " installed" 
    End If 
     
     'Run a cautionary message if version is less than (for example) v9.0
     'e.g. if userforms are to be shown modeless, V7 and 8 will generate
     'an error when Userform1.Show False is used...
    If Application.Version = 7 Then 
        MsgBox "This program was designed to run on Office 2000 (or higher)" & vbLf & _ 
        "You have Office '95 installed, so it's quite possible the program" & vbLf & _ 
        "may not function properly on your machine...", vbInformation, "Information..." 
    End If 
    If Application.Version = 8 Then 
        MsgBox "This program was designed to run on Office 2000 (or higher)" & vbLf & _ 
        "You have Office '97 installed, so it's quite possible the program" & vbLf & _ 
        "may not function properly on your machine...", vbInformation, "Information..." 
    End If 
    If Application.Version >= 8 Then 
        MsgBox "This program was designed to run on Office 2000 (or higher)" & vbLf & _ 
        "so you should have no problems when running it...  :o)", _ 
        vbInformation, "Information..." 
    End If 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select the "ThisWorkbook" module
Copy and paste the code above into the Module
Now select File/Close and Return To Microsoft Excel
Save the workbook and exit

Test the code:

To use the example file:
Open the workbook "VersionCompatibility.xls"
(Note that there is actually no program to in here to "run")

Sample File:

VersionCompatibility.zip 7.25KB

Approved by mdmackillop

This entry has been viewed 225 times.