formatting input boxes

[cereldine]

Hi, i have a piece of code that is basically a means for users to copy raw data out of external spreadsheets into another spreadsheet for analysis and manipulation.

The raw data has a column on left that declares year and quarter in format of 2005Q4. The user is faced with an inputbox that prompts them for the date to copy data until. If they make a mistake e.g 200Q4, 200404 2005Q5 then the code is going to break. I would therfore like some validation on the input box that verifys that we have a date in the correct format that can be searched for.

Something like

sYear = Application.InputBox("Which Year/Quarter should data be returned until? (In the format of 2006Q4)?", "Select Date", "2005Q4")

if syear = integer,integer,integer,integer,char,integer then

elseif syear = "" then
""No value"
end sub
end if

if left(syear,4) > 1990 and < 2010 then
else
exit sub
end if

if right(syear,1) in <> 1 or 2 or 3 or 4
exit sub
end if


Is this along the right lines or am i taking a very long way round?

[[email protected]]

Wouldn't you be safer having a user form, with two textboxes - one for
the year, one for the quarter - both of these could be validated
independantly, and then the final result built from the two inputs.

cereldine wrote:
> Hi, i have a piece of code that is basically a means for users to copy
> raw data out of external spreadsheets into another spreadsheet for
> analysis and manipulation.
>
> The raw data has a column on left that declares year and quarter in
> format of 2005Q4. The user is faced with an inputbox that prompts them
> for the date to copy data until. If they make a mistake e.g 200Q4,
> 200404 2005Q5 then the code is going to break. I would therfore like
> some validation on the input box that verifys that we have a date in
> the correct format that can be searched for.
>
> Something like
>
> sYear = Application.InputBox("Which Year/Quarter should data be
> returned until? (In the format of 2006Q4)?", "Select Date", "2005Q4")
>
> if syear = integer,integer,integer,integer,char,integer then
>
> elseif syear = "" then
> ""No value"
> end sub
> end if
>
> if left(syear,4) > 1990 and < 2010 then
> else
> exit sub
> end if
>
> if right(syear,1) in <> 1 or 2 or 3 or 4
> exit sub
> end if
>
>
> Is this along the right lines or am i taking a very long way round?
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=552603

[DAV135]

Split the checking into 4 section to be certain...

'Check the number of characters....
if len(sYear) <> 6 then
'Error Message
end if

you also need to validate each section (ie the year and the quarter),
to do this is also simple...

'Check that the first 4 characters are numbers which will represent the
year.
if isnumeric(left(sYear,4)) = false then
'Error Message
end if

'Check that the 5th character is the letter "Q".
if mid(sYear,5,1)) <> "Q" then
'Error Message
end if

'Check that the final character is a number which represents the
quarter.
if isnumeric(right(sYear,1)) = false then
'Error Message
end if

Obviously you can just put them all into one if statement using the OR
command, but thought it would be easier to explain that way.

[cereldine]

Thanks for your input dav135

I have used code in following way and it works exactly as i want


Do '' don't continue until correct search criteria is available
sYear = Application.InputBox("Which Year/Quarter should data be returned until? (In the format of 2006Q4)?", "Select Date", "2005Q4")

If IsNumeric(Left(sYear, 4)) = False Or Len(sYear) <> 6 Or Mid(sYear, 5, 1) <> "Q" Or IsNumeric(Right(sYear, 1)) = False Or Left(sYear, 4) < 1990 Or Right(sYear, 1) > 4 Then
MsgBox "Bad search value, try again!"
i = 0
Else
i = 1
End If
Loop Until i = 1