Managing Excel menu's for embedded OLE control

Hello Nico,

tried but failed ...

private barcount as integer
private cbars() as boolean

Private Sub test_exit()
Dim ExcelApp
Dim i

  Set ExcelApp = Me.test.Object.Application

  For i = 1 To barcount
    ExcelApp.CommandBars.Item(i).Visible = cbars(i)
  Next

End Sub

Private Sub test_DblClick(Cancel As Integer)
Dim ExcelApp
Dim i

  Set ExcelApp = Me.test.Object.Application
  barcount = ExcelApp.CommandBars.Count

  ReDim cbars(barcount)

  For i = 1 To ExcelApp.CommandBars.Count
    If (ExcelApp.CommandBars.Item(i).Visible) And (ExcelApp.CommandBars.Item(i).Name <> "Worksheet Menu Bar") Then
      cbars(i) = ExcelApp.CommandBars.Item(i).Visible
      ExcelApp.CommandBars.Item(i).Visible = False
    End If
  Next

End Sub

there seems to be no way to control the close event of the excel object in the ole control

another way would be

-create an excel application object
-handle the bars
-keep a global boolean array to store the bar status visible or not
-then wait for the control to be back to the access app
-check if the excel object was closed is nothing
-then create a hidden excel obejct
-restore the bars
-then close the excel app

but this is very very dirty ... hopefully someone else has a good idea

:O)Bruintje

Thanks bruintje,

I was thinking and coding the same way, the closure was no problem using the CommandBars object, but when the control is active I couldn't "get it under control".

The right-click should also be disabled as that would allow activation too....

Hope another expert gets an "AHA-erlebniss" ;-)

Nic;o)

Ran into:

Me.SubForm.Form.objTheObject.Verb = acOLEVerbInPlaceActivate
Me.SubForm.Form.objTheObject.Action = acOLEActivate

But it doesn't work.

Using an unbound OLE object frame for excel the form just shows the sheet very short before switching back...

Nic;o)

Am I missing something here? Couldn't you just do something like:

Private Sub mySheet_GotFocus()

With mySheet.Object.Application
    For i = 1 To .CommandBars.Count
        .CommandBars(i).Enabled = False
    Next
End With

End Sub

Private Sub mySheet_LostFocus()

With mySheet.Object.Application
    For i = 1 To .CommandBars.Count
        .CommandBars(i).Enabled = True
    Next
End With

End Sub

Hi mcallarse,

Thanks for looking into this.
Your Got/Lost focus is what we tried, but when the excel sheet has been "fired" then you loose "control". The LostFocus is e.g. also triggered when the form is opened and the closure of the excel application is "out of reach".

As there should only be cells available, we also looked into a straight opening of the object "within" the form.
(See my last comment), but we weren't successfull either....

A rather fustrating problem...

Nic;o)

Looks great !

I posted the Q for a collegue, as I got stuck myself :(

I'll ask him to check your sample too.
Hope to finalize this tomorrow, thanks again !

Nic;o)

My collegue did actually test this, but he ran into the problem that when opening another excelsheet on the same machine it does appear to have no command bars either.

The code actually hides them on an opened excelsheet too as I experienced, as closing the form will activate them again.

That's also the reason for the use of the "acOLEVerbInPlaceActivate", trying to keep it local to the access application.

Nic;o)

The second try proved to work.
For those interested:

Dim myExcel As clsXL

Private Sub Form_Load()

Set myExcel = New clsXL

With myExcel
    .CallingCard = Me.Caption
    Set .appExcel = mySheet.Object.Application
End With

End Sub

Private Sub Form_Unload(Cancel As Integer)

Set myExcel = Nothing

End Sub

Great job mcallarse !

Nic;o)

Thanks Nico...glad to help.

For any interested:

Needed Excel to call the Excel-specific event so had to use WithEvents. Each Excel sheet is part of a workbook, and found that Excel includes the Access Form _caption_ in the auto-assigned workbook name of an embedded sheet. clsXL instructs the Excel application to disable any enabled menus when a workbook is activated with a name that includes the Access Form name (CallingCard). Any disabled menus are enabled when this condition is not met, and when the class is destroyed.

clsXL:

Public WithEvents appExcel As Excel.Application
Public CallingCard

Private Sub appExcel_WorkbookActivate(ByVal Wb As Excel.Workbook)

Dim cmdBar

If InStr(Wb.Name, CallingCard) > 0 Then
    For Each cmdBar In appExcel.CommandBars
        If cmdBar.Enabled Then cmdBar.Enabled = False
    Next
Else
    Debug.Print Wb.Name
End If

End Sub

Private Sub appExcel_WorkbookDeactivate(ByVal Wb As Excel.Workbook)

Dim cmdBar

If InStr(Wb.Name, CallingCard) > 0 Then
    For Each cmdBar In appExcel.CommandBars
        If Not cmdBar.Enabled Then cmdBar.Enabled = True
    Next
End If

End Sub

Private Sub Class_Terminate()

Dim cmdBar

For Each cmdBar In appExcel.CommandBars
    If Not cmdBar.Enabled Then cmdBar.Enabled = True
Next

End Sub