Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problems in XP
The following code works correctly in Excel 2000, but not
in XP. The code allows a date to be typed without the slashes, so 092104 returns 09/21/2004. Typing the same date in XP gives me 03/02/2152. Is there a problem with compatibility between 2000 and XP? I'm having problems with several macros not executing properly in XP. Sub Worksheet_Change(ByVal Target As Excel.Range) ' Macro add slashes for dates in Range E21:E1000, ' So date entry can be typed as 122503 but shows as 12/25/03, ' The date is formatted correctly so it can be used in a calculation. Dim DateStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("e24:e1000")) 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(.Formula) Case 4 ' e.g., 9298 = 2-Sep-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2) Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right (.Formula, 2) Case 6 ' e.g., 090298 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right (.Formula, 2) Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right (.Formula, 4) Case 8 ' e.g., 09021998 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right (.Formula, 4) Case Else Err.Raise 0 End Select .Formula = DateValue(DateStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid date." Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problems in XP
Hi
I'm on XP with Excel Office 2000. I ran this :- Sub Macro10() Dim DateStr As String Dim InDate As String InDate = "092104" DateStr = Left(InDate, 2) & "/" & _ Mid(InDate, 3, 2) & "/" & Right(InDate, 2) MsgBox DateValue(DateStr) End Sub And with English(UK Settings) got 21/09/2004. I've tried English(US) settings and then I get 9/21/2004. My guess is that if you look in Start-settings-control Panel- Date time,Lang Regional Options-Change Format Numbs and Dates you'll find some weird regional date setting. PS I try to help but I've posted 3 questions with no answers ... Can you have a look at my latest one I posted it today at 8:50 and see if you think it'll be OK - Thanks -----Original Message----- The following code works correctly in Excel 2000, but not in XP. The code allows a date to be typed without the slashes, so 092104 returns 09/21/2004. Typing the same date in XP gives me 03/02/2152. Is there a problem with compatibility between 2000 and XP? I'm having problems with several macros not executing properly in XP. Sub Worksheet_Change(ByVal Target As Excel.Range) ' Macro add slashes for dates in Range E21:E1000, ' So date entry can be typed as 122503 but shows as 12/25/03, ' The date is formatted correctly so it can be used in a calculation. Dim DateStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("e24:e1000")) 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(.Formula) Case 4 ' e.g., 9298 = 2-Sep-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2) Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right (.Formula, 2) Case 6 ' e.g., 090298 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right (.Formula, 2) Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec- 1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right (.Formula, 4) Case 8 ' e.g., 09021998 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right (.Formula, 4) Case Else Err.Raise 0 End Select .Formula = DateValue(DateStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid date." Application.EnableEvents = True End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro problems | Excel Discussion (Misc queries) | |||
Macro Problems | Excel Discussion (Misc queries) | |||
Problems with macro | Excel Discussion (Misc queries) | |||
MACRO PROBLEMS | Excel Programming | |||
macro problems | Excel Programming |