PDA

View Full Version : Disable commandbars in Excel 2007 - through VBA



aravindhan_3
12-16-2011, 12:56 AM
Hi,

I have the below code which will disable SaveAs, Save, SendTo buttons if the cell value in C1= Error, this is working perfectly in excel 2003,

Can someone help me to run the similar command in 2007 aslo, that is if the user is using 2003, then run this, else
run the code for 2007.
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim LR As Long
Dim row As Integer
If Worksheets("Item Request Form").Range("E1").Value <> "HSC" Then
If Worksheets("Item Request Form").Range("C1").Value = "Error" Then
MsgBox "Sheet incomplete, please check", vbCritical, "Input required"
' Following line will prevent all saving
Cancel = True
' Following line will prevent the Save As Dialog box from showing
SaveAsUI = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Send To").Enabled = False
Else
SaveAsUI = True
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Send To").Enabled = True
End If
End If
End Sub

Thanks for your help.
Cross post: http://www.mrexcel.com/forum/showthread.php?t=598925

Aflatoon
12-16-2011, 02:23 AM
You cannot do that in code in 2007. You need to add custom XML to a 2007 format workbook to alter parts of the ribbon.