i want to create a formula to change a months name

[change a month]

I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly.
Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me.
It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro.

 

[JE McGimpsey]

You can either have a value or a function in a cell, not both. When you
enter a value in the cell, the formula is overwritten.

You can use an event macro to change the value on entry. Put this in the
worksheet code module (right click on the worksheet tab and choose view
code):

Private Sub Worksheet_Change(ByVal rCell As Excel.Range)
With rCell
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then _
If IsNumeric(.Value) Then _
.Value = Format(DateSerial(2004, .Value, 1), "mmmm")
End With
End Sub

Change A1 to your desired cell.

 

[Sandy Mann]

As far as I know you can't get Excel to return the month with just a 2 digit
entry - how is Excel to know that you ment month 02 and not day 02? If you
custom format the cell as "mmmm" (without the quotes), and enter 01/01 for
January, 01/02 for February etc then you will get the month displayed.
Incidentally XL will assume the year is the current year and the day part of
the date can be any legal date.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

I want to create a formula to allow me to enter a # into a cell that will

display a corresponding months name in that cell. In doing an IF statement I
have been getting a cricular error, I assume because i'm creating the
formula IN the refrence cell, or Excel can't find the project or library.
Either way I'm sure I'm not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and have the

corresponding mont displayed in the cell I'm creating the formula in. Is
this possibe if written in the same cell? Sounds pretty simple but I can't
get an "IF" statement to work for me.

It's been a loooong time since I last worked with Excel so I'd appreciate

any help you can give. BTW: my office is 2002 Pro.

 

[David McRitchie]

This should allow you to type in a number 1 - 12, anything
else will show up as entered. Use of dateserial, I think
would be slower and the example had no provision for errors.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
x = Target.Formula
Application.EnableEvents = False 'should be part of Change macro
On Error Resume Next
Target.Value = Choose(x, "January", "February", "March", _
"April", "May", "June", "July", "August", "September", _
"October", "November", "December")
done:
If IsEmpty(Target.Value) Then Target.Formula = x
Application.EnableEvents = True 'should be part of Change macro
Exit Sub
End Sub

To install right click on the sheet tab, view code, insert your coding

More on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm
watch out for the following problem, when working with
change macros that terminate.
http://www.mvps.org/dmcritchie/excel/event.htm#problems

You'll probably want to change that the "Jan", "Feb", ...
but this was what you asked for.

I think you would get more out of newsgroups if you used your
first and last name, both more friendly and more professional.

 

[David McRitchie]

Ripped out unwanted coding and threw out the
the check for the column (column 1 is column A),
this should work better.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
if target.column <> 1 then exit sub
x = Target.Formula
Application.EnableEvents = False 'should be part of Change macro
On Error Resume Next
Target.Value = Choose(x, "January", "February", "March", _
"April", "May", "June", "July", "August", "September", _
"October", "November", "December")
done:
If IsEmpty(Target.Value) Then Target.Formula = x
Application.EnableEvents = True 'should be part of Change macro
Exit Sub
End Sub

 

[Steven]

Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out but maybe a decent, "User Name" would look and feel better

 

[Steven]

Sandy - thanks much for your input, really...thank you
Steven

----- Sandy Mann wrote: -----

As far as I know you can't get Excel to return the month with just a 2 digit
entry - how is Excel to know that you ment month 02 and not day 02? If you
custom format the cell as "mmmm" (without the quotes), and enter 01/01 for
January, 01/02 for February etc then you will get the month displayed.
Incidentally XL will assume the year is the current year and the day part of
the date can be any legal date.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

I want to create a formula to allow me to enter a # into a cell that will

display a corresponding months name in that cell. In doing an IF statement I
have been getting a cricular error, I assume because i'm creating the
formula IN the refrence cell, or Excel can't find the project or library.
Either way I'm sure I'm not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and have the

corresponding mont displayed in the cell I'm creating the formula in. Is
this possibe if written in the same cell? Sounds pretty simple but I can't
get an "IF" statement to work for me.

It's been a loooong time since I last worked with Excel so I'd appreciate

any help you can give. BTW: my office is 2002 Pro.

 

[Steven]

