Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

Thanks Kevin,

The macro follows the worksheet, but the button on the tool bar does
not. Is there a way to code the addition/deletion of the button to be
associated with the specific workbook? I would like to be able to
email the spreadsheet and when the user opens it, the custom button for
the macro be available.

Thanks,

Mark

Kevin B wrote:
If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

Mark,

Follow my directions, below.

HTH,
Bernie
MS Excel MVP


The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow
these instructions and example code.


In the workbook's Thisworkbook object code module, place the following code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub


'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' On Error GoTo NotThere
' Application.CommandBars("My Bar").Visible = True
' Exit Sub
'NotThe
' CreateCommandbar
'End Sub


'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
' On Error Resume Next
' Application.CommandBars("My Bar").Visible = False
'End Sub


In a regular code module, place the following:


Dim myBar As CommandBar
Dim myButton As CommandBarButton


Sub CreateCommandbar()


On Error Resume Next
DeleteCommandBar


Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With


End Sub


Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub


Sub SayHello()
MsgBox "Hello there"
End Sub


You can add as many buttons or other menu items as you like.



"Racesmith" wrote in message
oups.com...
Thanks Kevin,

The macro follows the worksheet, but the button on the tool bar does
not. Is there a way to code the addition/deletion of the button to be
associated with the specific workbook? I would like to be able to
email the spreadsheet and when the user opens it, the custom button for
the macro be available.

Thanks,

Mark

Kevin B wrote:
If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

I have copied your code to this workbook and module for the spreasheet,
and it works on my PC, but when I email the spreasheet, the user just
gets a blank enrollmenttoolbar. Here is a copy of the code.

This is contained in the module:
Sub CreateCommandbar()
Dim EnrollmentToolbar As CommandBar
Dim EnrollmentButton As CommandBarButton

On Error Resume Next
DeleteCommandBar

Set EnrollmentButton = Application.CommandBars.Add("EnrollmentToolBar")
With EnrollmentToolbar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set EnrollmentButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With EnrollmentButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.OnAction = "SayHello"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars(EnrollmentToolbar).Delete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub

This is contained in This Workbook:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("EnrollmentToolBar").Visib le = True
Exit Sub
NotThe
CreateCommandbar
End Sub
Sub mac1()
'
' mac1 Macro
' Macro recorded 8/23/2006 by Technology Department
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("EnrDnld").Select
Sheets("EnrDnld").Name = "EnrDnld"
Range("A1").Select
Application.ScreenUpdating = False
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{ENTER}") 'No need to click OK now
Application.Run "fShowTToDialog"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("EnrollmentToolBar").Visib le = True
End Sub

You have been so helpful, and I hope that you can help me even more.
I do apologize for being such a noob at this VBA stuff. :)

Mark

Bernie Deitrick wrote:
Mark,

Follow my directions, below.

HTH,
Bernie
MS Excel MVP


The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow
these instructions and example code.


In the workbook's Thisworkbook object code module, place the following code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub


'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' On Error GoTo NotThere
' Application.CommandBars("My Bar").Visible = True
' Exit Sub
'NotThe
' CreateCommandbar
'End Sub


'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
' On Error Resume Next
' Application.CommandBars("My Bar").Visible = False
'End Sub


In a regular code module, place the following:


Dim myBar As CommandBar
Dim myButton As CommandBarButton


Sub CreateCommandbar()


On Error Resume Next
DeleteCommandBar


Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With


End Sub


Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub


Sub SayHello()
MsgBox "Hello there"
End Sub


You can add as many buttons or other menu items as you like.



"Racesmith" wrote in message
oups.com...
Thanks Kevin,

The macro follows the worksheet, but the button on the tool bar does
not. Is there a way to code the addition/deletion of the button to be
associated with the specific workbook? I would like to be able to
email the spreadsheet and when the user opens it, the custom button for
the macro be available.

Thanks,

Mark

Kevin B wrote:
If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

Mark,

Your error is in this line:

Set EnrollmentButton = Application.CommandBars.Add("EnrollmentToolBar")

It should be:

Set EnrollmentToolbar = Application.CommandBars.Add("EnrollmentToolBar")

And you left out some quote marks, you forgot to enable the commandbutton, and your name for the
commandbar and the variable are the same (which is a bad programming practice).

