Keyboard Shortcut Question

[Russ At Index]

Hi,

There are , as Smitty once famously posted on this site , hundreds
no n thousands of Excel keyboard shortcuts ( Hi Smitty) ,

I am looking for a keyboard shortcut that will just enter a month ,
not necessarily the current one but one of the users choice.

Where ctrl + ; will give you 27/07/2010 , i am after something similar
but with just the month , say ctrl + ; + 1 would enter January .......

Thankfully the above isn't the answer !!!

Any ideas , as always appreciated ,

TIA

Russ

 

[macness]

You're probably looking at some VBA to tie a sub to a keystroke.

You can always use a =month(thedate) where 'thedate' is a cell reference to a date or a serial date for excel.

 

[Scott Huish]

I would decide what range you want this to happen in and use the Change Event.

Say you want this to happen in the range of A1:A12

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A1:A12"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then c = Format((c Mod 12) * 29, "mmmm")
    Next
Application.EnableEvents = True
End Sub

[/CODE]

Then you could just type the month number in this range, and it will change it to the month name

 

[Russ At Index]

Hi Hotpepper ,

Brilliant piece of code , thank you ,

Possible addition to code to have the year ( yy)
after your keystroke , so 1 would give me January 10 ??

Thanks Again ,

Russ

 

[Michael M]

Hi Russ
I don't believe there is such a creature

Ctrl +semi colon = Date
Ctrl + Shift + # = Changes the Format to (Day, Month, Year)

I'd suggest a Macro would be the other option

I'm willing to bet someone can prove me wrong though.....LOL !!

 

[Fazza]

or

Sub EnterMonth()
 
  'Can be assigned to a shortcut key from Excel via ALT-F8, select the macro and then options.
 
  Dim i As Variant
  On Error Resume Next
  i = InputBox(Prompt:="Enter month number? [1 to 12]", Title:="Month Entry", Default:=1)
  Select Case i
    Case 1 To 12: Selection.Value2 = "'" & Format$(DateSerial(Year(Date), i, 1), "mmmm yy")
    Case Else
  End Select
 
End Sub