Add A Button To The Menu Bar When Opening Workbook

[Minitman]

Greetings,

I need to place a button, with code, onto the menu bar when I open a
particular workbook from any machine in my network.

I can do it manually, but I can't figure out how to do it with code.

Any suggestions, ideas or possible links that could shed some light as
to how to proceed?

Any help is appreciated.

-Minitman

 

[JW]

Sure.
Sub addbar()
Dim MenuItem As CommandBarButton
Set MenuItem = _
Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlButton)
MenuItem.Style = msoButtonCaption
MenuItem.Caption = "Caption Here"
MenuItem.OnAction = "macronamehere"
Set MenuItem = Nothing
End Sub

HTH
-Jeff-

 

[Dave Peterson]

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Minitman]

Hey Jeff,

Thanks for the reply.

I received a

Run-time error '5':
Invalid procedure or argument.

This is the line debug highlighted:

Set MenuItem = _
Application.CommandBars _
("Worksheet MenuBar"). _
Controls.Add(Type:= _
msoControlButton)

I do not see any difference between your code (see below) and what I
pasted into Module1.

Any ideas as to what happened?

Hoping to hear from you soon.

-Minitman



Sure.
Sub addbar()
Dim MenuItem As CommandBarButton
Set MenuItem = _
Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlButton)
MenuItem.Style = msoButtonCaption
MenuItem.Caption = "Caption Here"
MenuItem.OnAction = "macronamehere"
Set MenuItem = Nothing
End Sub

HTH
-Jeff-

 

[Minitman]

Hey Dave,

Thanks for the reply. I did learn some things from this link, it's
just not what I am looking for.

I loaded this code and when I saw what it produced I realized that a
menu was is not what I am looking for. I also would like to avoid an
add-in.

I was hoping for just a button with only a tool tip instead of a
caption. The smiley face circles look ok when placed on the menu bar
manually. I just need them to be temporary (not on all of my
worksheets, only one).

Is there a way to do this with code?

Any help is appreciated.

-Minitman

 

[Dave Peterson]

First, you got hit by a line wrap problem with Jeff's code.

But this may work for you. (Starting with Jeff's code.)

Option Explicit
Sub addbar()
Dim MenuItem As CommandBarButton
Set MenuItem = Application.CommandBars("Worksheet Menu Bar") _
.Controls.Add(Type:=msoControlButton, temporary:=True)
With MenuItem
'.Style = msoButtonIconAndCaption
.Style = msoButtonIcon
.FaceId = 59
'.Caption = "Caption Here"
.OnAction = "'" & thisworkbook.name & "'!" & "macronamehere"
.TooltipText = "Hi there"
End With
End Sub

If you're looking for some of the built-in icons:

Doug Clancy's:
http://www.dicks-blog.com/archives/2004/11/23/faceid-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech

 

[Minitman]

Thanks Dave,

That works Great.

Your help is greatly appreciated.

-Minitman

 

[Dave Peterson]

Glad it worked for you.

As a personal note, I find that showing the caption makes it easier for me as a
user. And I bet it would make it stand out more for your users, too.

There are so many icons/options on those toolbars, the user may see the smiley
face, but never think to mouse over it.

With a nice caption, it may make it a little more noticeable.

 

[Minitman]

Thanks for the suggestion, it's a good one.

The final workbook is using three different icons and this workbook is
designed for in house use only. But I will take note of better
labeling for future projects.

-Minitman

 

[Dave Peterson]

That's also one of the reasons I like to make a floating toolbar, too. I figure
that icons that are added to existing toolbars can be missed.

But if I plop a new toolbar right in the way, the user (sometimes me, too) will
see it--heck, I may have to move it to do the work.

 

[Guest]

I found your coding very helpful, but I haven't been able to delete the menu
item automatically when I close my file. Can you provide me with the coding
for that?

Thanks,

Chris