Hide Custom Toolbar

[Absolution]

Hi all

Long time reader - first time poster. Sounds like some of you clever folk out there might be able to help me with a little problem I am facing.

I have written a workbook with a custom toolbar running macro's. This sheet is used by everyone in my office with a computer and therefore I suffer complaints from many users who just can't seem to get it right.

The Custom toolbar opens and closes in the workbook, but shows on other workbooks if open at the same time.

Problem 1 - How can I make my custom toolbar appear in mid screen rather than up in the toolbar.

Problem 2 - Is there a way to make the toolbar hide when the user is looking at a different sheet (The general public whinge when they get a runtime error because they clicked a button when in a different sheet)

Many thanks in advance

 

[Zack Barresse]

Hi Absolution, welcome to posting!

First off, let me say when creating a custom toolbar or an add-in, it's always best to put into your code expressly the sheet that the code should be affecting; or force the sheet in question to be the activesheet, or even ask if the activesheet is the one you wish to run the code on.

On a related note, if you want to take the toolbar off when the sheet is deactivated, try a sheet event (Worksheet_Deactivate).

How is your code setup? Do you have a routine that will create the toolbar at runtime? Or did you attach the toolbar to the workbook, then distribute? Can you post any/all associated code?

And an example of making a toolbar float (position defined by the individual user and stored in their .xlb file), in this case the Chart toolbar, would look like this ...

Option Explicit

Sub Make_ChartToolbar_Float()
    With Application.CommandBars("Chart")
        .Visible = True
        .Position = msoBarFloating
    End With
End Sub

Does this help?

 

[Cbrine]

Absolution,

Add this code to the Thisworkbook level of your workbook. Update the Custom1 to your commandbar name. Save the workbook. Now the toolbar will appear and disapper when they switch to a new workbook.

Private Sub Workbook_Activate()
ActiveWorkbook.CommandBars("Custom1").Visible = True
ActiveWorkbook.CommandBars("Custom1").Position = msoBarFloating
End Sub

Private Sub Workbook_Deactivate()
ActiveWorkbook.CommandBars("Custom1").Visible = False
End Sub

PS-The toolbar must already exist, either created manually or programatically, for this to work.

HTH
Cal

Edit: Hey Zack, missed the sheet reference, thought it was workbook.

 

[Absolution]

Re: Hide Custome Toolbar

Wow - Quick response.

I have it creating on open. I used some code similar to the code found on these pages under a different thread.

This Workbook is currently

Private Sub Workbook_Open()
    Call CreateToolbar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteToolbar
End Sub

The above references then lead into module 6 with

Global Const gToolbarName = "Profile Options"

Public Sub CreateToolbar()

Dim Tbar As CommandBar
Dim NewBtn As CommandBarButton
Dim NewMenu As CommandBarPopup
Dim NewSubButton As CommandBarButton
Dim NewSubMenu As CommandBarPopup

'Delete the toolbar if it already Exists
Call DeleteToolbar

'Build the Toolbar
Set Tbar = CommandBars.Add

With Tbar
.Name = gToolbarName
.Visible = True
.Position = msoBarTop
'Set position before the formatting menu
.RowIndex = Application.CommandBars("Formatting").RowIndex
End With

'======== Main Menu
Set NewBtn = CommandBars(gToolbarName).Controls.Add

With NewBtn
.Style = msoButtonCaption
.OnAction = "Autocalc"
.Caption = "Autocalc"
.FaceId = 191
.TooltipText = "Automatically calculate entries"
End With

End Sub

Sub DeleteToolbar()

On Error Resume Next
CommandBars(gToolbarName).Delete
Err.Clear

End Sub

There are more buttons on the full sheet but I have the basic skeleton here at home and didn't think it very important to know them

I tried the code Cbrine suggested but got a run time error, although I haven't tried replacing "Custom1" with gToolbarName yet which I think is what I have done wrong here.

 

[Zack Barresse]

Yes, you'll need to do that. Although I'd look at this line ...

With Tbar
.Name = gToolbarName
.Visible = True
.Position = msoBarTop
'Set position before the formatting menu
.RowIndex = Application.CommandBars("Formatting").RowIndex
End With

Why put it to the top, then make it floating? Why not just start out with making it floating?

 

[Absolution]

I just kicked myself and changed that after your post and it is floating.

How about the hiding then, Cbrine's isn't playing ball at the moment

 

[Zack Barresse]

Ensure the name is changed, and ensure the Visible property is actually set to False, not True. ..

ActiveWorkbook.CommandBars(gToolbarName ).Visible = False

 

[Absolution]

Aha !!!!

many thanks my new found genious types.

Now we are very happy.