Solved: assign macros to the controls of a toolbar

I made a toolbar using tool/customize. then from commands/Rearrange commands/modify section I assign the macros I defined in a module to the controls. the toolbar works on my pc, but when I try to use the controls in another pc it gives an error that the macro cannot be find. The formula I typed for macro will be changed to sth else and someother path will be added to the formula for example 'F:\...\ .... \name of excel file.xls'! name of the subrutine. the formula should only be 'name of excel file.xls! name of the subrutine.When I remove the extra part for example F:\....\...\ and changed it to name of exelfile.xls! name of subrutine then the controls work.I am confused. How can I get rid of the extra part of the formula(the path) so that I dont need to delete for each control when I run my program on other pcs?

Create the toolbar dynamically

[vba]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub
[/vba]

To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

I have a seprate module in which I wrote the codes to creat a tool bar backup, beside creating a toolbar with tools/costumize. but I dont know why every time the path in which I saved the file will be added to the formula I assigned to the control in costumize and when I want to open it in another computer I have to delete these things manually for each control first so that it can find micros.I cannot attach my module to this thread.

You can copy and post the code from the module or zip and attach... (click the "Go Advanced" button to manage attachments)

'Function that Creates the Toolbar as BackUp. To create just run the macro.
[vba]
Sub CreateToolBar()
Application.CommandBars.Add(Name:="Object Palette1(BackUp)").Visible = False

Set Toolbar = Application.CommandBars("Object Palette1(BackUp)")

With Toolbar
.Controls.Add Type:=msoControlButton, ID:=204, Before:=1
.Controls.Add Type:=msoControlButton, ID:=182, Before:=2
End With

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Cross").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(3).Caption = "Cancel Selection"
Toolbar.Controls(3).OnAction = "Breakchoice"

With Toolbar
.Controls.Add Type:=msoControlButton, ID:=2642, Before:=4
.Controls.Add Type:=msoControlButton, ID:=1142, Before:=5
End With

Toolbar.Controls(4).Caption = "Connector"
Toolbar.Controls(4).OnAction = "AddConnector"

Toolbar.Controls(5).Caption = "Material Stream"
Toolbar.Controls(5).OnAction = "AddStreams"

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Mixer").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(6).Caption = "Mixer"
Toolbar.Controls(6).OnAction = "AddMixer"

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Tee").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(7).Caption = "Tee"
Toolbar.Controls(7).OnAction = "AddTee"

Toolbar.Controls.Add Type:=msoControlButton, ID:=2949, Before:=8
Toolbar.Controls(8).BeginGroup = True
Toolbar.Controls(8).Caption = "Heating/Cooling"
Toolbar.Controls(8).Style = 2
Toolbar.Controls(8).OnAction = "MenuTitle"

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Heater").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(9).Caption = "Heater"
Toolbar.Controls(9).OnAction = "AddHeater"

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Cooler").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(10).Caption = "Cooler"
Toolbar.Controls(10).OnAction = "AddCooler"

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("HeatExchanger").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(11).Caption = "Heat Exchanger"
Toolbar.Controls(11).OnAction = "AddHeatExchanger"
[/vba]

and then there is another module in which there are subroutines for AddConector, Addstream, ... .

