Assign kbd shortcut to a macro using vba
 

I have copied a macro from one (master) spreadsheet to another using the code
below. But when I open the spreadsheet which the code has been copied into up
the kbd shortcut which I had given it is lost. The code works if I go alt+F8
| run; and I can re-assign the kbd shortcut through options but I want it to
be there automatically. Is there any way to do this when I copy the code
across?
Thanks, Dean.

'copy macro to validated wookbook
Dim FName As String
With Workbooks("TEAMS_Master.xls")
FName = .Path & "\code.txt"
.VBProject.VBComponents("Module9").Export FName
End With
Workbooks("CBev.xls").VBProject.VBComponents.Impor t FName

'code I am copying to new workbook
Sub Macro_t()
'
' Macro_t Macro
' Macro recorded 05/04/2007 by Dean
'
' Keyboard Shortcut: Ctrl+t
'
Range("Y2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=25, Criteria1:="0", Operator:=xlAnd
Range("A1").Select
End Sub

Assign kbd shortcut to a macro using vba
 

Dean,

try this:

Application.MacroOptions Macro:="Macro_t", ShortcutKey:="t"


--
Hope that helps.

Vergel Adriano


"Dean" wrote:

I have copied a macro from one (master) spreadsheet to another using the code
below. But when I open the spreadsheet which the code has been copied into up
the kbd shortcut which I had given it is lost. The code works if I go alt+F8
| run; and I can re-assign the kbd shortcut through options but I want it to
be there automatically. Is there any way to do this when I copy the code
across?
Thanks, Dean.

'copy macro to validated wookbook
Dim FName As String
With Workbooks("TEAMS_Master.xls")
FName = .Path & "\code.txt"
.VBProject.VBComponents("Module9").Export FName
End With
Workbooks("CBev.xls").VBProject.VBComponents.Impor t FName

'code I am copying to new workbook
Sub Macro_t()
'
' Macro_t Macro
' Macro recorded 05/04/2007 by Dean
'
' Keyboard Shortcut: Ctrl+t
'
Range("Y2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=25, Criteria1:="0", Operator:=xlAnd
Range("A1").Select
End Sub

Assign kbd shortcut to a macro using vba
 

Thanks Vergel, that works great.

Cheers, Dean

"Vergel Adriano" wrote:

Dean,

try this:

Application.MacroOptions Macro:="Macro_t", ShortcutKey:="t"


--
Hope that helps.

Vergel Adriano


"Dean" wrote:

I have copied a macro from one (master) spreadsheet to another using the code
below. But when I open the spreadsheet which the code has been copied into up
the kbd shortcut which I had given it is lost. The code works if I go alt+F8
| run; and I can re-assign the kbd shortcut through options but I want it to
be there automatically. Is there any way to do this when I copy the code
across?
Thanks, Dean.

'copy macro to validated wookbook
Dim FName As String
With Workbooks("TEAMS_Master.xls")
FName = .Path & "\code.txt"
.VBProject.VBComponents("Module9").Export FName
End With
Workbooks("CBev.xls").VBProject.VBComponents.Impor t FName

'code I am copying to new workbook
Sub Macro_t()
'
' Macro_t Macro
' Macro recorded 05/04/2007 by Dean
'
' Keyboard Shortcut: Ctrl+t
'
Range("Y2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=25, Criteria1:="0", Operator:=xlAnd
Range("A1").Select
End Sub