Solved: assign macros to the controls of a toolbar

[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?

[Bob Phillips]

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.

[maryam]

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.

[johnske]

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

[maryam]

'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]

[maryam]

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.

[maryam]

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.

[Charlize]

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

[maryam]

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.

[Charlize]

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

[maryam]

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?

[maryam]

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.

[lucas]

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....

[lucas]

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.....

[johnske]

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

[maryam]

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

[maryam]

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.

[johnske]

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

[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

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

[lucas]

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.