help with error handling

Corbana

New Member
Joined
Nov 9, 2005
Messages
23
Even tho I have put in the message box enter the date in the format dd/mm/yyyy some people cannot do that, so can anyone help me with some error handling, I have never done any error handling before so if anyone can point me at a good source to learn error handling that would be helpful too.

The code basically imports a text file that has all the data in it alread and then takes the user to a view of it made to look pretty!

Code:
Sub DailyStats()

 Dim Dateinput As Date
        
    Dateinput = Format(Dateinput, "dd/mm/yyyy")
        
        Dateinput = InputBox("Enter date (dd/mm/yyyy)") 

        Range("IV2").Select
        Selection.ClearContents
        ActiveCell = Dateinput
        Range("A1").Select
        
        'Get Day
        
            Sheets("Day Import").Select
                Cells.Select
                Selection.ClearContents
                Selection.QueryTable.Delete
            Range("A1").Select
            
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\team\" & "D" & Sheets("Menu").Range("IV3").Value & ".txt", _
        Destination:=Range("A1"))
        .Name = "Day import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
    Sheets("Day view").Select
    Range("B38").Select
    ActiveCell.FormulaR1C1 = "These are the daily stats for " & Dateinput
    Range("A1").Select
End Sub
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is an example of validating a date in an InputBox:

Code:
Sub Test()
    Dim Ans As Variant
    Dim DateInput As Date
    Do
        Ans = InputBox("Enter date (dd/mm/yyyy)")
'       User pressed Cancel
        If Ans = "" Then Exit Sub
        If Ans Like "##/##/####" Then
            DateInput = DateValue(Ans)
            If Day(DateInput) = Val(Left(Ans, 2)) _
                And Month(DateInput) = Val(Mid(Ans, 4, 2)) _
                And Year(DateInput) = Val(Right(Ans, 4)) Then
                Exit Do
            Else
                MsgBox "Please enter a valid date in the format dd/mm/yyyy"
            End If
        Else
            MsgBox "Please enter a valid date in the format dd/mm/yyyy"
        End If
    Loop
'   Replace next line with your code
    MsgBox DateInput
End Sub

Of course you can't check day and month when they are ambiguous, eg 01/12/2005 could be 1 December or 12 January depending on the user's regional settings.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,318
Messages
5,571,527
Members
412,401
Latest member
allenayres83
Top