Windows 7 - How to make macros work in both XL2003 and XL2010?!?

Asked By Charlotte E. on 10-Aug-12 11:28 AM
I am experimenting with Ribbons in 2010, and apparrently I must create
the macro with '(Control As IRibbonControl)' to be able to call the
macro from a ribbon-button.

But this gives an a compile error in XL2003.

My quick test -macro is:

Sub Show_A1(Control As IRibbonControl)

If Range("A1").Value = "" Then
MsgBox "Cell A1 is empty!"
MsgBox "Cell A1 = " & Range("A1").Value
End If

End Sub

How to make macros work in both XL2003 and XL2010 without compiler-error
in either???


Living the Dream replied to Charlotte E. on 10-Aug-12 02:14 PM
Hi Charlotte

What about testing for the version first before the Ribbon Code is fired.

This is a snippet of code from Ron Debruin's Mail code which tests which
version is running and looks for the applicable security structure
although I think you could use it for your purpose, you will have to
change this to suit..

If Val(Application.Version) < 12 Then 'Version Running is 2000-2003
Call the applicable code.
Select Case Sourcewb.FileFormat
Case 50: Call the applicable code.
Case 51: Call the applicable code.
Case 52: Call the applicable code.
Case Else: Call the applicable code.
End Select
End If

Charlotte E. replied to Living the Dream on 10-Aug-12 02:22 PM
I knew that :-)

That's not the issue here....
...the issue is 'IRibbonControl' object which does not exists in
pre-ribbon versions of Excel.

unless there is a way to make a call from a ribbon to a macro without
this, it is impossible to create Excel-files with custom ribbons that
will work in both newer and older versions of Excel.

Surely Microsoft must have thought of that - question is: How???


Den 10.08.2012 20:14, Living the Dream skrev: