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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
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
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
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
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
Jun 27, 2006
I have an Excel spreadsheet that I use to schedule employees. I use this to assign sections to various employees. I was wondering if there is a formula that will restrict me entering the same "section" (or data) on the spreadsheet twice. The page is rather large and sometimes I can enter the same section without knowing it until I manually check it.
View 5 Replies
View Related
May 19, 2007
A Macro needed which will be assigned to a button,say “Check” and when the same will be clicked all the sheets will be say protected an users cannot modify any data. Another button will be there which will be assigned macro & when clicked will ask for a password to be known by only some administratives say departmental heads only and they can modify the data.Users after clicking the “Check” button,cannot modify the data.
View 9 Replies
View Related
Sep 4, 2009
I have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.
Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it? I've attached a simplified example worksheet.
View 5 Replies
View Related
May 14, 2014
How do you restrict a user to only be able to enter up to 2 decimal places in a cell without it automatically rounding for them?
View 6 Replies
View Related
Oct 10, 2013
I am looking at a way to create a dsitributable excel spreadsheet (.xlsx), which contains non-sensitive, yet restrictied organisational information segmented by regional department for NCSA, EMEA for APAC respectively.
By design, all data has been collected in a datatable on a password protected "data" sheet - and - presented on an "executive summary" sheet. T
The "executive summary" sheet contains a privottable with select "multiple options" to allow for aggregated dataviews on executive level. However, on a regional department level, the users must only see their own regions, hence the filtering mechanims on the pivottable should be restricted.
Currently, the restrictions on the pivot tables are made in VBA, using the following script. However, it still allows people to change the "multiple options" filter in the pivot. How can I restrcit this option
Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = False
.EnableDrilldown = True
.EnableFieldList = True
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .PageFields
[code].....
As a sidenote; the above design requires that a spreadsheet is compiled for each region each month. Ideally, the ability of filtering, could be password restricted - or - filteren. So only one selection would be presented for the NCSA, EMEA and APAC respectively, while Executives could view across one-or-many regions? However, as Pivot tables are used by design, I cannot see how this could efficiently be achieved?
View 1 Replies
View Related
Jan 11, 2007
Can I Restrict Excel Data Form only for appending new record. (No deletion allowed) and also Can I save Data Form in the File? Is there any way, whenever we open the file Data Form should popup?
View 9 Replies
View Related
Jul 22, 2007
I know there must be some way to write vba to force users of an excel sheet to choose from a drop-down list for that particular cell. I am having trouble with a spreadsheet that is used widely through the company with people doing copy/paste or just typing in entries.
View 2 Replies
View Related
Mar 12, 2009
I want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.
View 3 Replies
View Related
Oct 2, 2012
I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
View 3 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
Aug 6, 2013
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
View 3 Replies
View Related
Oct 25, 2012
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
View 3 Replies
View Related
Aug 31, 2013
I have a two sheet, in Sheet1 i have a data, and in sheet 2 i have a count of location wise data in it, which is from sheet1.. And beside it u will see a button "View Detail". When you click on that button it will pop up a userform which have listbox and 2 button.. Now in listbox i get a filtered value of column C of sheet1 (For eg. Mumbai, Pune, Chennai).. When i click on the value (Mumbai) in Listbox and then click on View Location Details. It should give me a details of mumbai based candidate, like name, pod no, location in the range of (H5:J100) of sheet2..
I wrote the code for it but its not giving me the correct data..
View 1 Replies
View Related
Mar 26, 2014
Creating a working userform that takes data input from the user and puts it onto a spread sheet.
That data is then used to calculate a result (via the spreadsheet), and I want that result to show on the userform when the data is submitted (if that makes sense?)
View 1 Replies
View Related
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
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:
=(NUM1*(NUM2*$H10))/$H10
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
Jan 20, 2007
I'm trying to convert this code so it transfers the data without the input box being needed.
Dim response As Long
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As Variant, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False
Set wSht = Worksheets("Transfer Sheet")
strToFind = Application.InputBox("Enter Your Initials")
If strToFind = False Or strToFind = "" Then Exit Sub
With ActiveSheet.Range("H2:H5000")
Set rngC = .Find(what:=strToFind, LookAt:=xlWhole)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Worksheets("Transfer Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy wSht.Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With
View 12 Replies
View Related