Excel 2007: Disable Office Menu control on the fly

[Gary Nguyen]

I'm looking for a way to disable Office Menu controls such as: New, Save,
SaveAs, etc on the fly. Whenever user opens an excel workbook into my
application, those controls under Office Menu are disable.
In office 2003, I could do something like this when user opens their
workbook in my application:
With objExcel.CommandBars("Worksheet Menu Bar")
.Controls("&File").Controls("&New...").Enabled = False
.Controls("&File").Controls("&Open...").Enabled = False
.Controls("&File").Controls("&Close").Enabled = False

Thanks

 

[Ron de Bruin]

Hi Gary

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

Download the example workbooks (point 2)

 

[Gary Nguyen]

Hi Ron,
Thanks for your reply. As I read your website and previous threads within
this forum, this is what I understand from your website:
- let say users have Workbook A
- Users have to use CustomUI to customize Ribbon manually through .xml file.
And the changes to Ribbon are only effected for Workbook A.

Is there a way we can automate this?
And if user open up workbook A, B, C, etc. into my application, can I be
able to disable some controls under Office Menu as I did before with Office
2003?
I'd appreciate for your help.
Thanks

 

[Ron de Bruin]

Hi Gary

If you save your workbook with ribbonx changes as a add-in it
is not only for one workbook but for all workbooks.

 

[Ron de Bruin]

More info

You can let your workbook open the addin with code when it opens and close the add-in when you close the workbook.

Good night

 

[Gary Nguyen]

Ron, thanks very much for your reply.
I'm new to Office Development as well. I'm fixing a bug in our application
that can adapt with Office 2007. Can you please point me further instructions
where I can integrate this workbook into Excel as Addin?
By the way, I hope this method should work for Word and PowerPoint as well.
I'd appreciate your help.
Thanks

 

[Ron de Bruin]

Hi Gary

There are dictator workbook examples on this page that you can save as a add-in
http://www.rondebruin.nl/ribbon.htm

See point 2

 

[Gary Nguyen]

Ron,
Thanks for your reply. I already had an add-in. I'm now looking for code
that can hook up this add-in into excel so every time user open/close a
workbook in my application, my code will attach/detach this add-in.
Thanks,
Chau

 

[Ron de Bruin]

Hi Gary

Basic code in the Thisworkbook module looks like this of the add-in is in the same folder
as the workbooks (if not change the path)

Private Sub Workbook_Activate()
If bIsBookOpen_RB("MenuAddin.xlam") = False Then
Workbooks.Open (ThisWorkbook.Path & "\MenuAddin.xlam")
End If
End Sub

Private Sub Workbook_Deactivate()
If bIsBookOpen_RB("MenuAddin.xlam") = True Then
Workbooks("MenuAddin.xlam").Close False
End If
End Sub

Function bIsBookOpen_RB(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen_RB = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

 

[Gary Nguyen]

Wow, it works. With this code, you actually attach/detach the template
whenever user open/close a workbook.
Dump me while I'm looking for a way to "add-in" this template into workbook.
Anyway, thanks alot for your help.
I'll grade this message.