Viv

Hi everyone, and thanks for reading this!

I have recently started to learn VB and VBA and am nearing completion of my first mini prog in VBA with excel. I have come to a halt at one little problem. I would like the prog to automatically add a new tollbar item when the project is opened but do not now how to do this. The program is a simple payroll system and just needs a launch point from the main excel toolbar so that the user can access the user forms.

I hope that makes sense

Thanks again

Viv

Timbo

Look up the 'Event' topic for the Workbook object in the VB help, and run a board search on 'commandbar'...

Viv

ok, bit of a dead end that one

found an article with this
****************************************************

The code below will help you to create a toolbar and button. You'll need to modify it to suit your needs. Toolbar buttons (and menu buttons) have to have a macro to call, so you'll need to create a macro that will be run when the user clicks on the button that you created. Ideally, you'd want to call this procedure from the Workbook_Open procedure.



VB:--------------------------------------------------------------------------------Private Sub AddToolBar()

Dim cmdbar As CommandBar
Dim CmdBtn1 As CommandBarButton

Set cmdbar = CommandBars.Add(Name:="MyToolbar", Position:=msoBarTop, Temporary:=True)
cmdbar.Visible = True

With cmdbar
.Left = CommandBars("Standard").Width
.RowIndex = CommandBars("Standard").RowIndex

Set CmdBtn1 = .Controls.Add(msoControlButton, , , , True)
With CmdBtn1
.Style = msoButtonCaption
.Caption = "MyToolbar" 'The text to appear on the toolbar (i.e. Back)
.TooltipText = "This is my toolbar" 'The tooltip that will appear when the cursor is over that button
.OnAction = "Macro1" 'Whatever macro you want to run when the button is pressed
End With

End With

Set cmdbar = Nothing
Set CmdBtn1 = Nothing

End Sub--------------------------------------------------------------------------------


__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handy
****************************************************

