Formatting a cell to Display MONTH ONLY

[Mister H]

Hi All:

Hopefully this is an easy one. I am trying to format a cell so that it will display the month (Full Name) after the person gets out of the cell.

I want the user to enter 1 to 12 and then have the Alpha of that month displayed whern they exit the cell. I can get it to Display the month but ionly if I type the year as well.

If the user type a 6 or 06 I want that cell to display June when they exit it.

Any suggestions?

THANKS,
Mark

 

[BruceyBonu$]

Great bit of code ... Think there is one slight adjustment required to this line
Target = Format(Target.Value & "/1/2006", "mmmm")

should be..

Target = Format("1/" & Target.Value & "/2006", "mmmm")

 

[BruceyBonu$]

my apologies.. Im thinking in UK dates

 

[tmcfadden]

my apologies.. Im thinking in UK dates

True, I didn't even think about Localization! Good catch though.

 

[Mister H]

Hi tmcfadden:

I must have been typing my Reply while you were typing your solution. THANKS for your code. I tried it but whenever I entered any number in B25 (the cell I am working with) it always showed January. This could be due to my lack of VBA understanding.

I then replaced the one line that BruceyBonu$ suggested and it seems to be working. However I now see that BruceyBonu$ now says that he was confused and now I am too. The following is the code I am using that seems to be working. Maybe one of you can tell me if this will work for me without messing something up in the background.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngChangeToMonth As Range, Isect
Set rngChangeToMonth = Sheet1.Range("B25")

If (Target.Cells.Count = 1) Then
    Set Isect = Application.Intersect(Target, rngChangeToMonth)
    If Not (Isect Is Nothing) Then
        Target = Format("1/" & Target.Value & "/2006", "mmmm")
    End If
End If
End Sub

Also, since the code has 2006 in it will this effect anything when we reach the New Year?

THANKS Again to ALL of you,
Mark

 

[dcardno]

If you needed it to work with different regional settings, you could use:

Target = Format(DateSerial(2006,target.value,1), "mmmm")

 

[tmcfadden]

It's because in the states we use the first digit as the month, second as the day, third as the year. For example, 2/1/2006 is Februrary 1st, 2006.

In England ( and probably most of the rest of the world ), 2/1/2006 is January 2nd, 2006. I would prefer it that way actually. I makes more sense.

The year doesn't matter because we're using "mmmm" to display the month.

-Tim

 

[BruceyBonu$]

If you wanna tighten it up a little try this , to force user to enter a number between 1 and 12 ..


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngChangeToMonth As Range, Isect
Set rngChangeToMonth = Sheet1.Range("a1:a100")
NumChk = Target.Cells.Value

If (Target.Cells.Count = 1) Then
Set Isect = Application.Intersect(Target, rngChangeToMonth)
If Not (Isect Is Nothing) Then
If Application.WorksheetFunction.IsNumber(NumChk) Then
If (Target.Cells.Value > 0 And Target.Cells.Value < 13) Then
Target = Format("1/" & Target.Value & "/2006", "mmmm")
Else:
MsgBox ("please enter a number between 1 and 12")
Target.Select
End If
Else:
MsgBox ("please enter a number between 1 and 12")
Target.Select

End If
End If
End If

End Sub

 

[BruceyBonu$]

Apologies, my last post of code had an error, below works (tested this time !)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngChangeToMonth As Range, Isect
Set rngChangeToMonth = Sheet1.Range("a1:a100")
NumChk = Target.Cells.Value

If (Target.Cells.Count = 1) Then
    Set Isect = Application.Intersect(Target, rngChangeToMonth)
    If Not (Isect Is Nothing) Then
        If Application.WorksheetFunction.IsNumber(NumChk) = True Then
            If (Target.Cells.Value > 0 And Target.Cells.Value < 13) Then
            Target = Format("1/" & Target.Value & "/2006", "mmmm")
            Else:
                MsgBox ("please enter a number between 1 and 12")
                Target.Select
            End If
        Else:
            MsgBox ("please enter a number between 1 and 12")
            Target.Select
        End If
        
    End If
End If

End Sub

 

[Mister H]

Hi BruceyBonu$:

THANKS for your help. I will go with the final code you wrote. Your assitance (and the others) is GREATLY APPRECIATED.

Have an EXCELLENT day,
Mark

 

[tmcfadden]

[sarchasm]Oh sure... sure... go with HIS code... I see how it is![/sarchasm]