My this workbook is:
[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Object Palette1").Delete

Workbooks("Data1.xls").Close

'Original settings for toolbars display are restored
Run RestoreToolBars
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Workbooks("Data1.xls").Save
End Sub
Private Sub Workbook_Open()
Dim PathName As String

'Application.CommandBars("Design Palette").Protection = msoBarNoCustomize

'All irrelevant toolbars are hidden
Run HideAllToolBars

Application.ScreenUpdating = False
PathName = Application.ThisWorkbook.Path & "\Data1.xls"
Workbooks.Open (PathName)
ActiveWindow.Visible = True
Application.ScreenUpdating = True

Sheets("Menu").Select

End Sub
[/vba]

How can we add the macros to the list of the macros. I mean from tools/ macro/macro we can see the name of the macros but I cannot see all the names. I defined some macros in a module but the names didn't come to the list.

I understand about the previous post, when we define the sub as private the name doeant come and when we just write sub or public sub then the name will come in the list as well. Please help me with the macro formula.

Don't you want your toolbar to execute the macros when clicked upon ? The .onaction = "name_of_macro" means that there must be a macro (sub) that has te name name_of_macro()
Private module (before or after option explicit) means the macros in that module aren't visible in the list of macros.

Charlize

Dear charlize,
what do you mean by .onaction=" "
I want the toolbar controls to execute the macro when clicked on. but the macro cannot be executed as a path name is added to the name of the macro I aaigned to the control. I have to delete the pathname to be able to run the macro when clicked on the control but after I saved my file, the next time I open it there is the same problem.

Quote:

---

Originally Posted by xld

This one means that you don't take the time to add a toolbar manually but
use this code to create and when finished, delete the toolbar for the current workbook.
Create the toolbar dynamically

[vba]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Delete toolbar that you have created
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
When workbook opens, create toolbar
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

On Error Resume Next
If for some reason the toolbar is still present, delete it, or you will get an error
Application.CommandBars("myToolbar").Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
The line beneath this sentence means when pushed on button with
caption savenv execute macro savenv
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
Position of the toolbar
.Position = msoBarTop
End With

End Sub
[/vba]

---

Charlize

I sent my macros in #5, what is wrong with that? I want to be able to asign macros to the toolbar which is made through tools/customize beside the toolbar which we made and open it when we open the worksheet. But dont know why the path is added to the macro I assing to the controls of that toolbar?

The same problem is with the controls of the toolbar I made in a module. When I click the path will come first and then the macro dor example addStream and the macro cannot be run.

Because there is a more logical, practical way. That way is to have the workbook open create the toolbar each time the workbook is open and have it specific to that workbook only....if you use customize then you have to attach the toolbar to each workbook....someone correct me if I'm wrong....

As Bob stated in post #2 and Charlize re-iterated in post #10......it is a much better way in vba(this is not vb)to create your toolbars and menu's.....

Quote:

---

Originally Posted by maryam

I made a toolbar using tool/customize. then from commands/Rearrange commands/modify section I assign the macros I defined in a module to the controls. the toolbar works on my pc, but when I try to use the controls in another pc it gives an error that the macro cannot be find. The formula I typed for macro will be changed to sth else and someother path will be added to the formula for example 'F:\...\ .... \name of excel file.xls'! name of the subrutine. the formula should only be 'name of excel file.xls! name of the subrutine.When I remove the extra part for example F:\....\...\ and changed it to name of exelfile.xls! name of subrutine then the controls work.I am confused. How can I get rid of the extra part of the formula(the path) so that I dont need to delete for each control when I run my program on other pcs?

---

maryam, from what I can gather, it appears that you're assigning a macro that resides in one workbook that's open (which we'll call "book A") to a control that resides in a different open workbook (which we'll call "book B").

Now, when book A is closed, the name of the macro that the button is assigned to in book B will change to show the full path to book A.

The solution to this is to make sure that the macro code that's assigned to the control, and the control itself, both reside in the same workbook.

HTH :)

I cannot fix my problem yet. It is something like what is explained here:
http://exceltips.vitalnews.com/Pages/T0732_Macros_in_Template_Files.html.
but when I follow the intructions I cannot fix my problem. I changed all the formula to the name of the excel file and macro name and save my file but again when I open the wrong path will come infront of the formula. Is there any way I can send my file to any one of you? I cannot attach here as it is big.



Thanks

dean Johnske and Xld I can see you on. do you have time to help me? I think I need to send my file to you, but cannot attach it to the post.

maryam,

I don't need to look at the workbook,

1) Make sure the macro is in the same workbook that the button is to go in.

2) Create the toolbar dynamically as XLD suggested - do NOT create it manually

John :)

Dear All,
I have an excel file. I want to have the toolbar in sheet2. I wrote the macros for example "Private Sub AddStreams" in module1 which is inside the same file. then from tools/customize I try to assign this fomula to my control "filename.xls!AddStreams" but the formula changed to another thing and the path is added to it.
Also In module6 which is inside this file,I creat a toolbar as back up,some part of the codes is:

Sub CreateToolBar()
Application.CommandBars.Add(Name:="Object Palette1(BackUp)").Visible = False

Set Toolbar = Application.CommandBars("Object Palette1(BackUp)")

With Toolbar
.Controls.Add Type:=msoControlButton, ID:=204, Before:=1
.Controls.Add Type:=msoControlButton, ID:=182, Before:=2
End With

ThisWorkbook.Sheets("FrmTask1Buffer").Pictures("Cross").Copy
Set MyButton = Toolbar.Controls.Add(Type:=msoControlButton)
MyButton.PasteFace
Toolbar.Controls(3).Caption = "Cancel Selection"
Toolbar.Controls(3).OnAction = "Breakchoice"

With Toolbar
.Controls.Add Type:=msoControlButton, ID:=2642, Before:=4
.Controls.Add Type:=msoControlButton, ID:=1142, Before:=5
End With

