Required Input Based On Validation
Jun 13, 2007
I have a form that when users hit submit, the data they entered gets transfered to Access (thanks to our MVP SydneyGeek for providing me with a macro!) I want to be able to limit or control the users who sends data, by making sure they enter every information that is needed.
I have a column that has a list using Validation. How do I make certain columns required based on what they choose on the list.. if they don't fill in those columns, I don't want them to be able to click Submit, showing an error that would say "Missing Required Fields".
View 9 Replies
ADVERTISEMENT
May 24, 2012
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
View 9 Replies
View Related
Jun 12, 2013
I have numerous text files named HOSTNAME.txt with the below information imbedded.
HOSTNAME.txt
Code:
Instance shutdown complete
Thu May 02 19:36:01 2013
Instance shutdown complete
Thu May 09 20:19:58 2013
Completed: ALTER DATABASE OPEN
Thu May 09 20:25:26 2013
Instance shutdown complete
[code].....
In the file, the date always follows the action. The sequence as you can see is not always complete which indicates a issue.
Each File will represent a row in my spreadsheet and the columns will then have the headers STOP,START,STOP,START,etcSystem
Stop
Start
Stop
Start
Stop
Start
Stop
Start
[code].....
I would like to use the captured data later to calculate downtime, everything can be changed except for the structure of the text file.
View 1 Replies
View Related
Jul 10, 2012
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
View 4 Replies
View Related
Nov 23, 2009
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
View 8 Replies
View Related
Jul 9, 2009
I'm trying to get input from a user regarding parameters for loan calculations. I'm still very new at VBA so some of this will seem very silly, but I've been trying for hours to get this to work right and I keep fixing problems and creating more simultaneously. I've come to the conclusion that I have a fundamental misunderstanding of how this works.
This one actually works fine, but I am including it just in case I've done something less than perfect and someone can correct it.
View 9 Replies
View Related
Mar 1, 2014
I have , within a workbook multiple worksheets which have multiple cells with a certain DATA VALIDATION MESSAGE. I am trying to write a macro to sum up all those cells which have a common DATA VALIDATION MESSAGE.
View 2 Replies
View Related
Apr 2, 2009
is it posible to control the positioning of the pop-up data validation input messsage box via vba? As often it obscures the view of critical cells below.
You can manually click and drag it to a desired position manually. Mana users are not aware of that. It would be desirable to reposition it elsewhere rogramatically.
View 2 Replies
View Related
Sep 22, 2009
Below, both inputbox cases appear to work fine. If you enter an incorrect input on your first attempt it will offer you the option to return you to an inputbox.
View 3 Replies
View Related
Jan 24, 2009
I would like to use Validation to verify that the date entered in cell "B3" is a Sunday. Or do I have to go at this some other way?
View 4 Replies
View Related
Jan 9, 2008
I am using data validation input messages for data entry tips/definitions. The data entry cells are grouped in small tables (about 5 rows X 6 columns) so when the user clicks on a cell, the data validation input message box pops up next to the active cell. This covers the adjacent cells which need to be completed as well.
If I drag the message box over to right of the input table, the messages for the other cells stay in the new location as well. However, Excel doesn't remember this location the next time the workbook is opened.
Is there a way to set the location of the message box or some other way to achieve the same objective of having text boxes pop up in a defined location when a cell is activated?
View 9 Replies
View Related
Sep 2, 2008
I currently have 6 cells that Have a Validation Criteria of a "List". I'm using the Input Message on the Data Validation box. I need to have this Input Message Pinned to a specific area on my worksheet. Each time that I exit and reopen the file the Input Message returns to the Area around the dropdown box. The input message is different for each of the 6 cells.
View 9 Replies
View Related
Sep 13, 2009
Cells A1 to A10 contain names of different cars, what i would like to do in VBA is when i select any individual cell to the right of these the name of the car appears as a message (as if i had gone DATA -> VALIDATION -> INPUT MESSAGE -> CARS NAME).
Tried positioning a Userform next to the active cell but when i scroll down the page and select a cell the userform is to far down the screen to be seen.
View 9 Replies
View Related
Jan 8, 2009
I have to input data into "ANYDATA" field. Since I need this input to be 100% accurate, I would like my operator to input it TWICE. E.g. 1st input - Obama 2nd input- Obama. Only if BOTH the entries match will the data move into ANYDATA field, else the field becomes blank once again, and the operator has to re-key twice again. In example above, if he had input 2nd time as Obame, then he will have to repeat the process. Can anyone pls help me create suitable formula/ field settings...Thx! Preferably, I would NOT like to create two dummy fields "ANYDATA1" and "ANYDATA2" with corresponding entry in "ANYDATA" if both match.
p.s. Need this to be done using MS-Excel only.
View 10 Replies
View Related
Oct 5, 2009
Ive been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies
View Related
Oct 13, 2008
Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
View 4 Replies
View Related
May 5, 2014
What would be the correct syntax for pulling information to the input message box from another sheet?
Code:
Sub test_data_validation()
'
' test_data_validation Macro
'
Dim nrows As Integer
Dim i As Integer
Sheets("sheet1").Activate
[Code] .........
View 2 Replies
View Related
Apr 21, 2009
I have multiple data validations with input messages. For the new user, this is handy, but for the person that does this a lot, they get in the way. When I open the sheet, I can move one message to the side of the page and the rest of them come up at the same spot but when I save, exit, and reopen, the message comes up beside the cell and I would like it to always stay to the side of the page.
View 9 Replies
View Related
Jan 13, 2010
I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.
I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.
Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.
View 2 Replies
View Related
Dec 1, 2011
I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.
I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?
I have Excel 2007. I saved in both xlsx and xlsm formats
View 5 Replies
View Related
Nov 7, 2012
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
View 1 Replies
View Related
Nov 11, 2008
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
View 2 Replies
View Related
May 11, 2009
I need to find out Top 5/Top 10 or as required based on two columns. I have clearly explained the way it is required in the attached sheet.
View 9 Replies
View Related
Dec 9, 2008
What I am trying to accomplish....
If the input changes in D5, K11 will reset to $0.00..
Example. If cell K11 is set to $300.00, and the corresponding input in D5 is '123', when I change the contents of D5 it sets K11 back to '0.00'.
View 9 Replies
View Related
Jun 27, 2014
I have a macro which creates and names worksheets. I am making a button which also deletes the latest of these created worksheets, but doesn't delete other sheets. I am getting the error: Run-time error '424': Object Required. Here is my code for deleting the sheet:
[Code] .....
MSCount stores the highest "MS#" sheet.
The first line of the IF statement is where the error is.
View 7 Replies
View Related
May 1, 2014
I need to merge data from sheet 2 to data on sheet1.
Sheet 1 data is in the format given below with start date in G column and campaign ID in column H.
Start Date
Campaign ID
17-May-14
3834177
[Code].....
I need to look up Campaign ID of sheet 1 in sheet 2 and add all insertions and platform for that campaign to next columns i.e. I and J in sheet 1. For ex Campign ID 3834177 of sheet 1 has 5 insertions corresponding to it in sheet 2 hence the insertions and their Platform would transfer to sheet 1. Since Campign ID 3834177 is only in 2 rows on sheet 1, 3 more rows would need to be inserted in sheet 1 to accommodate 5 items and it would look like.
Start Date
Campaign ID
Insertion ID
Platform
[Code].....
View 1 Replies
View Related
May 30, 2014
code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.
For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.
View 1 Replies
View Related
Mar 6, 2013
I have written a macro that prompts the user to chose some files and if they meet certain criteria, it copies them to a specified folder.However, I have run into this 424 error "Object required".
Code:
Sub FILES2SFTP()
Dim FileNames As Variant
Dim I As Integer
Dim fso As Variant
Dim Data As String
ChDrive "G:"
ChDir "G:TEST"
[code]....
The error is in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
View 2 Replies
View Related
Jun 10, 2013
I have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
View 2 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
