Open Workbook Add In with 2007 & 2003, Which Changes Ribbon & Tool

[RyanH]

I have a workbook that is currently 7 megs and filled with userforms and
macros. I want to create an Add In workbook that will hold all the code and
the current workbook will just hold the data and make references to the Add
In. The problem is this, depending on which version of Excel opens the data
workbook I want this Add In to change the Ribbon in 2007 or change the
CommandBars in 2003 of the data workbook when it is opened.

Do I need to develop two different Add-Ins, one for 2003 & one for 2007?

I have already used the Custom UI to use XML to change the Ribon of a
workbook. If I made this workbook an Add In, how could I make the Add In XML
change the data workbook Ribbon?

 

[Ron de Bruin]

hi RyanH

See this page
http://www.rondebruin.nl/compatiblemenu.htm

 

[RyanH]

Once again, thanks for the reply Ron! Your site sure helps trying to learn
this Ribbon stuff. After reading the link you sent, I'm still not convinced
I won't have to develop two different Add-Ins. Please correct me if I am
wrong.

Here is a better explaination of my situation:

Private Sub Workbook_Open()

' ****************************
' When Data Workbook Opens
' ****************************

If Val(Application.Version) < 12 Then
' open 97-2003 Add-In
' 97-2003 Add-In will change the Data Wbk Command Bars and
' will contain all userforms and all modules code to manipulate Data
Wbk
Else
' open 2007 Add-In
' 2007 Add-In will change Data Wbk Ribbon and
' will contain all userforms and all modules code to manipulate Data
Wbk
End If

End Sub


When either Add-In (97-2003 or 2007) is called to open will the Add-In
always assume the Data Workbook is the ActiveWorkbook thus changing the
CommandBars or Ribbon of the Data Workbook?

--
Cheers,
Ryan

hi RyanH

See this page
http://www.rondebruin.nl/compatiblemenu.htm

 

[Ron de Bruin]

Hi RyanH

The 2007 add-in only have RibbonX to create the ribbon
All other code is in the xls file

Look good at the second example on the page.
You see that there is only ribbonx in the 2007 add-in

If it is Excel 12 it open the 2007 add-in to create the ribbon
If not it call the macro AddMenu to create the menu in Excel 97-2003

Private Sub Workbook_Open()
If Val(Application.Version) > 11 Then
If Dir(ThisWorkbook.Path & "\" & RibbonxAddin) <> "" Then
Workbooks.Open ThisWorkbook.Path & "\" & RibbonxAddin
Else
MsgBox "The RibbonX add-in (" & RibbonxAddin & " is not in the same folder as this workbook." & vbNewLine & _
"The custom Ribbon tab cannot be created without it."
End If
Else
AddMenu
End If
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Once again, thanks for the reply Ron! Your site sure helps trying to learn
this Ribbon stuff. After reading the link you sent, I'm still not convinced
I won't have to develop two different Add-Ins. Please correct me if I am
wrong.

Here is a better explaination of my situation:

Private Sub Workbook_Open()

' ****************************
' When Data Workbook Opens
' ****************************

If Val(Application.Version) < 12 Then
' open 97-2003 Add-In
' 97-2003 Add-In will change the Data Wbk Command Bars and
' will contain all userforms and all modules code to manipulate Data
Wbk
Else
' open 2007 Add-In
' 2007 Add-In will change Data Wbk Ribbon and
' will contain all userforms and all modules code to manipulate Data
Wbk
End If

End Sub


When either Add-In (97-2003 or 2007) is called to open will the Add-In
always assume the Data Workbook is the ActiveWorkbook thus changing the
CommandBars or Ribbon of the Data Workbook?

 

[RyanH]

Thanks for sticking with this Ron, I appreciate it! I understand what you
are saying on the website and I still think I will need two Add-Ins . Your
example opens the Add-In to change the Ribbon if the 2007 version is used or
uses the code contained in the workbook to change the CommandBars if an
earlier version is used, right? Maybe I am missing something or not
explaining what I mean so I will attempt the explanation again.

My currect Data Workbook is 7 megs, because it contains all the data,
userforms, and module code. I want to make this file smaller by creating an
Add-In that will contain all the userforms and module code to manipulate the
data. This Add-In will also change the CommandBars or Ribbon, depending upon
which version opens the Add-In. If I used the example on the site I would
still need to have all my module code and userforms in the Data Workbook.

 

[Ron de Bruin]

You must always open the add-in with the code and forms so it makes
no differents I think.

Open one file of 7 megs or two that are together 7 megs

Maybe you file is to big because of a usedrange that is to big
See
http://www.contextures.com/xlfaqApp.html#Unused


If you create two different version you will see that the 2007 file is much smaller then the 2003 file
because of the new format.

Good night



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks for sticking with this Ron, I appreciate it! I understand what you
are saying on the website and I still think I will need two Add-Ins . Your
example opens the Add-In to change the Ribbon if the 2007 version is used or
uses the code contained in the workbook to change the CommandBars if an
earlier version is used, right? Maybe I am missing something or not
explaining what I mean so I will attempt the explanation again.

My currect Data Workbook is 7 megs, because it contains all the data,
userforms, and module code. I want to make this file smaller by creating an
Add-In that will contain all the userforms and module code to manipulate the
data. This Add-In will also change the CommandBars or Ribbon, depending upon
which version opens the Add-In. If I used the example on the site I would
still need to have all my module code and userforms in the Data Workbook.

 

[RyanH]

I have about 250 rows and 87 columns in 1 Sheet, then I have 20 Sheets with
30 rows and 12 Columns, the a Chart Sheet. I removed all of the userforms
and code from the workbook and there is only a 300KB difference. Interesting
to me, I thought code took more space than that.

I still think I will create two Add-Ins because the workbook is renamed and
saved each day and if I happen to find a bug (and I'm sure I will) it would
be useful to only make the correction in the Add-In instead of all the
different workbooks, right?

Thanks for all your help!

 

[Jim Rech]

I still think I will create two Add-Ins

That's what you need. Your primary add-in will open the RibbonX add-in only
under Excel 2007. The ribbonx will point to code in the primary add-in.

Jim ( author of example on Ron's site)

 

[Ron de Bruin]

Hi Ryan

I see Jim already give you the correct answer
Great that when I sleep people in the US are working <g>