Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
I have an input box for the user to enter the full name of the month for the
report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
Hard to give complete answer without seeing your code.
Have the first three letters of the input box text converted to uppercase with mytext=Mid(Ucase(text_from_input_box),1,3) Then compare mytext to JAN, FEB .... to return JANUARY, FEBRUARY using maybe a Select Case structure Select Case mytext Case "JAN" : the-month = "JANUARY" Case "FEB" : the-month = "FEBRUARY" Or by using arrays and an index best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Keep It Simple Stupid" wrote in message ... I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
It's hard to say how to fit this code into your existing code without seeing
it or knowing what you want to do when a bad month name is input; however, you should be able to modify the following code to suit your purposes... Sub Test() Dim InputBoxText As String Const Mnths As String = "*January*February*Marcy*April*May*June*July" & _ "*August*September*October*November*December" ' ' Assign user input to the InputBoxText variable here ' If Len(InputBoxText) 2 And InStr(1, Mnths, "*" & _ Replace(Ans, "*", ""), vbTextCompare) 0 Then MsgBox "The input was a valid month!" Else MsgBox "That is not a month name I ever saw." End If End Sub The above code will test the user's input to see if it is at least 3 characters long and also test to see if those characters actually make up the beginning text of any month name. -- Rick (MVP - Excel) "Keep It Simple Stupid" wrote in message ... I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
There is a small problem with the code I posted... I changed a variable name
at the last moment and did not correct all the occurrences of that variable, so the code won't work as posted. In addition, I performed an unneeded correction on that variable (wouldn't affect the functionality of the code, only its efficiency). Here is the corrected code... Sub Test() Dim InputBoxText As String Const Months As String = "*January*February*Marcy*April*May*June*July" & _ "*August*September*October*November*December" ' ' Assign user input to the InputBoxText variable here ' If Len(InputBoxText) 2 And InStr(1, Months, "*" & _ InputBoxText, vbTextCompare) 0 Then MsgBox "The input was a valid month!" Else MsgBox "That is not a month name I ever saw." End If End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It's hard to say how to fit this code into your existing code without seeing it or knowing what you want to do when a bad month name is input; however, you should be able to modify the following code to suit your purposes... Sub Test() Dim InputBoxText As String Const Mnths As String = "*January*February*Marcy*April*May*June*July" & _ "*August*September*October*November*December" ' ' Assign user input to the InputBoxText variable here ' If Len(InputBoxText) 2 And InStr(1, Mnths, "*" & _ Replace(Ans, "*", ""), vbTextCompare) 0 Then MsgBox "The input was a valid month!" Else MsgBox "That is not a month name I ever saw." End If End Sub The above code will test the user's input to see if it is at least 3 characters long and also test to see if those characters actually make up the beginning text of any month name. -- Rick (MVP - Excel) "Keep It Simple Stupid" wrote in message ... I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
Incases like that, I usually use a listbox with the months spelled out and
set to single select. As soon as they click on one, it closes the the listbox and executes the underlying code which can set up like: RangeOnReport = UCase(ListBox1.Value) 'If you need upper case That way you don't have to worry about mispelling or case. "Keep It Simple Stupid" wrote: I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
I feel dumb, but I'm going to ask you all to "slow it down" for me.
This is the code before and after I want the user to enter the month. 'THERE IS MORE CODE BEFORE THIS ActiveCell.FormulaR1C1 = "DEPARTMENT" Range("L2").Select 'THIS IS WHERE I WANT THE USER TO SELET THE MONTH Range("a1").Select Range("a1").Value = InputBox("IN ALL CAPS: Enter the full name of the month") Sheets("Sheet2").Select Sheets("Sheet2").Copy BEFO=Workbooks("MONTHTRACKER.xls").Sheets(1) Sheets("Sheet2").Select Range("a1").Copy Sheets("Sheet1").Select Range("a1").PasteSpecial I tried looking into ListBoxes but I have never used those before and I can't seem to find the "basics". Do you think that would be my best option (since my months aren't going to change"? Whatever month is selected, I just need that value to be entered into cell A1. (It's okay to growl in frustration at my stupidity... I've got a bit of a "grrr" going on for frustrating myself). Thanks everyone. "JLGWhiz" wrote: Incases like that, I usually use a listbox with the months spelled out and set to single select. As soon as they click on one, it closes the the listbox and executes the underlying code which can set up like: RangeOnReport = UCase(ListBox1.Value) 'If you need upper case That way you don't have to worry about mispelling or case. "Keep It Simple Stupid" wrote: I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Sensitive Input Box
I think I got it now. The list box was a good idea.
Thanks everyone!!! "Keep It Simple Stupid" wrote: I feel dumb, but I'm going to ask you all to "slow it down" for me. This is the code before and after I want the user to enter the month. 'THERE IS MORE CODE BEFORE THIS ActiveCell.FormulaR1C1 = "DEPARTMENT" Range("L2").Select 'THIS IS WHERE I WANT THE USER TO SELET THE MONTH Range("a1").Select Range("a1").Value = InputBox("IN ALL CAPS: Enter the full name of the month") Sheets("Sheet2").Select Sheets("Sheet2").Copy BEFO=Workbooks("MONTHTRACKER.xls").Sheets(1) Sheets("Sheet2").Select Range("a1").Copy Sheets("Sheet1").Select Range("a1").PasteSpecial I tried looking into ListBoxes but I have never used those before and I can't seem to find the "basics". Do you think that would be my best option (since my months aren't going to change"? Whatever month is selected, I just need that value to be entered into cell A1. (It's okay to growl in frustration at my stupidity... I've got a bit of a "grrr" going on for frustrating myself). Thanks everyone. "JLGWhiz" wrote: Incases like that, I usually use a listbox with the months spelled out and set to single select. As soon as they click on one, it closes the the listbox and executes the underlying code which can set up like: RangeOnReport = UCase(ListBox1.Value) 'If you need upper case That way you don't have to worry about mispelling or case. "Keep It Simple Stupid" wrote: I have an input box for the user to enter the full name of the month for the report. Then there is a whole slew of code that follows. The value is then entered into the sheet, and I have formulas that look for this value. Unfortunately, if the user does not enter the entire name of the month and in all caps, it doesn't work. How can I make it so I have some kind of error-checker. For example, if the users enters january, January, or JANYOUARIE then the code will stop, but if the user enters JANUARY, FEBRUARY, MARCH, ... etc, then the code continues. I cant figure out the if/then/else |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
Case sensitive filter | Excel Worksheet Functions | |||
Case Sensitive w/ IF | Excel Worksheet Functions | |||
.Name case sensitive | Excel Programming | |||
Case sensitive vlookup | Excel Discussion (Misc queries) |