If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
ENTERING TIME
While I've not had to do anything like this myself, it would seem you can
make the "single dot to colon" replacement both position sensitive and automatic without anyone having to remember anything. For example, the following installed in the Workbook module should do the "single dot to colon" replacement **only** for Column E on the worksheet named "Sheet3" and treat the dot normally everywhere else... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Sh.Name = "Sheet3" Then If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If Else .DeleteReplacement "." End If End With End Sub -- Rick (MVP - Excel) "Ragdyer" wrote in message ... Expanding on that old trick: When the girls in the office where finished entering the time card data on Monday morning for the previous week, they always forgot to change back until they noticed the mistakes they were making (usually wasting a half hour's work). So now we use the AutoCorrect to replace *2* decimals with a colon. 12..15 is just about as easy to use as 12.15, AND, some of them never have to change back, leaving it in force indefinitely. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Another trick... **Temporarily** set up an AutoCorrect option to replace a decimal point with the colon. Then enter the time in 24 hr format using the decimal point instead of the colon. Most people probably use the numeric keypad for entering numbers. It's a lot more ergonomic to hit the decimal point key than to have to reach over to the qwerty keys and do shift colon. Just remember to reset the AutoCorrect option when you're done. Then reformat the times as desired. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are talking about entering the current time (of day), then Jacob has given you your answer. If, on the other hand, you mean a time other than the current time (such as would be taken from a log sheet of some kind), then you will need a macro to do what you want. Are you entering 24-hour time values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400 pm with a space between them)? Are you entering the seconds as well (and, if so, will they always be available for each entry or not)? -- Rick (MVP - Excel) "Cletus" wrote in message ... I have a spreadsheet in excel that I record length of time in. How can I enter the time and have the : automatically entered for me? |
#12
|
|||
|
|||
ENTERING TIME
Hi Rick
That's a very neat method. I wondered why you had chosen to put it in a workbook module, rather than just place it in the relevant worksheet Private Sub Worksheet_Change(ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If End With End Sub -- Regards Roger Govier "Rick Rothstein" wrote in message ... While I've not had to do anything like this myself, it would seem you can make the "single dot to colon" replacement both position sensitive and automatic without anyone having to remember anything. For example, the following installed in the Workbook module should do the "single dot to colon" replacement **only** for Column E on the worksheet named "Sheet3" and treat the dot normally everywhere else... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Sh.Name = "Sheet3" Then If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If Else .DeleteReplacement "." End If End With End Sub -- Rick (MVP - Excel) "Ragdyer" wrote in message ... Expanding on that old trick: When the girls in the office where finished entering the time card data on Monday morning for the previous week, they always forgot to change back until they noticed the mistakes they were making (usually wasting a half hour's work). So now we use the AutoCorrect to replace *2* decimals with a colon. 12..15 is just about as easy to use as 12.15, AND, some of them never have to change back, leaving it in force indefinitely. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Another trick... **Temporarily** set up an AutoCorrect option to replace a decimal point with the colon. Then enter the time in 24 hr format using the decimal point instead of the colon. Most people probably use the numeric keypad for entering numbers. It's a lot more ergonomic to hit the decimal point key than to have to reach over to the qwerty keys and do shift colon. Just remember to reset the AutoCorrect option when you're done. Then reformat the times as desired. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are talking about entering the current time (of day), then Jacob has given you your answer. If, on the other hand, you mean a time other than the current time (such as would be taken from a log sheet of some kind), then you will need a macro to do what you want. Are you entering 24-hour time values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400 pm with a space between them)? Are you entering the seconds as well (and, if so, will they always be available for each entry or not)? -- Rick (MVP - Excel) "Cletus" wrote in message ... I have a spreadsheet in excel that I record length of time in. How can I enter the time and have the : automatically entered for me? |
#13
|
|||
|
|||
ENTERING TIME
Two things...
First, you can't use the Change event as your posted code shows because the change in the AutoCorrect won't take place at the right time. Using the Change event means that each time you enter Column E, the first change you make into a cell in that column won't have the "dot to colon" AutoCorrect replacement available (the replacement won't be installed into AutoCorrect until *after* the entry is complete; hence, it won't go back and change the just completed entry as it is no longer being typed). Worse yet, if you switch to a different column *after* making a **single** (first) entry in Column E, the entry in that new column will have the "dot to colon" replacement feature active (the first entry turns it on for the next entry and that second entry, being in a different column than E, won't turn it off until *after* the entry has been completed). You must use the SelectionChange event to get the functionality to stick to the selected column. I'm assuming you meant that and just typed your code into your message off the top of your head, but I wanted readers of the thread to understand why the Change event was the wrong one to use. Second, the reason why I used the SheetSelectionChange event in the workbook module is because if you use the SelectionChange event in the relevant worksheet module, that event will not be activated when you switch to a new worksheet. So, if you enter Column E and then switch to another worksheet, whether you make an entry in Column E or not (remember, we are using the SelectionChange event, not the Change event), the AutoCorrect "dot to colon" replacement will be active for that entire sheet... actually, for the rest of the workbook until you return to Column E on the (relevant) worksheet, and move to a different column on that relevant worksheet. -- Rick (MVP - Excel) "Roger Govier" roger@technology4unospamdotcodotuk wrote in message ... Hi Rick That's a very neat method. I wondered why you had chosen to put it in a workbook module, rather than just place it in the relevant worksheet Private Sub Worksheet_Change(ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If End With End Sub -- Regards Roger Govier "Rick Rothstein" wrote in message ... While I've not had to do anything like this myself, it would seem you can make the "single dot to colon" replacement both position sensitive and automatic without anyone having to remember anything. For example, the following installed in the Workbook module should do the "single dot to colon" replacement **only** for Column E on the worksheet named "Sheet3" and treat the dot normally everywhere else... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Sh.Name = "Sheet3" Then If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If Else .DeleteReplacement "." End If End With End Sub -- Rick (MVP - Excel) "Ragdyer" wrote in message ... Expanding on that old trick: When the girls in the office where finished entering the time card data on Monday morning for the previous week, they always forgot to change back until they noticed the mistakes they were making (usually wasting a half hour's work). So now we use the AutoCorrect to replace *2* decimals with a colon. 12..15 is just about as easy to use as 12.15, AND, some of them never have to change back, leaving it in force indefinitely. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Another trick... **Temporarily** set up an AutoCorrect option to replace a decimal point with the colon. Then enter the time in 24 hr format using the decimal point instead of the colon. Most people probably use the numeric keypad for entering numbers. It's a lot more ergonomic to hit the decimal point key than to have to reach over to the qwerty keys and do shift colon. Just remember to reset the AutoCorrect option when you're done. Then reformat the times as desired. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are talking about entering the current time (of day), then Jacob has given you your answer. If, on the other hand, you mean a time other than the current time (such as would be taken from a log sheet of some kind), then you will need a macro to do what you want. Are you entering 24-hour time values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400 pm with a space between them)? Are you entering the seconds as well (and, if so, will they always be available for each entry or not)? -- Rick (MVP - Excel) "Cletus" wrote in message ... I have a spreadsheet in excel that I record length of time in. How can I enter the time and have the : automatically entered for me? |
#14
|
|||
|
|||
ENTERING TIME
Hi Rick
I wrote and posted without testing first. Since trying it, I can see exactly what you mean and fully understand the reason for your method. I shouldn't have doubted a "true VB guy"bg -- Regards Roger Govier "Rick Rothstein" wrote in message ... Two things... First, you can't use the Change event as your posted code shows because the change in the AutoCorrect won't take place at the right time. Using the Change event means that each time you enter Column E, the first change you make into a cell in that column won't have the "dot to colon" AutoCorrect replacement available (the replacement won't be installed into AutoCorrect until *after* the entry is complete; hence, it won't go back and change the just completed entry as it is no longer being typed). Worse yet, if you switch to a different column *after* making a **single** (first) entry in Column E, the entry in that new column will have the "dot to colon" replacement feature active (the first entry turns it on for the next entry and that second entry, being in a different column than E, won't turn it off until *after* the entry has been completed). You must use the SelectionChange event to get the functionality to stick to the selected column. I'm assuming you meant that and just typed your code into your message off the top of your head, but I wanted readers of the thread to understand why the Change event was the wrong one to use. Second, the reason why I used the SheetSelectionChange event in the workbook module is because if you use the SelectionChange event in the relevant worksheet module, that event will not be activated when you switch to a new worksheet. So, if you enter Column E and then switch to another worksheet, whether you make an entry in Column E or not (remember, we are using the SelectionChange event, not the Change event), the AutoCorrect "dot to colon" replacement will be active for that entire sheet... actually, for the rest of the workbook until you return to Column E on the (relevant) worksheet, and move to a different column on that relevant worksheet. -- Rick (MVP - Excel) "Roger Govier" roger@technology4unospamdotcodotuk wrote in message ... Hi Rick That's a very neat method. I wondered why you had chosen to put it in a workbook module, rather than just place it in the relevant worksheet Private Sub Worksheet_Change(ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If End With End Sub -- Regards Roger Govier "Rick Rothstein" wrote in message ... While I've not had to do anything like this myself, it would seem you can make the "single dot to colon" replacement both position sensitive and automatic without anyone having to remember anything. For example, the following installed in the Workbook module should do the "single dot to colon" replacement **only** for Column E on the worksheet named "Sheet3" and treat the dot normally everywhere else... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) With Application.AutoCorrect On Error Resume Next If Sh.Name = "Sheet3" Then If Target.Column = 5 Then .AddReplacement ".", ":" Else .DeleteReplacement "." End If Else .DeleteReplacement "." End If End With End Sub -- Rick (MVP - Excel) "Ragdyer" wrote in message ... Expanding on that old trick: When the girls in the office where finished entering the time card data on Monday morning for the previous week, they always forgot to change back until they noticed the mistakes they were making (usually wasting a half hour's work). So now we use the AutoCorrect to replace *2* decimals with a colon. 12..15 is just about as easy to use as 12.15, AND, some of them never have to change back, leaving it in force indefinitely. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Another trick... **Temporarily** set up an AutoCorrect option to replace a decimal point with the colon. Then enter the time in 24 hr format using the decimal point instead of the colon. Most people probably use the numeric keypad for entering numbers. It's a lot more ergonomic to hit the decimal point key than to have to reach over to the qwerty keys and do shift colon. Just remember to reset the AutoCorrect option when you're done. Then reformat the times as desired. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... If you are talking about entering the current time (of day), then Jacob has given you your answer. If, on the other hand, you mean a time other than the current time (such as would be taken from a log sheet of some kind), then you will need a macro to do what you want. Are you entering 24-hour time values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400 pm with a space between them)? Are you entering the seconds as well (and, if so, will they always be available for each entry or not)? -- Rick (MVP - Excel) "Cletus" wrote in message ... I have a spreadsheet in excel that I record length of time in. How can I enter the time and have the : automatically entered for me? |
|
Thread Tools | |
Display Modes | |
|
|