Upgrade VBA code from 2003 to 2007

[lmzhuee]

Dear all,

I'm working on a project to upgrade a excel-vba based program fro
excel 2003 to 2007.

The program is a stand-alone excel file with VBA code, in order t
pretend to be a normal program, it hide all excel menus and disable al
excel functions, replace with its own menu.

It is easy to implement that in Excel 2003 throught controllin
Commandbar object. but for excel 2007, I found ribbon is totall
differet with classic commandbar, so I need some help.

What I want to do is:
Hide all excel menus, replace with my own stuff, make it looks like th
normal program.

What I already knew:
I found some articles in MSDN, which tell me that customUI.xml can b
used to modify the ribbon menu. I tried and it did work for ribbon tabs
but still 3 buttons in office menu "New", "Open", "Save", as well a
list of recent open file remains

What I'd like to ask here:
1. how to remove all buttons and list of recent open file on offic
menu ?
2. Is it the easiest way to use xml file to customize the ribbon menu
if not, what's other possibilities ?
3. for vba code upgrading from 2003 to 2007, as I just start thi
project, I am not aware of what kinds of problem I may meet, can anyon
share some general tips

 

[Anthony]

try this. you might need to adapt a little as this is cut out of a project i
have done

Sub Disable_on_version07()

'turn off autosave
Application.AutoRecover.Enabled = False

'ExecuteExcel4Macro portion by Jim Rech
'Other code Nick hodge
With Application
.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
.CommandBars("Status Bar").Visible = False
.DisplayFormulaBar = False
.DisplayScrollBars = True
.ActiveWindow.DisplayHeadings = False
.ActiveWindow.DisplayWorkbookTabs = False
End With

KeyCombos (False)

'make all the worksheets visible
For Each c In Sheets
c.Visible = True
Next c

End Sub

Sub enable_on_version07()

'turn on autosave
Application.AutoRecover.Enabled = True

'ExecuteExcel4Macro portion by Jim Rech
'Other code Nick hodge
With Application
.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",true)"
.CommandBars("Status Bar").Visible = True
.DisplayFormulaBar = True
.DisplayScrollBars = True
.ActiveWindow.DisplayHeadings = True
.ActiveWindow.DisplayWorkbookTabs = True
End With

KeyCombos (True)

'make all the worksheets visible
For Each c In Sheets
If (c.Visible = False) Then
c.Visible = True
End If
Next c
End Sub

the ribbon code section was found online and is not mine so I am not taking
credit for it.

the key combos function is just a call to turn of key strokes.

regards

Anthony

 

[Ron de Bruin]

but still 3 buttons in office menu "New", "Open", "Save", as well as

list of recent open file remains

See the Dictator example on my site
http://www.rondebruin.nl/ribbon.htm


--

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

 

[lmzhuee]

Hi Ron de Bruin,

I downloaded the example from your site, it's exactly what I need,
especially the code to get rid of MRU list is interesting and practical,
quite helpful for me, thank you very much.

Thanks for sharing your project, Anthony, I learned an easier way to
deal with Ribbon.