How to Disable certain Menu options in Excel within VB calls ?

[WYokeCheng]

I need to develop an application using VB, and within my program call up an existing Excel sheet to allow user to do entry. Afterwhich the control need to pass back from Excel application to my VB program to do authentication and then my program will save the Excel file to another file name according to a specific directory path and filename.

My problems here are :

a) How can I disable most of the options in Excel (leaving only perhaps the 'Exit' option), so that the user cannot perform other operations, e.g. save the file by themselves?

b) Will my VB program be able to save the Excel file after the user has quit the Excel applicaiton? Or is it possible to create a special button in the Excel sheet for user to click when they want to return back to my program?

I've found some threads on how to activate Excel application and saving the file, but they still can't solve my problems. Anyone has the solutions pls help. Thanks in advance.

 

[sdraper]

You could disable the desired menu options by using the Workbook's Open event. The code below is from MSDN
you could place ActiveWorkbook.save in the BeforeClose event of the workbook.

Function CBToolbarShow(strCBarName As String, _
blnVisible As Boolean, _
Optional lngPosition As Long = msoBarTop) As Boolean

' This procedure displays or hides the command bar specified in the
' strCBarName argument according to the value of the blnVisible
' argument. The optional lngPosition argument specifies where the
' command bar will appear on the screen.

Dim cbrCmdBar As CommandBar

On Error GoTo CBToolbarShow_Err

Set cbrCmdBar = Application.CommandBars(strCBarName)

' Show only toolbars.
If cbrCmdBar.Type > msoBarTypeNormal Then
CBToolbarShow = False
Exit Function
End If
' If Position argument is invalid, set to the default
' msoBarTop position.
If lngPosition < msoBarLeft Or lngPosition > msoBarMenuBar Then
lngPosition = msoBarTop
End If

With cbrCmdBar
.Visible = blnVisible
.Position = lngPosition
End With

CBToolbarShow = True

CBToolbarShow_End:
Exit Function
CBToolbarShow_Err:
CBToolbarShow = False
Resume CBToolbarShow_End
End Function

 

[WYokeCheng]

sdraper,

Thanks for yr prompt reply.
For the Open and BeforeClose events, u are refering to the VB Editor in Excel, am I right?

Anyway, I tried putting the function CBToolbarShow in my VB program, somehow it is working. But this is for toolbar only. How can I hide the Excel Menu Bar in my VB program (i.e. the File, Edit, Format,....Help) ? My users only allow to close the Excel application and then return to my program and nothing else. I need to prevent them from doing Save or SaveAs on their own in Excel; these functions will be handled in my VB program.

Any idea. Or can I add in Excel as an object in my VB program instead of running the Excel in a separate instance.
If Yes, HOW ?

Thanks again.