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

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 wrote:
> 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

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 wrote:
>
> 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

--

Dave Peterson

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 wrote:
> 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

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





On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
<(E-Mail Removed)> wrote:

>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 wrote:
>>
>> 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

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/2...-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 wrote:
>
> 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
>
> On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
> <(E-Mail Removed)> wrote:
>
> >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 wrote:
> >>
> >> 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

--

Dave Peterson

Thanks Dave,

That works Great.

Your help is greatly appreciated.

-Minitman



On Thu, 10 May 2007 07:37:20 -0500, Dave Peterson
<(E-Mail Removed)> wrote:

>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/2...-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 wrote:
>>
>> 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
>>
>> On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
>> <(E-Mail Removed)> wrote:
>>
>> >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 wrote:
>> >>
>> >> 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

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 wrote:
>
> Thanks Dave,
>
> That works Great.
>
> Your help is greatly appreciated.
>
> -Minitman
>
> On Thu, 10 May 2007 07:37:20 -0500, Dave Peterson
> <(E-Mail Removed)> wrote:
>
> >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/2...-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 wrote:
> >>
> >> 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
> >>
> >> On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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 wrote:
> >> >>
> >> >> 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

--

Dave Peterson

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

On Fri, 11 May 2007 07:04:21 -0500, Dave Peterson
<(E-Mail Removed)> wrote:

>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 wrote:
>>
>> Thanks Dave,
>>
>> That works Great.
>>
>> Your help is greatly appreciated.
>>
>> -Minitman
>>
>> On Thu, 10 May 2007 07:37:20 -0500, Dave Peterson
>> <(E-Mail Removed)> wrote:
>>
>> >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/2...-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 wrote:
>> >>
>> >> 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
>> >>
>> >> On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >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 wrote:
>> >> >>
>> >> >> 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

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.

Minitman wrote:
>
> 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
>
> On Fri, 11 May 2007 07:04:21 -0500, Dave Peterson
> <(E-Mail Removed)> wrote:
>
> >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 wrote:
> >>
> >> Thanks Dave,
> >>
> >> That works Great.
> >>
> >> Your help is greatly appreciated.
> >>
> >> -Minitman
> >>
> >> On Thu, 10 May 2007 07:37:20 -0500, Dave Peterson
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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/2...-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 wrote:
> >> >>
> >> >> 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
> >> >>
> >> >> On Wed, 09 May 2007 15:57:34 -0500, Dave Peterson
> >> >> <(E-Mail Removed)> wrote:
> >> >>
> >> >> >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 wrote:
> >> >> >>
> >> >> >> 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

--

Dave Peterson