Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with VBA in Excel 2007
I have the following VBA code which somebody helped me with earlier and I can
get it to work. However, when I save the spreadsheet and then go back into it, it doesn't work again. The VBA code is so that I can enter 1423 and it will change to 14:23 when working, if it isn't it goes to 34152:00. Any ideas where I have gone wrong? Ideally I would like the VBA code over the workbook and not just the worksheet, but I don't know how to do this. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C:D,F:G,J:J")) 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 Thanks in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
Import an MS Money 2007 file into MS Excel 2007 worksheet | Excel Discussion (Misc queries) | |||
Excel 2007 Macro Help (Excel 2003 not working in 2007) | Excel Discussion (Misc queries) | |||
Paste EXCEL 2007 sheet into WORD 2007 - objects move around | Excel Discussion (Misc queries) | |||
Chart Selection - Excel 2007 copy to Powerpoint 2007 | Charts and Charting in Excel |