How do i create a VBA for a specific column

[sycamorm]

Hi all, this is the first time I have started a new thread so I hope this works.

I have a excel spreadsheet where along the top I have the different months of the year. Down columns A,B,C &D I have people’s names and various other information. I want the imputers of the information to be able to enter for example "8" into the April column and for "8th April" to be shown.

There are 4 dates for each month. I already have tried writing my own but when "8" is entered it also shows "8th April" in March and Feb column!!!!!

I have already got a VBA (I think it’s called this) and this is below

Private Sub Worksheet_Change(ByVal Target As Range)
If UCase(Target) = UCase("A") Then Target = "AUTHORISED A/L"
If UCase(Target) = UCase("M") Then Target = "AUTHORISED M/L"
If UCase(Target) = UCase("SI") Then Target = "REPORTED SICKNESS"
If UCase(Target) = UCase("W") Then Target = "AUTHORISED RECALL TO WARD"
If UCase(Target) = UCase("SS") Then Target = "AUTHORISED SHIFT SWAP"
If UCase(Target) = UCase("O") Then Target = "AUTHORISED (OTHER)"
End Sub

I need to keep this aspect of the document, but am still unsure of how to input the months.

I hope this makes sense and really hope there is some one out there that might be able to help.


Thanks in advance


Marc

[arlu1201]

First - please put your code in code tags. You are violating the forum rules by not doing so.

Also, please attach a sample workbook so we can help you better.

[foxguy]

Worksheet_Change(ByVal Target)
    With Target.Worksheet
        ' Find the Name of the month that is located in row 1
        If .Cells(1,Target.Column) = "April" Then
            On Error Resume Next
            Target.Value = DateSerial(2011,4,Target.Value)
            On Error GoTo 0
        ElseIf .Cells(1,Target.Column) = "March" Then
            On Error Resume Next
            Target.Value = DateSerial(2011,3,Target.Value)
            On Error GoTo 0
         .....
         End If
    End With
     '  rest of sub
End Sub

[Paul]

Hi Sycamorm,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here