|
Entering HH:MM
Excel 2003
I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
Preformat the column as time and try this.
Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
Hi Don
I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
Did you place Don's code in the sheet module as directed?
Right click sheet tabview codeinsert this Are you entering 1300 in a cell in column E? Any other column will not trigger the event. If Target.Column < 5 Then Exit Sub Gord Dibben MS Excel MVP On Mon, 28 Sep 2009 15:01:02 -0700, JB Bates wrote: Hi Don I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
Yes I did. and i even tried opening a new sheet with nothing in it and going
through all these steps. still get the dame results "Gord Dibben" wrote: Did you place Don's code in the sheet module as directed? Right click sheet tabview codeinsert this Are you entering 1300 in a cell in column E? Any other column will not trigger the event. If Target.Column < 5 Then Exit Sub Gord Dibben MS Excel MVP On Mon, 28 Sep 2009 15:01:02 -0700, JB Bates wrote: Hi Don I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
You mention Windows Vista.
That is an operating system, which has nothing to do with Excel. What version of Excel or Office? I will assume 2007. Did you save the workbook as a macro-enabled workbook(*.xlsm)? Have you enabled macros when the workbook opens? Gord On Mon, 28 Sep 2009 16:43:01 -0700, JB Bates wrote: Yes I did. and i even tried opening a new sheet with nothing in it and going through all these steps. still get the dame results "Gord Dibben" wrote: Did you place Don's code in the sheet module as directed? Right click sheet tabview codeinsert this Are you entering 1300 in a cell in column E? Any other column will not trigger the event. If Target.Column < 5 Then Exit Sub Gord Dibben MS Excel MVP On Mon, 28 Sep 2009 15:01:02 -0700, JB Bates wrote: Hi Don I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
I thought being VISTA might be an important fact, sorry
Yes Excel 2007 Yes saved as XLSM Yes Macros enabled. Still does not work. Is there another way to have it read with other than the code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub "Gord Dibben" wrote: You mention Windows Vista. That is an operating system, which has nothing to do with Excel. What version of Excel or Office? I will assume 2007. Did you save the workbook as a macro-enabled workbook(*.xlsm)? Have you enabled macros when the workbook opens? Gord On Mon, 28 Sep 2009 16:43:01 -0700, JB Bates wrote: Yes I did. and i even tried opening a new sheet with nothing in it and going through all these steps. still get the dame results "Gord Dibben" wrote: Did you place Don's code in the sheet module as directed? Right click sheet tabview codeinsert this Are you entering 1300 in a cell in column E? Any other column will not trigger the event. If Target.Column < 5 Then Exit Sub Gord Dibben MS Excel MVP On Mon, 28 Sep 2009 15:01:02 -0700, JB Bates wrote: Hi Don I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
Entering HH:MM
Works for me.
I see you started another thread elsewhere and received some satisfactory replies. Gord On Mon, 5 Oct 2009 11:39:01 -0700, JB Bates wrote: I thought being VISTA might be an important fact, sorry Yes Excel 2007 Yes saved as XLSM Yes Macros enabled. Still does not work. Is there another way to have it read with other than the code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub "Gord Dibben" wrote: You mention Windows Vista. That is an operating system, which has nothing to do with Excel. What version of Excel or Office? I will assume 2007. Did you save the workbook as a macro-enabled workbook(*.xlsm)? Have you enabled macros when the workbook opens? Gord On Mon, 28 Sep 2009 16:43:01 -0700, JB Bates wrote: Yes I did. and i even tried opening a new sheet with nothing in it and going through all these steps. still get the dame results "Gord Dibben" wrote: Did you place Don's code in the sheet module as directed? Right click sheet tabview codeinsert this Are you entering 1300 in a cell in column E? Any other column will not trigger the event. If Target.Column < 5 Then Exit Sub Gord Dibben MS Excel MVP On Mon, 28 Sep 2009 15:01:02 -0700, JB Bates wrote: Hi Don I am trying to do this same thing in windows Vista and when I do what you directed it does not work. If I enter 1300 in the cell it displays as 0000 and the formula lines shows "7/23/1903 12:00:00 AM" any thoughts? "Don Guillett" wrote: Glad to help -- Don Guillett SalesAid Software "Carole O" wrote in message ... PERFECT!! Thank you so much, Don!! CaroleO "Don Guillett" wrote: Preformat the column as time and try this. Right click sheet tabview codeinsert this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 5 Then Exit Sub Application.EnableEvents = False Target = Left(Target, 2) & ":" & Right(Target, 2) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Carole O" wrote in message ... Excel 2003 I have a co-worker who enters time on a spreadsheet. She would like to have the time cells fomatted so when she enters 1456 it appears in the cell as 14:56. This doesn't work when I format, cell, time, 13:30. I've looked at format, cell, special (which would be a logical place to put something like this), to no avail. TIA CaroleO |
| All times are GMT +1. The time now is 08:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com