Determining Toolbar Ownership?

[Don Wiss]

My application has a CommandBar. The buttons call macros that then call
another macro in the active workbook. This as multiple workbooks could be
open, and I want the single toolbar to work with whichever is active. When
a workbook is closed, it loops and activates all the other open workbooks,
and if it finds another of its type it does not delete the CommandBar. But
this causes a problem. The CommandBar that is visible could belong to a now
closed workbook. So clicking a button tries to open the workbook so it can
call the first level macro. What I need to determine when a workbook is
closed is whether the toolbar belongs to it. And if so, it has to close the
toolbar and another still open workbook has to recreate it. Or is there
some simpler way that I'm not seeing?

Don <donwiss at panix.com>.

 

[Bob Phillips]

Don,

Could you not put those macros and the toolbar in an add-in, or in
Personal.xls?

 

[Don Wiss]

Could you not put those macros and the toolbar in an add-in, or in
Personal.xls?

Sounds like the solution. I have been planning to put all the user forms
and macros into an add-in. But not until the end of the development
project. I guess I won't be able to wait until then.

Thanks, Don <donwiss at panix.com>.

 

[Don Wiss]

Could you not put those macros and the toolbar in an add-in, or in
Personal.xls?

I got around to implementing it. All works fine. I ended up not putting
anything else in the add-in. I was going to put some functions in, but I
wasn't planning to register the add-in, and with all the hassles of cross
calling, I didn't.

I used this to see whether the add-in already exists:

Function DoesProjectExist(AddInName As String) As Boolean
' addin name is case sensitive. is project name, not file name
Dim W As Object
DoesProjectExist = False
For Each W In Application.VBE.VBProjects
If W.Name = AddInName Then DoesProjectExist = True
Next
End Function

If upon opening the workbook the add-in doesn't exist I simple open it.

Upon closing this macro in the add-in is called:

Sub DeleteToolBar()
' we don't delete the toolbar if another Rating Tool is already open
' Auto_Close of each rating tool calls this.
' this also closes this add-in

Dim wb As Workbook, ActiveName As String

Application.ScreenUpdating = False
ActiveName = ActiveWorkbook.Name

For Each wb In Application.Workbooks
If wb.Name <> ActiveName Then
wb.Activate
If IsWorksheetOpen("HiddenSheet") Then Exit Sub
End If
Next wb

On Error Resume Next
Application.CommandBars("CasFacToolbar").Delete
ThisWorkbook.Close SaveChanges:=False

End Sub

Function IsWorksheetOpen(worksheetname As String) As Boolean
' function tests for worksheet by that name exists

Dim shName As Worksheet

IsWorksheetOpen = False
For Each shName In Application.Worksheets
If shName.Name = worksheetname Then
IsWorksheetOpen = True
End If
Next shName

End Function