Macro for a month

Brass Contributor

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?

6 Replies

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

 

DKoontz_0-1637608578673.png

 

@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

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.

@marty007 

try the attachment. it works only for excel though.

OK, thanks. That works.

My question was answered. Thanks.

@marty007