JE, thank you for the help, it did work. I thought, simple mindedly so apparently, it would be a matter of assigning a cell as a date then go from there. Again maybe a little simple mindedly so. With people like you and these others willing to help "newbies" such as my self, these responses puts a good light, almost a warm and fuzzy, on the "Geek-oid" myth.
Steve

----- JE McGimpsey wrote: ----

You can either have a value or a function in a cell, not both. When you
enter a value in the cell, the formula is overwritten

You can use an event macro to change the value on entry. Put this in the
worksheet code module (right click on the worksheet tab and choose view
code)

Private Sub Worksheet_Change(ByVal rCell As Excel.Range
With rCel
If .Count > 1 Then Exit Su
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
.Value = Format(DateSerial(2004, .Value, 1), "mmmm"
End Wit
End Su

Change A1 to your desired cell

 

[Anders S]

And another variant,

To JE and David: While I was working on this, both your replies came in and I
must admit that I have stolen a bit from both of you.

Using DateSerial, despise a possible slowdown, may have an advantage over hard
coding the month names, in that it will return the month names in language of
the system. On the other hand, if the output is to be in a specific language,
hard coding is the way to go.

My code is not any better than yours, but since I have it I can just as well
post it.

'*****
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim monthNum As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
monthNum = Target.Value
If monthNum < 1 Or monthNum > 12 Then Exit Sub
Application.EnableEvents = False
Target.Value = Format(DateSerial(1, monthNum, 1), "mmmm")
Application.EnableEvents = True
End Sub
'*****

Regards
Anders Silven

I want to create a formula to allow me to enter a # into a cell that will

display a corresponding months name in that cell. In doing an IF statement I
have been getting a cricular error, I assume because i'm creating the formula IN
the refrence cell, or Excel can't find the project or library. Either way I'm
sure I'm not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and have the

corresponding mont displayed in the cell I'm creating the formula in. Is this
possibe if written in the same cell? Sounds pretty simple but I can't get an
"IF" statement to work for me.

It's been a loooong time since I last worked with Excel so I'd appreciate any

help you can give. BTW: my office is 2002 Pro.

 

[David McRitchie]

This is another "improvement" on my code, checking the
IsNumeric from John's code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value < 1 Or Target.Value > 12 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Target.Value = Choose(Target.Value, "January", "February", _
"March", "April", "May", "June", "July", "August", _
"September", "October", "November", "December")
Application.EnableEvents = True 'should be part of Change macro
End Sub

I can find my postings on my email address which makes things
easy. My name is unique enough that I can even search on my
name to find my previous postings and replies.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out

but maybe a decent, "User Name" would look and feel better

 

[CLR]

Unless there is some special requirement for this unusual system of calling
up the Month names, you might consider a simple Data Validation Drop Down
box and just select the desired month from the drop-down list..............

Vaya con Dios,
Chuck, CABGx3

I want to create a formula to allow me to enter a # into a cell that will

display a corresponding months name in that cell. In doing an IF statement I
have been getting a cricular error, I assume because i'm creating the
formula IN the refrence cell, or Excel can't find the project or library.
Either way I'm sure I'm not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and have the

corresponding mont displayed in the cell I'm creating the formula in. Is
this possibe if written in the same cell? Sounds pretty simple but I can't
get an "IF" statement to work for me.

It's been a loooong time since I last worked with Excel so I'd appreciate

any help you can give. BTW: my office is 2002 Pro.

 

[JE McGimpsey]

Difference in style perhaps, though I certainly should have cycled
..EnableEvents.

For a non-looping Worksheet_Change() macro, speed is almost always
irrelevant - the code will be finished before the enter key has returned
to full up.

As for errors, I didn't assume that the user might not want to enter 13
or 27. It's more likely that the number should be limited to 1-12,
though. If that is the case, I'd be more inclined to return an error
value if a non-numeric entry or a numeric entry outside the bounds
[1-12] were entered, rather than exiting the sub:


Private Sub Worksheet_Change(ByVal rCell As Range)
Dim bValid As Boolean
With rCell
If .Address(False, False) = "A1" Then
Application.EnableEvents = False
If IsNumeric(.Value) Then
If .Value >= 1 And .Value <= 12 Then
.Value = Format(DateSerial(2004, _
.Value, 1), "mmmm")
bValid = True
End If
End If
If Not bValid Then .Value = CVErr(xlErrValue)
Application.EnableEvents = True
End If
End With
End Sub