PDA

View Full Version : Solved: Looking to change a character to another...



Byakko
03-19-2009, 01:13 AM
Hi,

I took a quick look to see if there was anything that would cover my request but found nothing. If there is already a post, my apologies. Also up front I am very new to VBA... three days and counting.

What I am attempting to do is the following.

I have a spreadsheet that I want to have function in both Excel 2003 & 2007.

Basically you have a column per person (Currently D thru M), and a task per row (5 thru 24). Thus the Range is D5:M24. These cells are formated as hh:mm. Currently you type in the amount of time for the task, for example, Task 1 would be as follows, 1:20, 0:30, 0:40 (note that some of the cells will be empty as not everyone will necessarily perform the task. The sheet takes the total time taken for the task(in this example 2:30 min) and converts it to a percentage (2.50) which is placed in column 'O' (N is used for something else not related to this issue. The sheet does this for each task. The totals are then copied to another sheet (external).

What I would like to be able to do for the cells in the range D5:M24 is to be able to type in the time using a decimal (.) instead of a colon (:) and upon exiting the cell hav it convert the decimal to a colon. I want to do this simply for speed as a decimal does not require the shift key and is located in the number pad.

Also as a secondary item, it would be nice to be able to simply type in say 10 and have 10:00 as a result or say .2 and get 0:20. These are lesser wants and if we can cover them here great, if not, that is okay.

Any help is definately appreciated.

Note: There are two additional ranges (D26:M45, D48:M65) that I left out for simplicity sake, all three are to be handled the same.

Thanks in advance,
Terry

Bob Phillips
03-19-2009, 01:26 AM
See http://www.cpearson.com/Excel/DateTimeEntry.htm

Byakko
03-19-2009, 01:55 AM
Thanks for the link... I have a better understanding now and am able to make the example work. However the one annoying thing that I am coming up against is that I have to leave and then re-enter the cell for the code to run.

Any Thoughts?

Bob Phillips
03-19-2009, 02:13 AM
Can you post your workbook so that I can see properly what you mean.

Byakko
03-19-2009, 04:53 AM
No problem, thanks for your time.

Terry

Byakko
03-19-2009, 05:00 AM
I just figured out what I was doing... I was using the SelectionChange event and not the Change event.

Again, thanks for the link, and your time.

Terry