Below are my fixes.

HTH,
Bernie
MS Excel MVP

'This is contained in the module:

Dim cbEnrollment As CommandBar
Dim EnrollmentButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set cbEnrollment = Application.CommandBars.Add("EnrollmentCommandBar" )
With cbEnrollment
.Position = msoBarTop
.Visible = True
.Enabled = True
Set EnrollmentButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With EnrollmentButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "mac1"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("EnrollmentCommandBar").De lete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub

Sub mac1()
'
' mac1 Macro
' Macro recorded 8/23/2006 by Technology Department
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("EnrDnld").Select
Sheets("EnrDnld").Name = "EnrDnld"
Range("A1").Select
Application.ScreenUpdating = False
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{ENTER}") 'No need to click OK now
Application.Run "fShowTToDialog"
End Sub

'This is contained in This Workbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandBar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("EnrollmentCommandBar").Vi sible = True
Exit Sub
NotThe
CreateCommandbar
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("EnrollmentCommandBar").Vi sible = False
End Sub



"Racesmith" wrote in message
oups.com...
I have copied your code to this workbook and module for the spreasheet,
and it works on my PC, but when I email the spreasheet, the user just
gets a blank enrollmenttoolbar. Here is a copy of the code.

This is contained in the module:
Sub CreateCommandbar()
Dim EnrollmentToolbar As CommandBar
Dim EnrollmentButton As CommandBarButton

On Error Resume Next
DeleteCommandBar

Set EnrollmentButton = Application.CommandBars.Add("EnrollmentToolBar")
With EnrollmentToolbar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set EnrollmentButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With EnrollmentButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.OnAction = "SayHello"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars(EnrollmentToolbar).Delete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub

This is contained in This Workbook:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("EnrollmentToolBar").Visib le = True
Exit Sub
NotThe
CreateCommandbar
End Sub
Sub mac1()
'
' mac1 Macro
' Macro recorded 8/23/2006 by Technology Department
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("EnrDnld").Select
Sheets("EnrDnld").Name = "EnrDnld"
Range("A1").Select
Application.ScreenUpdating = False
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{ENTER}") 'No need to click OK now
Application.Run "fShowTToDialog"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("EnrollmentToolBar").Visib le = True
End Sub

You have been so helpful, and I hope that you can help me even more.
I do apologize for being such a noob at this VBA stuff. :)

Mark

Bernie Deitrick wrote:
Mark,

Follow my directions, below.

HTH,
Bernie
MS Excel MVP


The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow
these instructions and example code.


In the workbook's Thisworkbook object code module, place the following code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub


'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' On Error GoTo NotThere
' Application.CommandBars("My Bar").Visible = True
' Exit Sub
'NotThe
' CreateCommandbar
'End Sub


'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
' On Error Resume Next
' Application.CommandBars("My Bar").Visible = False
'End Sub


In a regular code module, place the following:


Dim myBar As CommandBar
Dim myButton As CommandBarButton


Sub CreateCommandbar()


On Error Resume Next
DeleteCommandBar


Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With


End Sub


Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub


Sub SayHello()
MsgBox "Hello there"
End Sub


You can add as many buttons or other menu items as you like.



"Racesmith" wrote in message
oups.com...
Thanks Kevin,

The macro follows the worksheet, but the button on the tool bar does
not. Is there a way to code the addition/deletion of the button to be
associated with the specific workbook? I would like to be able to
email the spreadsheet and when the user opens it, the custom button for
the macro be available.

Thanks,

Mark

Kevin B wrote:
If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?

Thanks Bernie for making the corrections to allow it to work. I will
note the bad programming practice you mentioned. I am very new to
VBA, but will improve over time. You are without a doubt a Most
Valuable Professional.

Mark


Bernie Deitrick wrote:
Mark,

Your error is in this line:

Set EnrollmentButton = Application.CommandBars.Add("EnrollmentToolBar")

It should be:

Set EnrollmentToolbar = Application.CommandBars.Add("EnrollmentToolBar")

And you left out some quote marks, you forgot to enable the commandbutton, and your name for the
commandbar and the variable are the same (which is a bad programming practice).

Below are my fixes.

HTH,
Bernie
MS Excel MVP

