The Windows menu file list...

[GVF]

I have a custom menu bar with a "Windows" menu. Most of the commands are custom but I would like to emulate the File List of open workbooks that the standard "Windows" menu uses.

Is there a way to Get the "file list" of open workbooks from the standard Windows menu and paste it to my custom menu. I would want it to work exactly the same as the standard file list works (activate the window, check the active window name).

I have a file list that I use but as the windows change place in the order, they move around on the file list and it isn't neat.

 

[CautionMP]

And the answer is...

[blue]Public Sub[/blue] CurrentWorkbookCommandBar()
[blue]Dim[/blue] oWorkbook [blue]As[/blue] Workbook
[blue]Dim[/blue] NewCommandBar [blue]As[/blue] CommandBar
[blue]Dim[/blue] NewGroup [blue]As[/blue] CommandBarPopup
[blue]Dim[/blue] NewItem [blue]As[/blue] CommandBarControl
[green]
'Create the new Command bar and make it temporary[/green]
[blue]Set[/blue] NewCommandBar = CommandBars.Add("FileList", , , [blue]True[/blue])
NewCommandBar.Visible = [blue]True[/blue]
[green]
'Create a New group on the command bar called Open Files[/green]
[blue]Set[/blue] NewGroup = NewCommandBar.Controls.Add(msoControlPopup)
[blue]With[/blue] NewGroup
  .Caption = "Open Files"
[blue]End With[/blue]
[green]
'Iterate through the open workbooks to get their names[/green]
[blue]For Each[/blue] oWorkbook [blue]In[/blue] Excel.Application.Workbooks
  [green]'For each open workbook create a new button[/green]
  [blue]Set[/blue] NewItem = NewGroup.Controls.Add(msoControlButton)
  [blue]With[/blue] NewItem
    [green]'Name the button[/green]
    .Caption = oWorkbook.Name
    [green]'Show the text with no image[/green]
    .Style = msoButtonCaption
    [green]'Depress the button of the current workbook[/green]
    [blue]If[/blue] oWorkbook.Name = ActiveWorkbook.Name [blue]Then[/blue]
      .State = msoButtonDown
    [blue]End If[/blue]
  [blue]End With[/blue]
[blue]Next[/blue] oWorkbook
[green]
'Clean up[/green]
[blue]Set[/blue] NewItem = [blue]Nothing[/blue]
[blue]Set[/blue] NewGroup = [blue]Nothing[/blue]
[blue]Set[/blue] oWorkbook = [blue]Nothing[/blue]
[blue]End Sub[/blue]

Now the hard part is the [tt]OnAction()[/tt] events and the updating which workbbok is shown as active in the menu.

Hope this helps,
CMP


Instant programmer, just add coffee.

 

[GVF]

The code clip is essentially what I am doing now. When I iterate through the code when a new workbook is opened, the workbook order is changed and the open workbook names change places on the Windows menu.

What I would like to do is copy the Windows menu from the Worksheet menubar to my custom menu bar. Then I would change the onaction and caption of the first couple of commands and I would be home free.

Can you set a variable to a complete menu? Can you set a variable to the file list?

 

[CautionMP]

Yes and yes.

To correct the changing of the order on the open files you can try this:

Public Sub TestWorkbooks()
Dim wkbActive As Workbook
Dim intWorkbook As Integer
For intWorkbook = 1 To Excel.Application.Workbooks.Count
  Debug.Print Workbooks(intWorkbook).Name
Next intWorkbook
End Sub

Since this calls the workbooks by number (from the ROT) the order should relfect the order they are opened.

To recreate the Windows menu, this snipit will deconstruct the current menu item:

Public Sub ShowItemsInWindowPopup()
Dim cmdFile As CommandBar
Dim grpWindows As CommandBarPopup
Dim ctlOpenFiles As CommandBarControl
Set cmdFile = CommandBars("Worksheet Menu Bar")
Set grpWindows = cmdFile.Controls("Window")
For Each ctlOpenFiles In grpWindows.Controls
  Debug.Print ctlOpenFiles.Caption
Next ctlOpenFiles
End Sub

NOTE:This suprised me with a little funky output (below), I didn't realize that Excel would only show 10 open windows:[tt]
&Hide
&Unhide...
&Split
&Freeze Panes
&1 SB_0000833700_05212005_ROTH.csv
&2 mortgage.xls
&3 Book1
&Window Name Goes Here
&Window Name Goes Here
&Window Name Goes Here
&Window Name Goes Here
&Window Name Goes Here
&Window Name Goes Here
&Window Name Goes Here[/tt]

Hope this helps,
CMP



Instant programmer, just add coffee.

 

[GVF]

I was looking into this further, and I found that I could de-construct the file list. Each file gets it's own control though and then each control would have to be configured. I was back where I started.

If I add a control to my menu, and set the ID to 830, I get a button with a caption that says "&Window name goes here". Clicking the button brings up a dialog box with the file list.

I am going to get rid of my fancy code and just open the dialog box since it is easiest and the file list is always correct.

Application.Dialogs(xlDialogActivate).Show

and since my OnWindow events run when a window is selected, I am happy.

Thanks for your help
Greg