But as yet have been unable to get this to work, will this do what i want it to?? (it's looks like it oughta) i'm sorry, expect this has been asked manytimes before!Where would this code need to go?! Programme is made up of a set of 5 user forms to aid set up and use of payroll spreadsheet, I just need a button for the user to click to access th emain menu of these forms.

I'm still very new to this. thanks for your time

V

Squirm

I'm helping Viv with this on IRC. We've got the AddToolBar() sub being called from the Workbook_Open, but an error 91 (Object variable or With block variable not set) occurs on this line:

Hoving the mouse over CommandBars reveals that the object is unset at the point (it is 'Nothing'). Having not worked with Excel much, I don't know why this is or what the solution is. Since the original poster suggested that this be called from Workbook_Open I assumed it would work from there. Are we missing something?

(Original thread here)

TIA

max1mum0v3rdr1v

If you look at the original code above that Viv posted, you will see that the With statement for cmdbar does not contain an End With. Here is the original statement:


It should be:


Hope that helps. Good luck!

--Sean

Squirm

Well, it does contain an End With already, further down (they are nested). The code itself is not a problem, I'm pretty sure it WOULD work, if error 91 wasn't coming up.

Timbo

The above example, mixes commands used to create a new commandbar with those used to add controls to a commandbar.

Have a read of this lot - http://www.xtremevbtalk.com/sear...der=descending

Viv

ok this is what i actually needed-----


Private Sub Workbook_Open()

Dim myButton As CommandBarButton
Set myButton = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Caption = "&Payroll" 'name of mini prog
myButton.Style = msoButtonCaption
myButton.BeginGroup = True
myButton.OnAction = "Showit" 'name of macro


End Sub


Thanks for the help anyway

Artifi

Hello

I am trying to do this as well, with the first example I get the same error: 91. Then I used this code:

Private Sub Workbook_Open()
Dim myButton As CommandBarButton
Set myButton = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Caption = "Test" 'name of mini prog
myButton.Style = msoButtonCaption
myButton.BeginGroup = True
myButton.OnAction = "Test" 'name of macro

End Sub

You can see the result in the picture. HELP!!!

Each time I click on Test, it creates another button in the bar. Each time I open Excel the buttons are still there, even though the makro isn't on. How do I remove them?
They are also in the wrong place, they should be in the bar below and not in the bar with the pull down menus.

Couldn't you just customize the bar "Standard" or "Visual Basic" and add 5 buttons, draw your own symbols and 1 click does the macro and that's it?

Another nice method would be to create a pulldown menu, with 5 commands, but this Pulldown Menu with its Button should only appear when the appropriate sheet is open.

Thank you very much.

Mill

Have a look at the two most recent entries in my FAQ

Artifi

Thank you, I'm gonna look at that tomorrow (at work). But how do I remove the 6 "Test" buttons? Just by deleting the file with the according macro in it?

They have to go, each time I open Excel another button is created. One day, there will be thousands of buttons, there is not enough space for all of them.

Mill

Did you put the code into your Personal Workbook_Open event? If so, you need to remove that macro.

I'm a bit leary about deleting menu items through code. Therefore, I'd suggest just dragging them off by holding the Alt key, clicking on it, and dragging it onto the spreadsheet area.

Artifi

Yes I did.
I can remove the whole Excel file, it is emtpy, just that only macro is in it. When I try new things, I try them in a new clean sheet first, before things screw up.

Thank you, tomorrow I will see and tell you. Those files are only at work, not at home.

What does "leary" mean? Is that American english or used everywhere in english speaking countries?

Mill

Leary, leery, wary, cautious - all mean about the same thing (leary is in the dictionary, but it just says that it's a variant of "leery"). I think it's probably Americanese.

Basically, I've had experiences where more menu items were deleted than what I wanted to delete, which can be pretty scary for someone on a PC at work. Since then, I've found out that you can reset menu bars by right-clicking on it, clicking 'Customize', and then selecting the toolbar from the Toolbars tab and clicking the Reset button.

Artifi

Hello

I removed the Test buttons and deleted the files, so that was okay. I tried your code, both the commandbar button procedure as well as the pulldown menu procedure. Both make slight problems.

Sub AddToolBar()

Dim cmdbar As CommandBar
Dim CmdBtn1 As CommandBarButton
Dim strTBName As String

strTBName = "MyToolbar"

Instead of Sub AddToolBar this should be in the Open Workbook Procedure like this:

Private Sub Workbook_Open()
Dim cmdbar As CommandBar
Dim CmdBtn1 As CommandBarButton
Dim strTBName As String

strTBName = "MyToolbar"
...
...
End Sub

But then this happens (see jpg), error 424. This line is highlighted:
For Each bar In CommandBars

Where do I put the function and what is the purpose of a function?

The toolbar was put into the upper bar, I prefer it in the lower bar. I changed "Standard" to "Format", that should do it, shouldn't it?


The pulldown program had the following error (see jpg), and this line was highlighted:

Set mnuNew = .Controls.Add(msoControlPopup, , , intPosition, True).

Error roughly translates into: "Index outside the valid area".

Thanks!

Mill

I'm not sure about your errors because I can't understand German. You should put the line AddToolBar into your Workbook_Open event. The AddToolBar sub and the CheckForToolBar function should go into the Workbook module. Depending on how your toolbars are oriented, changing "Standard" to "Format" should work, I think.

Artifi

It half worked. The bar appears now, but at the top and not beside the "Format" bar.

What do I do, if I want the bar to appear directly on the screen and not together with the other bars?

And I want at least 6 buttons calling 6 different subs, which bits of code do I repeat?

Sorry for the German.

Mill

Okay, try "Formatting" as the name of the toolbar. To make it a floating toolbar, change


to


Look in your VBA help file for the Add method of the CommandBars control for more information on that.

Note also that, as of Office 97 (or maybe 2000), all toolbars can be floating or docked, so you can drag them around by their handles any way that you want to.

Artifi

I now changed the prog a bit and renamed the Toolbar. I thought the exitsing toolbar will be changed, but it created another toolbar. Now I want to remove the old toolbar. Sorry.


... oh, it is just done by restarting Excel. That's cool.

Artifi

You can open up a userform when opening the excel file/sheet with the Workbook Open Event, you can also type in coordinates of where you want the Userform to go.

Aktionen.Show
Aktionen.StartupPosition = 0 'Manual
Aktionen.Left = 200
Aktionen.Top = 200

Can you do the same thing with the commandbar? It should appear floating, but at the bottom and not directly on the screen. Thanks