Limit/Restrict Cell Input

Jun 5, 2007

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


Restrict Cells Depending On Input In Another Cell?

Jan 28, 2013

I 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 Related

Restrict User To Input Certain Data Without Referring To Another Cell?

Nov 27, 2013

I 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 Related

Excel VBA - Restrict Cell To Input Manual Entry

Apr 3, 2012

I 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...

View 5 Replies View Related

Excel 2007 :: Restrict Cell Input Based On Another Cell Value

Jan 22, 2013

I 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‎

View 4 Replies View Related

Restrict Cell Input Based On Text Of Another Cell

Jan 29, 2008

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 Related

Restrict Cell Input Based On Another Cell Value

Jul 27, 2009

I'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.

View 14 Replies View Related

Data Validation (restrict A Cell To Only Be Able To Input The Letter "i" Multiple Times)

Nov 5, 2008

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 Related

Restrict Input Of Zero

Jun 19, 2012

I'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 Related

Restrict Input In Cells

Mar 28, 2012

In 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 Replies View Related

Restrict Data In Input Box

Oct 31, 2007

How 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

View 9 Replies View Related

Restrict Data Input To Two Decimal Places

Dec 1, 2012

Is 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 Related

Input Parameter To Set A Limit

Apr 26, 2008

How do I adjust the VB code below so when the user clicks the command button they are prompted to set a limit.
i.e the code only runs til it hits the limit and stop (instead of running until it there are no more x in the columns)

Row 2, Col 9 stores the column numbers (listed as 1, 2,.....100) so thought this could be used in some way.

1. how do i set up the code so a user can enter a parameter value e.g 50 (which is the limit but also includes the values in column number "50")

2. how do i adjust the code so it take the parameter value and stop when it reaches column number "51"...

View 9 Replies View Related

Limit Entries Allowed On Input Box?

Aug 22, 2013

I have a code that I need to limit the entries allowed on an input box. I have got it limited to a single value ("S1") but I need to add a second ("S2"). If "S1" or "S2" is not enter I need the input box to reappear until the right value is entered.

My code:

dbl = InputBox("Enter the S1 or S2 for the rack location you are using", "Rack", "S1", 5, 275)
If dbl "S1" Then GoTo Repeater
End If

View 2 Replies View Related

Limit Input To Specific Date

May 20, 2014

Any way to limit the date a person can input to the fourth business day of the following month. So limiting the date the 06/05/2014 for instance. Would there be a way to keep this updating so manual updating would be unnecessary?

View 9 Replies View Related

Limit Scroll Area - Code Activates Only On Mouse Or Keyboard Input

Apr 21, 2007

I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.

How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
End Sub

I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?

View 2 Replies View Related

Restrict Cell To Alphabet Only

Nov 28, 2009

for instance if i type in a particular cell any number 1, 0, 53 .. excel must not allow me to do this...

but there are certain value that have both number and alphatbets eg. 001 michael jordan ... cell should allow such values.

View 9 Replies View Related

Restrict Cell Value Macro

Dec 19, 2006

I have a range B1 to B4 for user to input value. Worksheet Change Event

However, the range B1 to B4 should not accept any value if the cell on the left hand side is empty.

If A1 is empty, B1 should not accept any value.
If A2 is empty, B2 should not accept any value.
If A3 is empty, B3 should not accept any value.

Below code checks for condition in A1 only.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B4")) Is Nothing Then

If IsEmpty(Range("A1")) Then
MsgBox "Please input something under column A."
With Application
.EnableEvents = False
.EnableEvents = True
End With
Target.Offset(0, -1).Select
Exit Sub
End If

End If

End Sub

Eg, If I enter something in A1, B2 would accept a value which is not what I hope to achieve. It should be checking for cell in A2 whther it is empty.

Would be grateful for some advice on how to modify the code.

I attached a sample file for reference

View 7 Replies View Related

Input In Multiple Cells To Generate Input Into Single Cell?

Feb 22, 2014

I 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

View 4 Replies View Related

Input Box To Create And Input Info Into A Formula Which Then Populates A Cell With A Value

Jul 24, 2014

I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:

Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)

Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:


So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.

View 9 Replies View Related

Restrict Cell Entry To Numbers Based On Another Cell

Mar 13, 2008

I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:

Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.

Can I do that with data validation? I can't get any if thens to work in it.

View 3 Replies View Related

Cell Format - Restrict Certain Characters

May 14, 2014

I need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.

View 14 Replies View Related

How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'

Jan 28, 2009

I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.

What I am looking for

1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.

2) I would like this validation/formula to be applied to all the cells in a particular column.

3) Nice-to-have.

If the user enters a lower case letter, then I would like to convert to upper case.

View 8 Replies View Related

Restrict Number Of Characters In A Cell

Sep 17, 2012

I have a cell (A1) with a number in it. The number has 30 digits. I need a formula for (B1) that will enter the same number as A1 but restrict the number to 28 digits. Basically I need it to cut off the last 2 digits.

View 3 Replies View Related

Restrict Cell To Numeric Values Only?

Jun 6, 2014

I have cell E15 in Sheet1 which will take an 8 digit Personnel Number, which can have leading 0's. I want to prevent a user from entering any characters other than 0123456789. I have worked out that I need to use the Worksheet_Change event and I need to use the function IsNumeric.

No matter what I do I can't trigger the Worksheet_Change event!

View 4 Replies View Related

Restrict Cell Values Macro

Nov 29, 2006

a value in a cell depending on some condition.

In the attached sample file, when A2=Fruit, A9=Domestic, A10=Orange and A11=1, B2 would not accept a value greater than 200 and there is a pop up message.

This control check works fine if a user has input "Fruit" in A2 first and input amount in B2 later.

However, if the user has input value 250 in B2 first and input "Fruit" in A2 later, 250 would be accepted by B2 and there is no pop up message.

View 9 Replies View Related

Restrict Calculate Event By Cell

Jan 11, 2007

I am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited....

View 9 Replies View Related

Transferring Of Input From Input Cell To Output Cells?

Jun 8, 2014

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.

View 3 Replies View Related

Restrict Time Entries Based On Another Cell

Jan 23, 2008

The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.

View 2 Replies View Related

Restrict Cell Entry Based On Another Cell

Sep 18, 2007

I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.

So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved