Drop-down To Suggest Entry Not Force
Mar 20, 2008
I'm designing a spreadsheet for users to enter the length of journeys that they've claimed on their expenses. In order to ensure consistency between users, I want to add two drop-down boxes or listboxes that will offer a list of common locations (eg. office A, office B, office C, customer A): one for the start point, and one for the finish point. These will be referenced by a lookup table that will automatically fill in the journey distance.
However, users will also need to be able to input other locations themselves (ie. if a journey starts at home, not at the office). If I use data validation to create the drop-down box, it will only allow the values on the list; is there something that functions like validation in offering the user a range of options, but also allows other values?
View 4 Replies
ADVERTISEMENT
May 30, 2008
I have an Excel sheet which serves as an input form. I would like to set something up where if the user inputs any value into the cell range B12:B100 then they are forced to also enter a value in column J of that same row.
Maybe something could be done where if they enter a value in B12, then they cannot enter a value in B13 before entering a value in cell J12?
View 9 Replies
View Related
Jan 16, 2009
I have a spreadsheet in which has many different cells that need to be completed if another cell has data.
Examples
If the user inputs a name into A3, they must complete cells B3 thru D3. Cells B3:D3 contain different entry methods including a drop down list.
On the same sheet, if the user chooses GTD from a drop down list in cell U3, cells BA:BJ must be completed. And if they choose GCI from the drop down list in U3, they must complete cells BK:BR
View 9 Replies
View Related
Dec 30, 2007
I want to change data through a user form and disable users from going around my form. I want users to see the underlying spreadsheet as the form manipulates data.
If I enable a form in Modal mode users can bypass the form. If I use it without modal form I can't change data from my form. I seem to be in a catch 22.
View 5 Replies
View Related
Jun 30, 2008
I am using Control Tool box combobox in worksheet.How to change the Combobox to Non Editable combobox?
View 3 Replies
View Related
Feb 9, 2007
I have a spreadsheet that I add to daily with 3 Columns that are always the same. Name, ID number and Phone Number.
Is there a way to make it so that when other people in my department use this that no matter how they enter the name, it is always formatted the same. No matter they enter the ID number and phone number they are always formatted the same. I've tried some variations of data validation but couldn't get to where I need to be.
Attached is a simple example.
I'd like the name to always be formatted as Smith, Bob
I'd like the ID Number to always be formatted as xxx xx xxx
I'd like the Phone Number to always be formatted as xxx xxx xxxx
View 9 Replies
View Related
Feb 16, 2007
Below is the existing code that I'm working with and would like to be able to make the ' name' column either Upper or Proper case on entry. I haven't decided which I'm going to use yet.
Set r = Sheet1.Range("A2:C65536")
If Not Intersect(Target, r) Is Nothing Then
sTgt = Trim(Target.Value)
If sTgt = "" Then Exit Sub
Select Case Target.Column
Case NmCol
If InStr(sTgt, ",") = 0 Then
iSpc = InStrRev(sTgt, " ")
Target.Value = Mid(sTgt, iSpc + 1) & ", " & Left(sTgt, iSpc - 1)
End If
View 3 Replies
View Related
Sep 5, 2007
A form loads and the first thing the user is to do is enter a specific date in a text box (preferably in, "mm/dd/yyyy" format.)
Is there any way to set up the text box so that when the form loads, the, "/"'s (slashes) are already in place? And can I set it up so that if the user enters in the date in any other manner aside from, "mm/dd/yyyy", that it will give them an error?
Finally, I want the user's date input to store on a worksheet (in row, "G".) So the first time the user uses the form, they type in a date and it stores the date in the FIRST AVAILABLE CELL IN ROW, "G." (example: "G1") The next time they use the form and type in a date, it is to store in, "G2", then in, "G3", ect. Each time they fill out the form, the new entry is to save in the next row beneath the previous entry.....
For other text boxes and combo boxes I have been using the following code to do this:
Sheets("Sheet1").Range("A" & intRow) = Text.Value (or Combobox.Value - depending on whether it was a text box or a combo box.)
Unfortunately I have NO idea how to apply this logic in regards to text boxes that are to store dates.
View 9 Replies
View Related
Dec 30, 2007
I found the following code which works fine until I start to select multiple cells in cell B for deletion etc. At this point it throws up a run-time error 13 type mismatch, which will cause problems when other people start to use the spreadsheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Offset(0, -1) = "" Then
Target.Select
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
MsgBox ("You must enter Atlas Part No. first")
End If
End Sub
View 2 Replies
View Related
Jul 31, 2008
I need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View 2 Replies
View Related
Dec 29, 2006
I have two columns, Account Number and Account Type. I already use Data Validation on Account Number to make sure it is numeric and on Account Type to make sure the user picks from a drop-down list. I want it so that if a user inputs an account number, it forces the user to automatically pick from the drop-down menu in the account type column, otherwise post an error.
View 3 Replies
View Related
Dec 14, 2011
I have a list box in a user form using R2:R3 as Row source.
I need to force the users to choose one of the items and not allow a blank entry to be entered.
I used the code below for the entries however it still allows for a blank to be entered into the Cell.
This part of the code works for Me.sapor = "" Or Me.jobna = "" Or Me.ordernu = "" But not for the next (2) list boxes it allows blanks Or Me.snd = "" Or Me.mcode = ""
Me.Snd and Me.mcode are list boxes.
Code:
If Me.sapor = "" Or Me.jobna = "" Or Me.ordernu = "" Or Me.snd = "" Or Me.mcode = "" MsgBox ("Feilds SAP Number, Job Name, Price, Code and Month Code Must be Completed")
Exit Sub
Code:
Private Sub CommandButton1_Click()
Dim rNextCl As Range
' Row count = where the select cell finishes and window box moves to rows over
' offset = howmany cells up or down from the row count
[Code]....
View 2 Replies
View Related
Apr 22, 2009
I am trying to enter a formula that will automatically suggest a purchase quantity by comparing the pieces needed against the required package quantity. I have attached a small example workbook, which has a description of the requirements for the formula, but I will also incude that description in this post.
Problem Description:
Our purchasing guideline is that we MUST order product in package quantities, as the vendor won't ship a partial package. The required package quantity for each part is shown in column B. If the pcs needed qty in column A is less than half the buy pack qty, you buy zero. If it is half or above, you buy a full package.
I want to put a formula in column C that will recommend the suggested buy quantity. The formula would need to compare the pcs needed to the buy pack qty, and put the suggested buy qty in column C. It should also consider multiples of the buy pack qty, such as if the buy pack is 5, and the pcs needed is 12, the formula would suggest 10 pcs. If the buy pack is 5, and pcs needed is 13, the formula would suggest 15 pieces, etc. I have manually entered the sugg buy qty for the first 22 rows, to give examples of what value the formula should return.
View 2 Replies
View Related
Jan 24, 2013
I use drop down lists to speed up data entry. all my lists are on sheet 1 but my actual fields for selection are on sheet 2. I am trying to see if i can make the date fields columns drop down calenders. Sheet 2 is for a special import and i cannot add extra columns that are not a part of my import.. Is there a way to add drop down calenders or dates to sheet 2 without adding extra columns?
View 4 Replies
View Related
Jun 1, 2009
I currently have showing 800 suppliers in a List Box... sadly when having to search for a sepcific supplier this seems to be very fustrating and time comsuming for people in the Company.
I have tried the method of copying all of the Supplier above the list, so it can do an AutoComplete filter, but the data needs to be exact.
What i would really like is if i was to type the letter "V" in the cell, it would be able to provide me with a list of the "V" suppliers in a list.
View 9 Replies
View Related
Aug 31, 2009
I currently have a huge data sheet with multiple columns of information. It contains a list of projects organized by columns with information pertaining to each project. I've named this worksheet data.
On another sheet I've named Present, I'm trying to find a way to reference a single project at a time using a drop down list. The Present Sheet has a list of characteristics fields that need to be filled with information from "data" worksheet. I want the characteristics to change whenever I choose another project name from the drop down list.
View 2 Replies
View Related
Aug 21, 2007
I've been searching but I can't find an answer that fits. Here's the closest I've come. Force Date Entry on Particular Condition. What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data. Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.
View 3 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
Sep 11, 2013
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
View 3 Replies
View Related
Mar 14, 2014
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies
View Related
May 1, 2009
I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies
View Related
Nov 22, 2008
what its called when you have drop down menus that correspond with other drop down menus.
View 2 Replies
View Related
Oct 25, 2006
Here is what I'm trying to do:
I have 4 sheets:
1 called main
1 called states
1 called counties
1 called people
On "main" I have 1 drop down box and 1 cell:
the drop down lists the states from "states" sheet
the cell, which i would like to have as a drop down is supposed to be listing the counties in the state selected from the 1st drop down. right now it is setting the value to TRUE for testing purposes.
then when the county is selected, it will display the people in that county listed in "people". So far I can only get the 1st drop down to list the states. that's the easy part. i need to getting the counties to list in another drop down based on what state is selected.
View 2 Replies
View Related
Dec 5, 2007
Im a fairly basic VBA user who has just purchased the code for J-Walk's Enhanced Data Form.
On other threads in this forum I have noticed a lot of people asking how to make the contents of one drop-down box dependant on the selection in another. I am trying to do the same thing but with the criteria section of the EDF form.
Is this possible? What does give me hope is that a simple Autofilter - whilst not as aesthetically pleasing, does the job well.
View 3 Replies
View Related
Aug 7, 2008
Currently I have columns fixed or assigned to the names of insurance providers/companies.
In each column below the name of the Insurance Company, a combobox containing the plans that company offers. Choose a plan, and the rest of the column is filled in using vlookup.
Now to save space, IE opposed to having 13-15 columns one assigned to each Insurance Company/Provider. I am looking to have only 3-5 columns with a first initial combobox in which one chooses the Name of the Company/Provider, and in turn populate the original combobox and vlookup functionality I already have built in.
Here is an example of what I am working with.
[url]
View 3 Replies
View Related
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
May 28, 2008
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
View 9 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Mar 1, 2007
In the attached example I am trying to get the selections from one drop down to gnerate available selections in the next. The first drop down is the door style. This then needs to generate the possible drop downs in wood species. This then needs to generate the possible drop down in available stains. this then needs to generate the related door factor....
View 9 Replies
View Related
Oct 26, 2009
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies
View Related
