New Menu tab in Excel 2007

[shantanu]

Hello

I have migrated to excel 2007 from excel 2003.I have few macros which
when executed create a new tab in excel menu . Now when i run the
same
in excel 2007 the macros are seen under Add-In menu in ribbon. Is it
possible to get it on the main menu.


Thanks in advance
Shantanu

 

[Pal]

Actually, Excel 2007 have many changing (lets said that is improvment),
and the new menu bar in Excel 2007 (we call Ribbon) are using XML to create
so, if you want to create new 2007 menu start-alone (instead of add-in menu),
you need to save the excel to .xlsm and then open with a un-zip program,
and then, you need to change some of those file.

i give you a web-site for some guide-line
http://www.xtremevbtalk.com/showthread.php?t=265636

 

[shantanu]

Actually, Excel 2007 have many changing (lets said that is improvment),
and the new menu bar in Excel 2007 (we call Ribbon) are using XML to create
so, if you want to create new 2007 menu start-alone (instead of add-in menu),
you need to save the excel to .xlsm and then open with a un-zip program,
and then, you need to change some of those file.

i give you a web-site for some guide-linehttp://www.xtremevbtalk.com/showthread.php?t=265636







- Show quoted text -

Thanks a lot for the solution, this is fine. But i have a existing
macro and when i am saving as xlsm and then opening in CustomUI Editor
then its saying file is corrupt.
how can i resolve it

 

[shantanu]

Actually, Excel 2007 have many changing (lets said that is improvment),
and the new menu bar in Excel 2007 (we call Ribbon) are using XML to create
so, if you want to create new 2007 menu start-alone (instead of add-in menu),
you need to save the excel to .xlsm and then open with a un-zip program,
and then, you need to change some of those file.

i give you a web-site for some guide-linehttp://www.xtremevbtalk.com/showthread.php?t=265636







- Show quoted text -

I was able to create the TAB but was unable to attach the code any
idea how to attach the existing macro code

 

[Ron de Bruin]

Hi shantanu

Start here
http://www.rondebruin.nl/ribbon.htm

Or this one (easy)
http://www.rondebruin.nl/qat.htm

 

[shantanu]

Hishantanu

Start herehttp://www.rondebruin.nl/ribbon.htm

Or this one (easy)http://www.rondebruin.nl/qat.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm







- Show quoted text -

Hello
Thanks for the awesoem examples, but i am yet not able to get what i
require. After going thru the steps of converting the existing 2003
excel macro to xlsm fine and adding the customUI, i am able to create
a new tab but i am unable to connect the VBA code and the logic with
the menu. Please help

Regards
Shantanu

 

[Ron de Bruin]

In the RibbonX this will add a button for example

<button id="customButton1" label="Caption 1" size="normal" onAction="Macro1" imageMso="DirectRepliesTo" />

When you click the button it run the macro "Macro1"

This looks like this in a standard module of your workbook

Sub Macro1(control as IRibbonControl)

End Sub

And not like this

Sub Macro1()

End Sub

Is this your problem ?



--

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

Hishantanu

Start herehttp://www.rondebruin.nl/ribbon.htm

Or this one (easy)http://www.rondebruin.nl/qat.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm







- Show quoted text -

Hello
Thanks for the awesoem examples, but i am yet not able to get what i
require. After going thru the steps of converting the existing 2003
excel macro to xlsm fine and adding the customUI, i am able to create
a new tab but i am unable to connect the VBA code and the logic with
the menu. Please help

Regards
Shantanu

 

[shantanu]

Hello

This is in continuation of the above question

I am discussing the steps i have implemented

1. I save the macro file as .xlam
2. In Custom UI Editor i created the xml file to add menu item
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/
customui">
<ribbon>
<tabs>
<tab id="CapPlanID" label="Capacity Planning">

<group id="CapPlanGrp" label="Capacity Planning">
<button id="CapPlan" label="Capacity Planning"
onAction="VBAMacros_USE_v0.2.xls!BtnOnActionCall" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++

3. In the VBA on the worksheet load page i wrote the code

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++
Option Explicit

Const RibbonxAddin As String = "VBAMacros_USE_v0.2.xlam"

Private Sub Workbook_Open()

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Val(Application.Version) > 11 Then
On Error Resume Next
Workbooks("VBAMacros_USE_v0.2.xlam").Close False
Else
DeleteMenu
End If
End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++

Also Created modules
+++++++++++++++++++++++++
mRibbon
Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
Select Case Ctrl1.ID
Case "CapPlan": ThisWorkbook.CreateMenu
End Select
End Sub

+++++++++++++++++++++++++++++++++++++++

Kindly tell me where i am going wrong.

Thanks