Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
Thanks Dave,
That works Great. Your help is greatly appreciated. -Minitman On Thu, 10 May 2007 07:37:20 -0500, Dave Peterson 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 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 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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 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 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 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add A Button To The Menu Bar When Opening Workbook
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 "JW" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
Create floating button based on button click in menu | Excel Programming | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming |