how to control menus in Excel thru VBA Code

[v5652]

i have created a form in Access 2003 and thru that i have opened an Excel file. but i doesn't want to give access to print that excel file means i want to control menus in Excel thru VBA code.

is it possible...?
if any one have suggesion...
kindly reply.
thank u.

 

[grantwilliams]

Hi v5652,

If you're opening the same Excel file every time, put this code in "ThisWorkbook" in the VB Editor in Excel.

Private Sub Workbook_Open()

    Application.CommandBars("Worksheet Menu Bar").Enabled = False

End Sub

Cheers,

Grant

 

[Remou]

forum707 is a good place for this question.

 

[v5652]

Private Sub Workbook_Open()

Application.CommandBars("Worksheet Menu Bar").Enabled = False

End Sub

thanks for reply.

i tried above code. it works fine.

but afterword if i open excel, it doesn't show menu bar.

can you help me about it.

 

[grantwilliams]

Hi v5652,

I have the same problem if I open the spreadsheet directly.

I don't get the problem if I open the spreadsheet from Access with the following code:

Private Sub ExcelButton_Click()

    Dim MyExcel  As New Excel.Application
    Dim XLDoc As Excel.Workbook
    Set XLDoc = MyExcel.Workbooks.Open("Full path to the Excel spreadsheet")

    With MyExcel
        .Visible = True
    End With
    
    Set MyExcel = Nothing

End Sub

For this code to work, you need to have the Microsoft Excel Object Library checked in the VBA references.

Hope this helps,

Grant

 

[v5652]

hi grant,

i have solved above problem.

i have another one more problem that i can't control on Shortcut Menus like Cut,Copy, Paste, Print(Ctrl+P), Ctrl+S.

so how to control/disable these menus.

thanks in advance.

 

[mudstuffin]

If you just want to disable the printing, you could put Cancel=true in the beforeprint event of the workbook. This will stop the printing from the menu or CTRL 'P'


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub


You could include a msgbox or change the status bar to let the user know its disabled otherwise they might keep trying.

VBA code will only work if macros are enabled and the workbook is not put in design mode from the control toolbox though.

Philosophical and slightly witty comment to follow here....

 

[v5652]

hi mudstuffin

thanks for the reply
i will try this

thanks again.