Combo box to auto fill rest of cell 1

[e2the1stpower]

I have been working on this spreadsheet for awhile now and I am always looking to improve it. I have come to the conclusion that I will not be able to have excel recognize when it is am or pm so I came up with another idea, sure I am not the only one to have thought of this. I want to use a combo box that will allow someone to select am or pm so when they type a time in any cell designated cell it will automatically put in am or pm. I only want the change to happen when someone types in the time is this possible or do you think there is a better way to go about this. Thanks in advanced for any help. I am using Excel 2002 sp3.

 

[AnotherHiggins]

-> I have come to the conclusion that I will not be able to have excel recognize when it is am or pm

Why not? How are you entering your times?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

If the user enters any time between 0:00 and 12:00, Excel assumes AM. So all you need is an AM/PM control. You will need VBA code to control the time.

Ths code assumes that your time is in column A and the control "button" is the adjacent cell in column B. If you want some other column, change the BOLD value appropriately.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 2 Then 'col 2 is col B, adjacent to TIME: change accordingly
            Select Case .Offset(0, -1).Value - 0.5
                Case Is > 0
                    .Offset(0, -1).Value = .Offset(0, -1).Value - 0.5
                Case Is <= 0
                    .Offset(0, -1).Value = .Offset(0, -1).Value + 0.5
            End Select
            .Offset(0, -1).Select
        End If
    End With
End Sub

copy this code.

Right click the sheet tab and select, View Code

Paste into the Code window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

this works better...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 2 Then 'col 2 is col B, adjacent to TIME: change accordingly
            Select Case .Offset(0, -1).Value - 0.5
                Case Is >= 0
                    .Offset(0, -1).Value = .Offset(0, -1).Value - 0.5
                Case Is < 0
                    .Offset(0, -1).Value = .Offset(0, -1).Value + 0.5
            End Select
            .Offset(0, -1).Select
        End If
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}