Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Toolbar Ownership?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Toolbar Ownership?
Don,
Could you not put those macros and the toolbar in an add-in, or in Personal.xls? -- HTH ------- Bob Phillips "Don Wiss" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Toolbar Ownership?
On Thu, 13 Jan 2005, Bob Phillips wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Toolbar Ownership?
On Thu, 13 Jan 2005, Bob Phillips wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage of Ownership Formula | Excel Worksheet Functions | |||
How do I take ownership of a document I | Excel Discussion (Misc queries) | |||
Car cost of ownership Excel template | Excel Discussion (Misc queries) | |||
Adjusting toolbar size, restore toolbar | Excel Discussion (Misc queries) | |||
saving toolbar buttons on custom toolbar | Excel Programming |