'This is contained in the module:

Dim cbEnrollment As CommandBar
Dim EnrollmentButton As CommandBarButton

Sub CreateCommandbar()

On Error Resume Next
DeleteCommandBar

Set cbEnrollment = Application.CommandBars.Add("EnrollmentCommandBar" )
With cbEnrollment
.Position = msoBarTop
.Visible = True
.Enabled = True
Set EnrollmentButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With EnrollmentButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "mac1"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("EnrollmentCommandBar").De lete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub

Sub mac1()
'
' mac1 Macro
' Macro recorded 8/23/2006 by Technology Department
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("EnrDnld").Select
Sheets("EnrDnld").Name = "EnrDnld"
Range("A1").Select
Application.ScreenUpdating = False
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{ENTER}") 'No need to click OK now
Application.Run "fShowTToDialog"
End Sub

'This is contained in This Workbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandBar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("EnrollmentCommandBar").Vi sible = True
Exit Sub
NotThe
CreateCommandbar
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("EnrollmentCommandBar").Vi sible = False
End Sub



"Racesmith" wrote in message
oups.com...
I have copied your code to this workbook and module for the spreasheet,
and it works on my PC, but when I email the spreasheet, the user just
gets a blank enrollmenttoolbar. Here is a copy of the code.

This is contained in the module:
Sub CreateCommandbar()
Dim EnrollmentToolbar As CommandBar
Dim EnrollmentButton As CommandBarButton

On Error Resume Next
DeleteCommandBar

Set EnrollmentButton = Application.CommandBars.Add("EnrollmentToolBar")
With EnrollmentToolbar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set EnrollmentButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With EnrollmentButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.OnAction = "SayHello"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars(EnrollmentToolbar).Delete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub

This is contained in This Workbook:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("EnrollmentToolBar").Visib le = True
Exit Sub
NotThe
CreateCommandbar
End Sub
Sub mac1()
'
' mac1 Macro
' Macro recorded 8/23/2006 by Technology Department
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("EnrDnld").Select
Sheets("EnrDnld").Name = "EnrDnld"
Range("A1").Select
Application.ScreenUpdating = False
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{ENTER}") 'No need to click OK now
Application.Run "fShowTToDialog"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("EnrollmentToolBar").Visib le = True
End Sub

You have been so helpful, and I hope that you can help me even more.
I do apologize for being such a noob at this VBA stuff. :)

Mark

Bernie Deitrick wrote:
Mark,

Follow my directions, below.

HTH,
Bernie
MS Excel MVP


The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow
these instructions and example code.


In the workbook's Thisworkbook object code module, place the following code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub


Private Sub Workbook_Open()
CreateCommandbar
End Sub


'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' On Error GoTo NotThere
' Application.CommandBars("My Bar").Visible = True
' Exit Sub
'NotThe
' CreateCommandbar
'End Sub


'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
' On Error Resume Next
' Application.CommandBars("My Bar").Visible = False
'End Sub


In a regular code module, place the following:


Dim myBar As CommandBar
Dim myButton As CommandBarButton


Sub CreateCommandbar()


On Error Resume Next
DeleteCommandBar


Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With


End Sub


Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub


Sub SayHello()
MsgBox "Hello there"
End Sub


You can add as many buttons or other menu items as you like.



"Racesmith" wrote in message
oups.com...
Thanks Kevin,

The macro follows the worksheet, but the button on the tool bar does
not. Is there a way to code the addition/deletion of the button to be
associated with the specific workbook? I would like to be able to
email the spreadsheet and when the user opens it, the custom button for
the macro be available.

Thanks,

Mark

Kevin B wrote:
If the macro is alread written, you can cut and paste it to the worksheet
module.

Press Alt + F11 to open the VB editor. If the project window isn't
displayed press Ctrl + R to display it. Locate the module your macro is in,
select and cut to the clipboard.

In the project explorer click MICROSOFT EXCEL OBJECTS to display all the
worksheets and double click on the worksheet the macro is to be asscociated
with. Paste the macro from the clipboard and save the file.
--
Kevin Backmann


"Racesmith" wrote:

Is there a way to save a macro to a static place and call
the macro from the custom button, so the macro is not re-
associated with the new .xls?