Macro keyboard shortcuts

  • I have completed a complex macro, and if I go to Tools>Macros>Macro, select the macro, and hit "Run" it works flawlessly. However, every time I try to run it using a keyboard shortcut which I designated, it runs through the first little part of the macro and stops.

    Can someone tell me why?


  • Re: Macro keyboard shortcuts

    This is just a piece of the code, but should give some kind of idea. Let me know if this helps. The shortcut I designated was Ctrl-Shift-A.

    [vba]Sub CashMgmt_Array()

    strDate = Right(ActiveCell.Worksheet.Name, 6)
    strPostDate = Left(strDate, 2) & "/" & Mid(strDate, 3, 2) & "/" & Right(strDate, 2)
    strTranallFile = "K:\Servicing\LCSG Forms and Letters - Global Servicing\Cash Management Reports\SL Cash Management\"
    strTranall = "Tranall " & strDate
    strStrategyERMFile = "K:\Servicing\LCSG Forms and Letters - Global Servicing\Cash Management Reports\SL Cash Management\"
    strStrategyERM = "StrategyERM " & strDate
    strInvestorPath = "K:\Servicing\LCSG Forms and Letters - Global Servicing\Cash Management Reports\SL Cash Management\"
    strInvFileName = "Sun Life Cash Management Report"

    'Change name of Tranall file
    On Error GoTo HaveToSaveFile
    ActiveWorkbook.SaveAs Filename:=strTranallFile & strTranall & ".xls", FileFormat _
    :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False

    'Import StrategyERM file
    On Error GoTo DoesNotExist
    Workbooks.OpenText Filename:=strStrategyERMFile & strStrategyERM & ".txt", Origin:=437, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
    , 1), Array(13, 1), Array(26, 1), Array(37, 1), Array(44, 1), Array(52, 1), Array(61, 1), _
    Array(70, 1), Array(79, 1), Array(94, 1), Array(104, 1), Array(115, 1), Array(126, 1), _
    Array(137, 1), Array(150, 1), Array(160, 1), Array(173, 1), Array(187, 1)), _

    'Create header row
    'Delete Top 3 Rows
    Rows("1:3").Delete Shift:=xlUp

    'Merge Row 2 and 3 into Row 1
    Range("A1").FormulaR1C1 = "=R[1]C&"" ""&R[2]C"
    Range("A1").AutoFill Destination:=Range("A1:S1"), Type:=xlFillDefault

    'Copy / Paste Special
    Selection.PasteSpecial Paste:=xlPasteValues

    'Delete Row 2 and 3
    Rows("2:3").Delete Shift:=xlUp

    'Delete Unnecessary Columns
    Range("B:B,D:D,G:J,L:Q").Delete Shift:=xlToLeft

    'Sort StrategyERM by Transaction Description column
    Cells.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    Right here is where the shortcut ends
    'End Sub
    'Sub CM2_Compact()

    'Check if transaction column equals "Payment Received" or "Escrow Received"
    'If so don't delete, otherwise delete
    StratFinalRow = Range("E65536").End(xlUp).Row
    StratFirstRow = Range("A1").End(xlUp).Row

    Do Until StratFinalRow = StratFirstRow
    strTransaction = Cells(StratFinalRow, "G")
    If strTransaction <> "PMT REC'D" And strTransaction <> "ESCROW PMT" Then
    Selection.Delete Shift = xlUp
    'Delete dash from loan number
    strLoanNum = Cells(StratFinalRow, "A")
    Cells(StratFinalRow, "A") = Left(strLoanNum, 2) & Right(strLoanNum, 7)
    End If
    StratFinalRow = StratFinalRow - 1

    'Save as Excel file
    On Error GoTo HaveToSaveFile
    ActiveWorkbook.SaveAs Filename:=strStrategyERMFile & strStrategyERM & ".xls", FileFormat:=xlNormal _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

    'Move Tranall sheet to StrategyERM workbook
    Windows(strTranall & ".xls").Activate
    Sheets(strTranall).Move Before:=Workbooks(strStrategyERM & ".xls").Sheets(1)

    End Sub[/vba]

  • Re: Macro keyboard shortcuts

    Right. No error messages, the macro just stops. If I run the macro without the shortcut key it works fine. It also works fine if I assign a button on the toolbar for it and run it from there. It is only if I try to use the assigned keyboard shortcut that it has problems.

    I'm wondering if there are any special rules in relation to the macro keyboard shortcuts.

    Any suggestions would be appreciated.

  • Re: Macro keyboard shortcuts

    What is the shortcut key that you have assigned it to. Make sure that it is not one that is used for something else aswell.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!