Excel 2003 - VBA - Toolbar Control

[Craig Brandt]

I now create floating toolbars at the drop of a hat. In fact, I'm soooo good
at it that at times I may have two or three just sitting there. This is not
what I intended.
In a minimalist nutshell, this is the code I use to generate them:



Public WBToolbar As Office.CommandBar



Public Sub FltgToolbar()



' Close previous Toolbar

On Error Resume Next

Application.CommandBars("WBToolbar").Delete

With Application.CommandBars.Add

.Name = "WBToolbar"

.Left = 300

.Top = 200

.Visible = True

.Position = msoBarFloating

' Today Button



With .Controls.Add(Type:=msoControlButton)

.Style = msoButtonIconAndCaption

.OnAction = "DLAcct"

.Caption = "Import Accounts "

.FaceId = 173

.TooltipText = "Downloads Current Data"

End With



Etc..



What I thought I would do was create the following line of code and place it
in the "Workbook_WindowDeactivate" sub in ThisWorkbook:



WBToolbar.visible = False



Thinking that the bar would disappear when I switched to a different
worksheet.

What am I missing?

Craig

 

[Jim Rech]

What am I missing?


You switched to a different worksheet in the same window. So you'd want to
use the sheet deactivate event. If more than one window to the same
workbook could be open you would want to use the window deactivate event
too, only adding a change to see whether the window is open to the same
worksheet (don't hide toolbar) or a different one (hide toolbar).

--
Jim
|I now create floating toolbars at the drop of a hat. In fact, I'm soooo
good
| at it that at times I may have two or three just sitting there. This is
not
| what I intended.
| In a minimalist nutshell, this is the code I use to generate them:
|
|
|
| Public WBToolbar As Office.CommandBar
|
|
|
| Public Sub FltgToolbar()
|
|
|
| ' Close previous Toolbar
|
| On Error Resume Next
|
| Application.CommandBars("WBToolbar").Delete
|
| With Application.CommandBars.Add
|
| .Name = "WBToolbar"
|
| .Left = 300
|
| .Top = 200
|
| .Visible = True
|
| .Position = msoBarFloating
|
| ' Today Button
|
|
|
| With .Controls.Add(Type:=msoControlButton)
|
| .Style = msoButtonIconAndCaption
|
| .OnAction = "DLAcct"
|
| .Caption = "Import Accounts "
|
| .FaceId = 173
|
| .TooltipText = "Downloads Current Data"
|
| End With
|
|
|
| Etc..
|
|
|
| What I thought I would do was create the following line of code and place
it
| in the "Workbook_WindowDeactivate" sub in ThisWorkbook:
|
|
|
| WBToolbar.visible = False
|
|
|
| Thinking that the bar would disappear when I switched to a different
| worksheet.
|
| What am I missing?
|
| Craig
|
|

 

[Jim Rech]

only adding a change to see whether

Should be "check to see..."

--
Jim
| >What am I missing?
|
|
| You switched to a different worksheet in the same window. So you'd want
to
| use the sheet deactivate event. If more than one window to the same
| workbook could be open you would want to use the window deactivate event
| too, only adding a change to see whether the window is open to the same
| worksheet (don't hide toolbar) or a different one (hide toolbar).
|
| --
| Jim
| ||I now create floating toolbars at the drop of a hat. In fact, I'm soooo
| good
|| at it that at times I may have two or three just sitting there. This is
| not
|| what I intended.
|| In a minimalist nutshell, this is the code I use to generate them:
||
||
||
|| Public WBToolbar As Office.CommandBar
||
||
||
|| Public Sub FltgToolbar()
||
||
||
|| ' Close previous Toolbar
||
|| On Error Resume Next
||
|| Application.CommandBars("WBToolbar").Delete
||
|| With Application.CommandBars.Add
||
|| .Name = "WBToolbar"
||
|| .Left = 300
||
|| .Top = 200
||
|| .Visible = True
||
|| .Position = msoBarFloating
||
|| ' Today Button
||
||
||
|| With .Controls.Add(Type:=msoControlButton)
||
|| .Style = msoButtonIconAndCaption
||
|| .OnAction = "DLAcct"
||
|| .Caption = "Import Accounts "
||
|| .FaceId = 173
||
|| .TooltipText = "Downloads Current Data"
||
|| End With
||
||
||
|| Etc..
||
||
||
|| What I thought I would do was create the following line of code and place
| it
|| in the "Workbook_WindowDeactivate" sub in ThisWorkbook:
||
||
||
|| WBToolbar.visible = False
||
||
||
|| Thinking that the bar would disappear when I switched to a different
|| worksheet.
||
|| What am I missing?
||
|| Craig
||
||
|