View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Anthony[_15_] Anthony[_15_] is offline
external usenet poster
 
Posts: 18
Default Upgrade VBA code from 2003 to 2007

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

"lmzhuee" wrote in message
...

Dear all,

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

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

It is easy to implement that in Excel 2003 throught controlling
Commandbar object. but for excel 2007, I found ribbon is totally
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 the
normal program.

What I already knew:
I found some articles in MSDN, which tell me that customUI.xml can be
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 as
list of recent open file remains

What I'd like to ask he
1. how to remove all buttons and list of recent open file on office
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 this
project, I am not aware of what kinds of problem I may meet, can anyone
share some general tips ?


--
lmzhuee
------------------------------------------------------------------------
lmzhuee's Profile: http://www.thecodecage.com/forumz/member.php?userid=683
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=125794