ONKEY ony fires once

Hi there,

As soon as the "SendKeys" command is executed the colon character is entered into the active cell - this places the cell in Edit mode. As far as I know, it is not possible to execute a routine while a cell is in Edit mode, so this is probably why the routine executes only a single time.

Something along the lines of the following code might help to get you moving in the right direction - it takes a four-digit numeric value (e.g. 12.34) and converts it into a time value which "looks like" the numeric value (e.g. 12:34):

Please Login or Register  to view this content.

The highlighted may be altered to suit your own requirements.


Hope this helps.

Regards,

Greg M

Hi again,

If Target.Cells.CountLarge = 1 Then ' <-- applies the code to selected cells? might not need this since only one cell will be edited at a time. ?

I always include this to ensure that the subsequent code is NOT executed in cases where a range of cells is selected and the Delete key is pressed to clear the range.

If Not Intersect(Target, Me.Range(sRANGE_TO_CHANGE)) Is Nothing Then ' this i don't understand at all. is this a double negative? . . . wouldn't we want the range to intersect?

Yes, it IS a logical double negative, but it does exactly what we need. Yes, we DO want the range to intersect, and that is why we execute the code when the intersection is NOT Nothing.

The Intersect method returns a Range object if the Ranges represented by its arguments do in fact overlap, otherwise it returns Nothing - however, Nothing doesn't have any properties, so the only test you can perform is to check for NOT Nothing.

sTextValue = Target.Text ' <-- does this save the cell value to the var as text format?

Yes, it does, and yes you are right, Target.Value appears to work correctly also.

and this "If sTextValue Like "##.##" Then" . . . . so i tried a single digit wildcard (Like "?#.##") and also (Like "##.##" or Like "#.##") and got an error.

Sorry - mea culpa! To allow for the situation where a one-digit hour is entered as (e.g.) 01.23 or 1.23 further modifications to the code are required.

The following code allows times to be entered with formats such as 12.34, 00.23, 12.30, 00.20, 22.00 and 02.00:

Please Login or Register  to view this content.

I've attached a workbook which incorporates the above code.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Hi again,

Upon "mature reflection", the following approach might be a bit more straightforward:

Please Login or Register  to view this content.

Hope this helps also.

Regards,

Greg M