Restrict Input In Cells
Mar 28, 2012In cells B7:B229, I only want the user to be able to enter 1,2,3, or 4. How can I force this on them?
View 1 RepliesIn cells B7:B229, I only want the user to be able to enter 1,2,3, or 4. How can I force this on them?
View 1 RepliesI have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies View RelatedI'm trying to write a macro that will not allow a number to be zero. I have a macro that references a cell that if it is input to zero will create a loop that goes on forever. The thing is this cell will never have to be zero, so is there a way that I can write a macro that if this cell is zero, it gets reset to its previous value?
View 4 Replies View RelatedHow can I restrict the user from entering anything other than the date and the proper format required for this input box? I am having a tough time figuring out how to check the input for the right date and format . .
Sub EffDate()
Dim a As Variant
a = InputBox("Enter the effective date" & _
" of the subject's lease using a 2 digit day," & _
" a 2 digit month and a 4 digit year" & _
" (dd/mm/yyyy).", "C/NC Analysis")
Range("U4").Value = a
End Sub
Is there a way to limit say a value to be inputted into a cell say between 1 and 10, and to have a warning message pop up if any other values are keyed into that particular cell?
View 3 Replies View RelatedIs it possible to add a data validation to a cell which restricts the user from inputting more than 2 decimal places?
View 6 Replies View RelatedI want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies View RelatedI am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
I am trying to restrict a cell to only be able to input the letter "i" multiple times, almost like a tally sheet, in other words I want the cell to be restriced to one letter, but allow that letter to be entered multiple times.
View 4 Replies View RelatedI am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedI've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
I need a way to protect my worksheet from any changes but allowing the users to edit certain cells (eg. allowing users to enter data only into columns A-G).
View 2 Replies View RelatedDATA VALIDATION IN MY CODE
Below is my code which looks at Table1 and converts the cell to the appropriate colour when the cell equals the Case
What i would like to do is also have the Case be Data Validation anything else error
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRow As Range
Dim icolor As Integer
Dim ifont As Integer
With Application
.CellDragAndDrop = False
.CutCopyMode = False
End With
it is possible to create a character count in certain cells? I wish to create a worksheet that others can fill in but some cells must have a character count or restriction. It is similar to this - http://javascript.internet.com/forms...r-counter.html
View 3 Replies View RelatedI have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
How to restrict copy paste for certain cells in excel?
For Example : Range ("A1:A20) and Range ("C5:C20")
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I have an input in a cell, let's say: 8:50.
Is it possible to change the color of that cell in red if the text changes in
9:00 for example.
I have designed a form for my wife to use in school to help teach fractions and decimals.
The children will enter the numerator into a cell (say D2) and the denominator into another cell (say D6) and the decimal equivalent is then displayed to 2 decimal places in a third merged cell (say C8 - E8).
What I would like to do is this:
When the worksheet opens, the only cell that I want the children to be able to enter data into is the first cell (ie D2). When they enter a value (say 1 for the numerator) I'd like the return key to move the data entry to the next cell (which is D6). After this, when they have entered another value (say 3 - they are invesigating thirds for example), I'd like the return key to move to the next numerator cell (which is H2), then denominator cell (H6) and so on.
How do I do this? I have never created forms or macros and I suspect that this is how to do this.
For the first set of fractions that they are investigating, thirds, I'd like to limit their data entry in the denominator cells only (D6, H6, L6, P6, etc) to 3 and reject any other number. Later in the worksheet, they will be investigating quarters, fifths and so on.
How do I do this? I don't want then to use any list or drop-down, simply to type a value into the cell and it be accepted (if it is a 3 when investigating thirds) if it is correct or simply ignored if it isn't. Is this possible?
way, especially if it involves anything complicated like macros. Being active on lots of forums it is quite depressing when I see that someone has answered a poster's question but the answer seems to either assume a high level of knowledge or tends to assume that certain steps are taken without actually stating them.
I am working on Excel file with names and I would like to have a possibility to check if all characters from one cell are mentioned in another one.
The background of the problem is that the names are provided in different types (e.g. O.M.Izgala, M.O.Izgala, or only Izgala) so I believe I should first remove any spaces, dots etc. to have clear continuous text.
Finding a formula (if any exist), which can check if all digits from one cell are in the second relevant cell regardless the order of the digits. I would like to show you short example:
name needs to be checked cell to compare expected results
OMIZGALA MIZGALA no (as O is not in the relevant cell)
OMIZGALA MIZGALAO yes (as all digits are mentioned in the relevant cell but in different order)
OMIZGALA MOIZGALA yes (as all digits are mentioned in the relevant cell but in different order)
if it can work in Excel.
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
View 14 Replies View RelatedVBA script I can run on a worksheet that will clear certain cells when I input a fresh value into a separate cell please? For example: By entering a new value into A1, cells B1 and C1:C3 are automatically reset to empty.
View 5 Replies View RelatedI'm using Excel '03 and I need to be able to lock certain cells after input, but only for certain users on my network. Ideally, I (administrator), would still be able to edit the cells in case of a mistake or whatever reason. I want the other users limited to adding information and not editing the existing information in a range. I would need this applied to several columns (B, C, I, and K) if this is possible.
View 9 Replies View RelatedThere are a series of input cells in a protected worksheet and when the user hits an enter, tab or arrow key, the selection cycles through them in order. The cycle also restarts when the mouse clicks one of the cells out of order. After I switch worksheets and return to the first worksheet with the code, first it says there is a "Compile Error: Sub or Function Not Defined" and the code "Call SetKeys" in Sheet1 is hilighted. When I ignore that and stop the debugger, there is a "Run-time error '13' Type mismatch" and the code line "If arrIndex > 0 And arrIndex < UBound(arr) Then" is highlighted.
My question is: why doesn't the code work after I switch worksheets? especially given that it works perfectly before I switch worksheets. (ie the file opens on sheet 1 and everything is perfect, click sheet 2 tab and back to sheet 1 - nothing works) I also have yet to figure out why it seems to be necessary to post the same code in both ThisWorkbook and Sheet1.
Private Sub Workbook_Open()
'This code allows the user to change the unlocked cells, but protects each worksheet
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="mypassword", _
UserInterFaceOnly:=True
Next wSheet............................
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.
View 7 Replies View Relatedi am trying to merge cells based on the input from another sheet. these input will change from month to month and i need the sheet to cancel the merge and merge again based on the new input.
Example.xlsx the file showing what i need it to do.
I have a spreadsheet that has a couple of columns that ask for "Move In Date" and the other "Move Out Date". These dates are used in other calculations so there can be only one or the other in each row. I have users that mistakenly either leave both blank or both popluated. Is there a way to stop them with a message telling them that a date needs to be entered or deleted, maybe with data validation?
View 1 Replies View Relatedi have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet