Hi Ken,

I think you are on the wrong track with
trying to convert something wrong into something right.

The complications would be endless.

You should try to prevent something wrong inserted
in a cell beforehand.

If you can't use a datetimepicker-control or whatever,
I think, you better set up two dropdown-listboxes,
one for the hour, the other for the minute,
or try some other method for selecting from
predefined values.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

G'day there Helmut,

I think you are on the wrong track with
trying to convert something wrong into something right.

While you're quite correct with the perspective on conversion,
that aspect is out of my control.

The complications would be endless.

So I've discovered!!! <g

You should try to prevent something wrong inserted
in a cell beforehand.

The problem is that I have no access to the original forms.
They're simply an electronic copy of the old handwritten form that used
to be used. I work for a government dept that sees MS Office as a
glorified typewriter. Automation of documents is just about non existent
and isn't about to change in the near future <sigh.

If you can't use a datetimepicker-control or whatever,
I think, you better set up two dropdown-listboxes,
one for the hour, the other for the minute,
or try some other method for selecting from
predefined values.

That's for my next project =)

For the moment, I can not change the input forms. They are totally
beyond my control. I need to work with the data I get regardless of how
crappy it may be. That might be why the supervisor of the section I'm
writing this for has been trying to get someone to take on the project
for about 5 years. One developer even tried a dedicated database without
success.

Maybe because I'm only a hobbyist, not a developer/programmer, I
was too dim to realise that it couldn't be done <g.

In a serious vein I've already started working on the input side
of things. The form that's used for roster entry does have a few macros,
but they're only keystroke shortcuts that were included when the dept
introduced the first electronic form on our early Apple 603's. The form
hasn't changed in form or presentation since then.

I've already presented a prototype and it was warmly received.
However I wasn't encouraged when I asked the head of the section that
writes our electronic documents for a copy of the workbook with the
password removed so I could access those modules and no one there knew
what I was talking about. They thought I was talking about the worksheet
password (on a sheet that wasn't protected) and weren't aware that there
was a password on the module. That was 3-4 years ago and the dept is
still using the same worksheet and I'm still waiting for my unlocked
copy. I may be wrong, but since every electronic form we get is based on
MSWord, with tables for anything with rows & columns - including
financial calculations, I get the feeling that there's not a lot of
Excel expertise available.


--
See ya,
Ken McLennan
Qld, Australia

Hi Ken,

i had put up this one before,
but didn't dare to post it.

Though perfection is not possible,
it might help you.

Sub Test()
Dim ba As Boolean ' "a" was found
Dim bp As Boolean ' "p" was found
Dim strC As String
Dim str1 As String
Dim str2 As String
Dim lngC As Long
str1 = "6:00am On Call"
For lngC = 1 To Len(str1)
strC = (Mid(str1, lngC, 1))
Select Case strC
Case "1" To "9": str2 = str2 & strC
Case "a"
If ba = False Then str2 = str2 & strC
ba = True
Case "p"
If bp = False Then str2 = str2 & strC
bp = True
End Select
Next
MsgBox str2
End Sub

G'day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"