Hi,
I hope you could help with a macro issue.
I wrote several macros in VBA that were probably assigned to a specific
file. I copied them to another file and the keyboard shortcuts do not
function properly. It seems that the name of the macro in the new file has an
addition of the file name to the macro name such as
"SAR_Data.xls!CheckBit.CheckBit"
instead of just "CheckBit".
Other macros that were copied and didn't have the file name extension were
functional with the shortcuts.
I was wondering if there is any way to change macro's property to become
file specific and when I create a copy of the macro, the keyboard shortcuts
will be operational?
I even tried to assigned a macro to custumized tool bar and assign a key to
it. But it seems that the tool bar button is attached to a specific macro and
not generalized one, so it couldn't help me in other files. I would rather
not have the macros sit in presonal.xls
If you got any solution to that problem I'll appriciate it.
thanks,

You could create the toolbar in the file, like this

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


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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"IJ" wrote in message
...
Hi,
I hope you could help with a macro issue.
I wrote several macros in VBA that were probably assigned to a specific
file. I copied them to another file and the keyboard shortcuts do not
function properly. It seems that the name of the macro in the new file has
an
addition of the file name to the macro name such as
"SAR_Data.xls!CheckBit.CheckBit"
instead of just "CheckBit".
Other macros that were copied and didn't have the file name extension were
functional with the shortcuts.
I was wondering if there is any way to change macro's property to become
file specific and when I create a copy of the macro, the keyboard
shortcuts
will be operational?
I even tried to assigned a macro to custumized tool bar and assign a key
to
it. But it seems that the tool bar button is attached to a specific macro
and
not generalized one, so it couldn't help me in other files. I would rather
not have the macros sit in presonal.xls
If you got any solution to that problem I'll appriciate it.
thanks,

It helped!!! Thanks !!!

IJ

"Bob Phillips" wrote:

You could create the toolbar in the file, like this

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


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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"IJ" wrote in message
...
Hi,
I hope you could help with a macro issue.
I wrote several macros in VBA that were probably assigned to a specific
file. I copied them to another file and the keyboard shortcuts do not
function properly. It seems that the name of the macro in the new file has
an
addition of the file name to the macro name such as
"SAR_Data.xls!CheckBit.CheckBit"
instead of just "CheckBit".
Other macros that were copied and didn't have the file name extension were
functional with the shortcuts.
I was wondering if there is any way to change macro's property to become
file specific and when I create a copy of the macro, the keyboard
shortcuts
will be operational?
I even tried to assigned a macro to custumized tool bar and assign a key
to
it. But it seems that the tool bar button is attached to a specific macro
and
not generalized one, so it couldn't help me in other files. I would rather
not have the macros sit in presonal.xls
If you got any solution to that problem I'll appriciate it.
thanks,

BTW, there is a table driven method explained here

http://www.contextures.com/xlToolbar02.html


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"IJ" wrote in message
...
It helped!!! Thanks !!!

IJ

"Bob Phillips" wrote:

You could create the toolbar in the file, like this

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


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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"IJ" wrote in message
...
Hi,
I hope you could help with a macro issue.
I wrote several macros in VBA that were probably assigned to a specific
file. I copied them to another file and the keyboard shortcuts do not
function properly. It seems that the name of the macro in the new file
has
an
addition of the file name to the macro name such as
"SAR_Data.xls!CheckBit.CheckBit"
instead of just "CheckBit".
Other macros that were copied and didn't have the file name extension
were
functional with the shortcuts.
I was wondering if there is any way to change macro's property to
become
file specific and when I create a copy of the macro, the keyboard
shortcuts
will be operational?
I even tried to assigned a macro to custumized tool bar and assign a
key
to
it. But it seems that the tool bar button is attached to a specific
macro
and
not generalized one, so it couldn't help me in other files. I would
rather
not have the macros sit in presonal.xls
If you got any solution to that problem I'll appriciate it.
thanks,