Travelling Toolbars and hidden macro sheets

[Balsak]

Hi all,
I have created a custom toolbar that I want to
automatically display but only when a specific workbook
is opened. I don't want it there for anything else. Any
ideas??

Secondly, I have created a macro driven workbook. There
are public and private macros that refer to specific
sheets in the workbook and react to specific events such
as save, change or open. I want to split the macros out
and store them in another book - similar to an XLA. But
I don't want to use an XLA as once an addin is active, it
applies to all active workbooks and this can't happen.

So I want to create a hidden workbook that is
automatically opened by the host and is (to all intents
and purposes) invisible to the novice user.

I know about the XLStart folder but wondered if there was
a more elegant way.

Grateful for any help ...

Best regards,
Balsak

 

[Frank Kabel]

Hi
not sure why not to use an add-in? Depends on the macros you want to
use.

 

[Guest]

Hi,
Because an add-in would execute the event macros on every
open workbook not just the one I want it to work on.
Thanks.

Regards,
Balsak

 

[Frank Kabel]

Hi
you could place the processing logic in the add-in. You have to course
these macros in the workbook events of the desired workbook. The add-in
events will only be called if you open the addin for example

 

[Bernie Deitrick]

Balsak,

You could open another file using the workbook open event, and run macros
from that workbook by using the Call command.

As for the toolbars, simply read the message below that I've posted a few
dozen times....

HTH,
Bernie
MS Excel MVP

Here's my standard instructions/reply:

The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed.
Follow these instructions and example code.

In the workbook's Thisworkbook object code module, place the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub

Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub

In a regular code module, place the following:

Dim myBar As CommandBar
Dim myButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With

End Sub

Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

Sub SayHello()
MsgBox "Hello there"
End Sub

You can add as many buttons or other menu items as you like.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,
many many thanks for your help!!! It all worked a
treat. Thank you!!

Gratefully yours,
Balsak