Determining Office Version

[Jim Gross]

I guess I'm out of luck too?

I have found that there are two different strategies needed to disable Excel's built-in menu/toolbar buttons depending upon Excel version. In Excel 2002, the property CommandBars.DisableCustomize must be used to prevent re-enabling the buttons through customization, thanks to changes including the new Toolbar Options feature and loss of the "Well" command bars in xlVBA. CommandBars.DisableCustomize does not exist in earlier versions (Excel 2000) and a different strategy is needed to accomplish the same result.

Unfortunately the Excel 2000 VBA compiler barfs when it sees CommandBars.DisableCustomize. Conversely (and perhaps worse), using the code that works in 2000 leaves a gaping back door open in 2002. There appears to be no way to identify the Excel or Office version in conditional compilation directives to prevent this.

Or have I missed something?

Thanks.

Jim

[Dave Peterson]

I've lost the original post in this thread, but you could check the
application.version to see what version of excel you're running:

Option Explicit
Sub test()
If CDbl(Application.Version) >= 10 Then
Call SubToDisableCustomize
Else
'do your earlier version stuff
End If
End Sub

Then in a module that contains nothing but these lines:

Option Explicit
Sub SubToDisableCustomize()
Application.CommandBars.DisableCustomize = True
End Sub

The second module won't compile in earlier versions of excel, but it won't be
run either--so it doesn't need to.

And Peter Beach had a neat idea to use late binding to a similar question:
http://groups.google.com/groups?threadm=%23zeczIyqCHA.2488%40TK2MSFTNGP12

Sub a()
Dim o As Object

Set o = Application

On Error Resume Next
o.CommandBars.DisableAskAQuestionDropdown = True
If Err.Number = 0 Then
MsgBox "2002"
Else
MsgBox "not 2002"
End If
End Sub

=====
You would change that to
o.commandbars.disablecustomize = true

--

Dave Peterson
ec3...@msn.com

[Jim Gross]

Thanks for your response, Dave.

I know about Application.Version. Problem is that
CommandBars.DisableCustomize doesn't exist in Excel 2000
VBA, but I need it when the workbook is hosted in Office
XP. I can force the 2000 compiler to ignore that code if
I use conditional compilation with compiler directives:
#If...#Then...#Else...#End If (new in Excel 2000.)
Otherwise, 2000 VBA throws an error just at the sight of
CommandBars.DisableCustomize, before any code in the
module even executes.

Unfortunately, application.version doesn't work with the
compiler directives. The directives work only with hard
constants, and there aren't any built-in constants for
Excel/Office version that I can find.

The solution I have found is somewhat convoluted,
involving the use of Add-In methods to load (and later on
unload) part of the VBA code from a file or a worksheet
into a code module after editing it to set a conditional
compilation constant based on application.version. It's
not pretty, but it does meet the objective of a single
code base and so far it seems to work.

Jim Gross

>.
>

[Chip Pearson]

Jim,

You can use Conditional Compilation combined with CallByName to
do what you want. For example,

#If VBA6 Then
If CInt(Application.Version) > 9 Then
' Excel 2002 or 2003
CallByName Application.CommandBars, "DisableCustomize",
VbLet, True
Else
' Excel 2000
End If
#Else
' Excel 97
#End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Jim Gross" <anon...@discussions.microsoft.com> wrote in
message news:417101c3e3cb$ab98cd10$3101...@phx.gbl...