 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2006Check user input
I need to check data in a range of 100 cells. The value can be blank or single character only from these upper case letters (A, B, C, N, P, R, S, T, X, Y, Z). If the data is not correct prompt user to fix it. Is there better way to validate the cells that multiple if ..then .. else? Data>Validation Choose List enter A,B,C,N,P,R,S,T,X,Y,Z (without any quotes) in Source Box Click OK Et voila! HTH -- AP > I need to check data in a range of 100 cells. The value can be blank or > single character only from these upper case letters (A, B, C, N, P, R, S, T, > X, Y, Z). If the data is not correct prompt user to fix it. Is there better > way to validate the cells that multiple if ..then .. else? Set up data validation (Data->Validation) and in the settings tab, set Allow drop-down to a List. You need to set up a list of valid values somewhere in your worksheet. Unfortunately data validation is not case sensitive (!) so in the past I have used the worksheet change event to replace the value entered to uppercase. You will need to change the references to those cells you wish to change and test e.g. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 100 Then Target.Value = UCase(Target.Value) End If End Sub  SignatureCheers Nigel >I need to check data in a range of 100 cells. The value can be blank or > single character only from these upper case letters (A, B, C, N, P, R, S, > T, > X, Y, Z). If the data is not correct prompt user to fix it. Is there > better > way to validate the cells that multiple if ..then .. else? Ardus solution is even better - I learn something everyday! SignatureCheers Nigel > Set up data validation (Data->Validation) and in the settings tab, set > Allow drop-down to a List. You need to set up a list of valid values [quoted text clipped - 15 lines] >> better >> way to validate the cells that multiple if ..then .. else? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.