Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting input boxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting input boxes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting input boxes
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting input boxes
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 b 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 = -- cereldin ----------------------------------------------------------------------- cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206 View this thread: http://www.excelforum.com/showthread.php?threadid=55260 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input boxes | Excel Discussion (Misc queries) | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
Input Boxes | Excel Programming | |||
Can anyone help with input boxes? | Excel Programming | |||
Mask input in input boxes? | Excel Programming |