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.
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?
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
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
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
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.
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?
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.
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
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.
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
I've found part of my answer from searching for a previous thread and altering to suit my needs. I've got the array formula: =SUM(IF(FREQUENCY(IF(D2:D20000"",MATCH("~"&D2:D20000,D2:D20000&"",0)),ROW(D2:D20000)-ROW(D2)+1),1)) This gives me the correct number of unique entries. What I'm after is the number of unique entries that start with 1042
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'.
=COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")
I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong.
This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.
I have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.
I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
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.
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account. If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
