I want to format a cell with a custom time format that will allow my users to
type in the time without the colons. [1014 = 10:14]. I've tried several
different ways of setting up a custom format to do this, but nothing works.
Anybody have any ideas?

Thanks!
--
Sherry

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = TimeSerial(Int(.Value / 100), _
.Value - Int(.Value / 100) * 100, 0)
.NumberFormat = "HH:MM"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
I want to format a cell with a custom time format that will allow my users
to
type in the time without the colons. [1014 = 10:14]. I've tried several
different ways of setting up a custom format to do this, but nothing
works.
Anybody have any ideas?

Thanks!
--
Sherry

Thanks, Bob, for your help. This works great, except I need for the date
format to be hours and minutes only, and with no a.m. or p.m. Users will
choose a.m. or p.m. from another field, so if they type in 214 it should read
2:14 whether it's a.m. or p.m. It works if I use military time to indicate
p.m.s but I need it to not be either one. I know virtually nothing about
visual basic, so I don't know how to edit the code to make that work. Is it
possible?

Thanks
--
Sherry


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = TimeSerial(Int(.Value / 100), _
.Value - Int(.Value / 100) * 100, 0)
.NumberFormat = "HH:MM"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
I want to format a cell with a custom time format that will allow my users
to
type in the time without the colons. [1014 = 10:14]. I've tried several
different ways of setting up a custom format to do this, but nothing
works.
Anybody have any ideas?

Thanks!
--
Sherry

Sherry,

That shouldn't give AM/PM, it should just give a 24 hour clock. It is here,
I type 214, I get 02:14, not 2:14AM. I type 1414. I get 14:14, not 2:14PM.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
Thanks, Bob, for your help. This works great, except I need for the date
format to be hours and minutes only, and with no a.m. or p.m. Users will
choose a.m. or p.m. from another field, so if they type in 214 it should
read
2:14 whether it's a.m. or p.m. It works if I use military time to
indicate
p.m.s but I need it to not be either one. I know virtually nothing about
visual basic, so I don't know how to edit the code to make that work. Is
it
possible?

Thanks
--
Sherry


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = TimeSerial(Int(.Value / 100), _
.Value - Int(.Value / 100) * 100, 0)
.NumberFormat = "HH:MM"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
I want to format a cell with a custom time format that will allow my
users
to
type in the time without the colons. [1014 = 10:14]. I've tried
several
different ways of setting up a custom format to do this, but nothing
works.
Anybody have any ideas?

Thanks!
--
Sherry

Bob, thanks. I deleted it and pasted the code in again and it worked
perfectly! Thanks you so much!
--
Sherry


"Bob Phillips" wrote:

Sherry,

That shouldn't give AM/PM, it should just give a 24 hour clock. It is here,
I type 214, I get 02:14, not 2:14AM. I type 1414. I get 14:14, not 2:14PM.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
Thanks, Bob, for your help. This works great, except I need for the date
format to be hours and minutes only, and with no a.m. or p.m. Users will
choose a.m. or p.m. from another field, so if they type in 214 it should
read
2:14 whether it's a.m. or p.m. It works if I use military time to
indicate
p.m.s but I need it to not be either one. I know virtually nothing about
visual basic, so I don't know how to edit the code to make that work. Is
it
possible?

Thanks
--
Sherry


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = TimeSerial(Int(.Value / 100), _
.Value - Int(.Value / 100) * 100, 0)
.NumberFormat = "HH:MM"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ladypep" wrote in message
...
I want to format a cell with a custom time format that will allow my
users
to
type in the time without the colons. [1014 = 10:14]. I've tried
several
different ways of setting up a custom format to do this, but nothing
works.
Anybody have any ideas?

Thanks!
--
Sherry