Forum Discussion

marty007's avatar
marty007
Brass Contributor
Nov 22, 2021

Macro for a month

Is it possible to create a keyboard shortcut in Excel 2019 (let's say Control-M) That would return the month following the date I enter the macro. for example: if I enter Control-M, today (November), I would like the macro to say "December Invoice" and would this macro be able to be used in Outlook 2019, as well?

Macros and VBA

6 Replies

  • Josh_Waldner's avatar
    Josh_Waldner
    Brass Contributor
    Nov 24, 2021

    marty007 

    try pasting this into your code editor:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+m
    '


    ActiveCell.Formula = Month(Date) + 1

    If ActiveCell.Value = 1 Then
    ActiveCell.Value = "January Invoice"
    End If
    If ActiveCell.Value = 2 Then
    ActiveCell.Value = "Febuary Invoice"
    End If
    If ActiveCell.Value = 3 Then
    ActiveCell.Value = "March Invoice"
    End If
    If ActiveCell.Value = 4 Then
    ActiveCell.Value = "April Invoice"
    End If
    If ActiveCell.Value = 5 Then
    ActiveCell.Value = "May Invoice"
    End If
    If ActiveCell.Value = 6 Then
    ActiveCell.Value = "June Invoice"
    End If
    If ActiveCell.Value = 7 Then
    ActiveCell.Value = "July Invoice"
    End If
    If ActiveCell.Value = 8 Then
    ActiveCell.Value = "August Invoice"
    End If
    If ActiveCell.Value = 9 Then
    ActiveCell.Value = "September Invoice"
    End If
    If ActiveCell.Value = 10 Then
    ActiveCell.Value = "October Invoice"
    End If
    If ActiveCell.Value = 11 Then
    ActiveCell.Value = "November Invoice"
    End If
    If ActiveCell.Value = 12 Then
    ActiveCell.Value = "December Invoice"
    End If

    End Sub

    • marty007's avatar
      marty007
      Brass Contributor
      Nov 24, 2021
      Thank you very much but I think this is beyond my ability. I think I pasted your code in the Microsoft Visual Basic for Applications but I don't know what to do with it. Is this supposed to work in Excel or Outlook. When I enter control-m, nothing happens. I could send a screen print of what I did, if I could send you an attachment.
  • DKoontz's avatar
    DKoontz
    Iron Contributor
    Nov 22, 2021

    marty007 

     

    A Macro may be a little overkill, here is a formula that will return do the same:

    =+TEXT(EOMONTH(A1,1),"MMMM")&" Invoice"

     

    This will look at a date in A1, then add one month onto that date, and return the new month + "Invoice". The text function is to format the date correctly. When using &, excel uses the general text format unless specifically stated.

     

     

Resources