|
Format Cell As You Type
I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option. With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all. I just wanted to enter the figures 1930 and let the cell format itself to 19:30 I was informed by VBA Gurus to use the following VBA code: Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -------------------- and paste it in the workbook section (I have 25 worksheets in my workbook and more to come), so that I don't have to paste the code in all the individual worksheets. I believe the code needs some tweaking if it has to be placed in the workbook section. The problem is that it does not work. Can somebody help me in this regard. FYI, I know nothing about VB coding. Thanks. -- cooper ------------------------------------------------------------------------ cooper's Profile: http://www.excelforum.com/member.php...o&userid=33171 View this thread: http://www.excelforum.com/showthread...hreadid=529933 |
Format Cell As You Type
Hi Cooper,
Try changing: Private Sub Worksheet_Change(ByVal Target As Excel.Range) to Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) --- Regards, Norman "cooper" wrote in message ... I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option. With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all. I just wanted to enter the figures 1930 and let the cell format itself to 19:30 I was informed by VBA Gurus to use the following VBA code: Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -------------------- and paste it in the workbook section (I have 25 worksheets in my workbook and more to come), so that I don't have to paste the code in all the individual worksheets. I believe the code needs some tweaking if it has to be placed in the workbook section. The problem is that it does not work. Can somebody help me in this regard. FYI, I know nothing about VB coding. Thanks. -- cooper ------------------------------------------------------------------------ cooper's Profile: http://www.excelforum.com/member.php...o&userid=33171 View this thread: http://www.excelforum.com/showthread...hreadid=529933 |
Format Cell As You Type
Hi Cooper,
Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) This should be a single line, broken by the underscore (_) line continuation character: Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) --- Regards, Norman "Norman Jones" wrote in message ... Hi Cooper, Try changing: Private Sub Worksheet_Change(ByVal Target As Excel.Range) to Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) --- Regards, Norman "cooper" wrote in message ... I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option. With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all. I just wanted to enter the figures 1930 and let the cell format itself to 19:30 I was informed by VBA Gurus to use the following VBA code: Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -------------------- and paste it in the workbook section (I have 25 worksheets in my workbook and more to come), so that I don't have to paste the code in all the individual worksheets. I believe the code needs some tweaking if it has to be placed in the workbook section. The problem is that it does not work. Can somebody help me in this regard. FYI, I know nothing about VB coding. Thanks. -- cooper ------------------------------------------------------------------------ cooper's Profile: http://www.excelforum.com/member.php...o&userid=33171 View this thread: http://www.excelforum.com/showthread...hreadid=529933 |
Format Cell As You Type
Cooper,
Forgive me for being too simplistic here, but if you just want to type in 1930 and have it show up in the cell as 19:30, why don't you just custom format as ##":"## and you will accomplish the same thing. Mike "cooper" wrote: I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option. With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all. I just wanted to enter the figures 1930 and let the cell format itself to 19:30 I was informed by VBA Gurus to use the following VBA code: Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -------------------- and paste it in the workbook section (I have 25 worksheets in my workbook and more to come), so that I don't have to paste the code in all the individual worksheets. I believe the code needs some tweaking if it has to be placed in the workbook section. The problem is that it does not work. Can somebody help me in this regard. FYI, I know nothing about VB coding. Thanks. -- cooper ------------------------------------------------------------------------ cooper's Profile: http://www.excelforum.com/member.php...o&userid=33171 View this thread: http://www.excelforum.com/showthread...hreadid=529933 |
Format Cell As You Type
If I use Norman's method, I get a compilation error. So Norman, could you please amend the code and paste it using the 'code' tag. Thanks. Mike, your suggestion is very good, but then I can't do any time/date calculations using those cells. I will use it as a last resort. -- cooper ------------------------------------------------------------------------ cooper's Profile: http://www.excelforum.com/member.php...o&userid=33171 View this thread: http://www.excelforum.com/showthread...hreadid=529933 |
| All times are GMT +1. The time now is 04:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com