Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / January 2005

Tip: Looking for answers? Try searching our database.

Custom tool bar does not transfer correctly

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
WayneF - 28 Jan 2005 02:57 GMT
   Sorry about all these post :( but i think i am nearly at the end ...

   I have now created a tool bar with some buttons and dropdown menu. I did
this in 2003. I have attached the toolbar to the workbook but when I tried
to give this file to another use ... There is nothing on the toolbar; The
toolbar comes up ok with the write name when the book is first opened BUT
the are no buttons, only a very small box with the "close" cross. Does
anyone know why this does not work ? I have tried it from 2003 to 2002 and
from 2003 to 2003 and i get the same outcome ..... VERY small toolbar with
no buttons.

   Thanks for you help in advance

   Wayne
Bernie Deitrick - 28 Jan 2005 21:17 GMT
Wayne,

Attaching toolbars is problematic.

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. Attaching a
commandbar to a workbook is a bad choice for a few reasons: the commandbar
gets permanently attached to the user's library file, and will open anytime
the user starts Excel, and then any click on the commandbutton will try to
open your file. To get around that, 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

>     Sorry about all these post :( but i think i am nearly at the end ...
>
[quoted text clipped - 10 lines]
>
>     Wayne
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.