"Custom 1" Toolbar Visibility

[tdm100]

Howdy,

I desire to have a specific Toolbar "Custom 1" be visible when and only when the parent workbook "Book1" and "Sheet1" are active.
Any other time, for example Selecting "Book2" from "Book1""Sheet1"
Toolbar "Custom 1" shall not be visible.

Thanx in advance

[Bob Phillips]

Create the toolbar dynamically in the workbook open event, and workbook activate event, and delete it in the beforeclose and deactivate events.

[Erdin? E. Ka]

Hi ,

(I modified this codes from J-Walk's Calculator Sample)

To Sheet1's code page:

[vba]Private Sub Worksheet_Activate()
Dim Ctl As CommandBarControl
Dim MenuItem As CommandBarControl
On Error Resume Next
Set Ctl = Application.CommandBars(1).FindControl(ID:=30007)
If Ctl Is Nothing Then
MsgBox "Adding A NewMenu To Tools Menu.", vbCritical, ApplicationName
Else
Ctl.Controls(ApplicationName).Delete
On Error GoTo 0
Set MenuItem = Ctl.Controls.Add
With MenuItem
.Caption = ApplicationName
.OnAction = "Show_ToolBar"
End With
End If
On Error GoTo 0
Show_ToolBar
End Sub[/vba]

[vba]
Private Sub Worksheet_Deactivate()
Dim Ctl As CommandBarControl
Dim MenuItem As CommandBarControl
On Error Resume Next
Set Ctl = Application.CommandBars(1).FindControl(ID:=30007)
Ctl.Controls(ApplicationName).Delete
On Error GoTo 0
End Sub
[/vba]



And to Module1's code page:

[vba]
Option Explicit
Option Private Module
Public Const ApplicationName As String = "Custom 1"

Sub Show_ToolBar()
Dim ComBarCalculator As CommandBar
On Error Resume Next
Set ComBarCalculator = Application.CommandBars(ApplicationName)
If Err.Number = 0 Then
ComBarCalculator.Visible = Not ComBarCalculator.Visible
On Error GoTo 0
Exit Sub
Else
Call CreatToolBar
End If
On Error GoTo 0
End Sub
[/vba]

[vba]
Sub CreatToolBar()
Dim ComBarCalculator As CommandBar
Dim Ctl As CommandBarControl
Dim SubCtl As CommandBarControl
On Error Resume Next
Application.CommandBars(ApplicationName).Delete
On Error GoTo 0
Set ComBarCalculator = Application.CommandBars.Add(ApplicationName)
Set Ctl = Application.CommandBars(ApplicationName).Controls.Add(Type:=msoControlEdit)
Ctl.Width = 173
AddButton 30, "1", "This is an Example Button"
AddButton 30, "2", "This is an Example Button"
AddButton 30, "3", "This is an Example Button"
Set Ctl = Application.CommandBars(ApplicationName).Controls.Add(msoControlPopup)
End Sub
[/vba]

[vba]
Sub AddButton(Width, Caption, Optional ToolTip)
Dim Dugme As CommandBarButton
Set Dugme = Application.CommandBars(ApplicationName).Controls.Add
With Dugme
.Style = msoButtonCaption
.Width = Width
.Caption = Caption
.State = msoButtonDown
.OnAction = "ButtonClick"
If Not IsMissing(ToolTip) Then .TooltipText = ToolTip
If Caption = "" Then .Enabled = False
End With
End Sub
[/vba]

[vba]
Sub ButtonClick()
Dim Dugme_Indeksi
Dim Dugme As Variant
Dugme_Indeksi = Application.Caller(1)
Dugme = Application.CommandBars(ApplicationName).Controls(Dugme_Indeksi).Caption
Select Case Dugme
Case 1
MsgBox "You clicked to 1 (One)"
Case 2
MsgBox "You clicked to 2 (Two)"
Case 3
MsgBox "You clicked to 3 (Three)"
End Select
End Sub
[/vba]

[tdm100]

Thanx for the responses, I appreciate it

I do create the toolbar dynamically
& use the following events:
In the worksheet module
Private Sub Worksheet_Activate()
Private Sub Worksheet_Deactivate()
In the ThisWorkBook Module
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)

What I am trying to accomplish now is when I move from the activesheet in question,
( and hence the toolbar is visible) to the VBE the toolbar will remain visible.

I desire to have the Toolbar NOT visible while the VBE is active(?)
(and conversely the Toolbar BE visible again when I leave the VBE)
I incorporated the,
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
but to no avail

Is my desire possible?

[Bob Phillips]

ARe you saying that you craet a toolbar in Excel, and when you go to VBE you see that toolb ar amongst the VBE toolbars?

[tdm100]

ARe you saying that you craet a toolbar in Excel, and when you go to VBE you see that toolb ar amongst the VBE toolbars?

Yes, the toolbar is created in Excel via VBA
No other Toolbars (from excel) are shown in the VBE except for the toolbar in question.

After this toolbar is created on the fly,,and if I move from that activesheet to the VBE,,,that Toolbar will remain visible while I am in the VBE

The dynamically created Toolbar is Temporary and is a floater and I position it right smack in the middle of the activesheet to which it is assigned.
lol,,,,so when I goto the VBE it's visible right smack in the middle of the screen covering my code.
(Naturally when I attempt to close the Toolbar,, to get it out of my way,,, I will snap back to the activesheet from the VBE,,,, a pain in the butt, and the main reason why I want to eliminate it)

If I do anything else,, from that activesheet,,, such as activate another workbook or activate another sheet,, the toobar will behave like I want it to.
So it behaves accordingly at all other times except when I goto the VBE