Toolbar.Controls(4).Caption = "Connector"
Toolbar.Controls(4).OnAction = "AddConnector"

Toolbar.Controls(5).Caption = "Material Stream"
Toolbar.Controls(5).OnAction = "AddStreams"

But non of the control of these two toolbars work, because of the path added!!! Is it clesr now or why dont you want to look at my file!!!



Thank you,
Mariam

Quote:

---

Originally Posted by maryam

Dear All,
I have an excel file. I want to have the toolbar in sheet2. I wrote the macros for example "Private Sub AddStreams" in module1 which is inside the same file. then from tools/customize I try to assign this fomula to my control "filename.xls!AddStreams" but the formula changed to another thing and the path is added to it.
Also In module6 which is inside this file,I creat a toolbar as back up,some part of the codes is:

Sub CreateToolBar()
Application.CommandBars.Add(Name:="Object Palette1(BackUp)").Visible = False


But non of the control of these two toolbars work, because of the path added!!! Is it clesr now or why dont you want to look at my file!!!



Thank you,
Mariam

---

Hi Mariam,
I have watched folks try to give you subtle suggestions and you are for whatever reason ignoring everything you are being told....maybe we have a language problem...please let us know if that is part of the problem.

I am going to be a little more blunt this time....coming right to the point...you should abandon the method you are trying to use....repeat abandon the method of creating toolbars that you are currently attempting.

Start over with the toolbar by reading post #2 and go from there. Everything should be in one workbook....repeat one workbook.

I have no clue why you are creating a "Backup toolbar" this statement is a total mystery to me????

One more time in case it's not sinking in.....abandon, quit, cease and desist from your current method of creating the toolbar and at least investigate what folks here are trying to get you to see.

What you want done can't be done no matter how many times you come back with more problems with it.....

Hope this clears things up for you and will be awaiting your next question about how to create a toolbar dynamically......

This is my suggestion to you. Hope you understand.

maryam,

First, I didn't say I didn't want to look at your file, I said there is no need to.

There are only two things that can possibly go wrong to create your problem.

1) The macro resides in a different workbook.
(You say now that it's in the same workbook, so that only leaves the other problem).

2) The buttons are not being created dynamically.
(Looking at what you've just said I see this is what the problem is).

You must create the controls dynamically, not programmatically, there is a difference - I'll try to explain.

First, you need to note that Toolbar buttons are part of the Excel User Interface (UI), so they are essentially different to other types of buttons such as 'Forms' buttons or 'Controls' buttons.

Now, "dynamically" means that whenever someone opens the file on whatever machine they're using, temporary custom buttons are created with a Workbook_Open event and macros in that workbook are assigned to them with the "OnAction" property, the full path to the macros is assigned by Excel (but only a 'short' version of that path will be seen by you) - those buttons then become part of that users UI.

If you don't do it that way, and choose to create permanent controls on your machine instead then, when the file is opened on the other machine the macros can't be found by Excel because the full path to the macros being shown on their machine is the path to the macros on your own machine.

Delete all toolbars you have created and go back to the example shown post #2 and follow XLDs instructions - just change the names of the macros shown in "OnAction" to the names of your own macros.

To add to this discussion: See www.jkp-ads.com/articles/distributemacro04.htm for a more elaborate explanation about toolbars (in fact I think the other chapters of that article are useful for maryam too...

Is it deffirent from having a creat toolbar sub. I have .Onaction there too.

I have difficulties in understanding what to do. Can I send my file to any one? email adress?

There is no point in sending us a file. We have told you over and over how to get out of the predicament. CREATE THE TOOLBAR DYNAMICALLY. If and when you have tried that post back again with any problems.

Be sure that ALL your macro's that you want to use with the dynamic created toolbar are located in a standard module and not in the code for a sheet.

Charlize

Dear Charlize,
Yes.the subroutines are in a module not in the code for a sheet.



Thank you,
Maryam

Quote:

---

Originally Posted by maryam

I have difficulties in understanding what to do. Can I send my file to any one? email adress?

---

At the risk of breaching forum rules....

You can send the file to me, but I will charge you my usual hourly rate to make any changes to it.

Dear Jan,
No I dont want to break the laws of this good forum. Just because i couldn't attach my file I had to send it through email.



Thank you,
Maryam

I insert the code into the workbook open event (what bob told me to do) and the controls are working now. I want to thank all of you. Please accept my apologizes.

Maryam,

I did not mean to say you'd break the rules, I meant to say *I*'d break a rule by offering commercial service.