VBA . validating user entered data (via inputbox) and error handler

**rain4u** · 06-30-2011, 05:27 PM

Hi all
I have this code where the first inputbox asks the user to insert year e.g. 2011 and second inputbox asks to enter week number e.g. 52.
How can validate that user indeed selected the a valid year number lets say number between 2000 and 2100 (yes I believe no one will use excel in its current form after 2100

), and with week number it can be number between 1 and 52. Error handler should exit the sub and prompt with a message. Like "not a valid year" or "not valid week number"

Code itself looks following:

Sub charts_by_wk_number()
Sheets("Chart_data").Visible = True
wkYear = InputBox("Please insert the current year." & Chr(13) & Chr(13) & "Type in exactly 4 numbers e.g. 2011", "Insert current year", "2011")
wkNo = Application.InputBox("Please type in the week number e.g. number between 1 to 52")
Sheets("Chart_data").Select
Range("chart_year").Value = wkYear
Range("chart_week_number").Value = wkNo
Range("A1").Select
ActiveCell.FormulaR1C1 = _
    "=DATE(chart_year,1,chart_week_number*7-2)-WEEKDAY(DATE(chart_year,1,3))"
Range("chart_date_end").Select
    ActiveCell.FormulaR1C1 = "=chart_date_start+6"
Range("chart_date_info_concatenation").Select
ActiveCell.FormulaR1C1 = _
    "=IF(chart_week_number="""",CONCATENATE("" ("",TEXT(chart_date_start,""dd/mm/yy""),"" - "",TEXT(chart_date_end,""dd/mm/yy""),"")""),CONCATENATE("" (week "",chart_week_number,""; "",TEXT(chart_date_start,""dd/mm/yy""),"" - "",TEXT(chart_date_end,""dd/mm/yy""),"")""))"
Range("A1:E1").Value = Range("A1:E1").Value
Call continue_code
End Sub

If anyone knows how to do it, I would appreciate the help.
Cheers
Rain

**Mordred** · 06-30-2011, 05:54 PM

Hi rain, try to incorporate this into your procedure

Sub IBoxTest()
    Dim Yr As Long, Wk As Long
    Yr = InputBox("Enter Year")
    If Yr < 2000 Or Yr > 2100 Then
        MsgBox "You have entered an invalid year!"
        Exit Sub
    ElseIf IsError(Yr) Then Exit Sub
    End If
    Wk = InputBox("Enter Week")
    If Wk < 1 Or Wk > 52 Then
        MsgBox "You have entered an invalid week"
        Exit Sub
    Else: If IsError(Wk) Then Exit Sub
    End If
End Sub

I would but I am now leaving the building.

Thread: VBA . validating user entered data (via inputbox) and error handler

Thread Tools

Display

VBA . validating user entered data (via inputbox) and error handler

Re: VBA . validating user entered data (via inputbox) and error handler

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions