If you want to sort this range with new data, you need to re-perform the sort function.
Thank you so much for taking your time and sharing your knowledge to help me solve my problem.For example, you try to add a new data set into the range B1:B5, it won’t change the order automatically.
Is there a way to make this happen without entering this code into each worksheet? Since I'm entering text with your code (in Upper Case) and on those few occassions where it needs to be changed, is there a way to change the contents of the cell in D when entered in Lower case and have the validate list recognize the lower case, but display it in Upper case? I guess I could enter both Upper Case and Lower Case in the Validate List, so the question becomes how can those lower case entries be changed to upper case? All I'm trying to do is simplify the date entry proces by reducing the number of key strokes.įinally, the workbook this is going into will have 17 worksheets and each worksheet will need to run this code. I changed your numbers to the text (in Upper Case) I need to insert and the column from C to D. WOW, Jonesey! How do you know this stuff? I'm impressed. If it isn’t empty, the macro stops execution The only downside to this is that the macro will be invoked EVERY time a change is made to the worksheet, although it only takes a second to run, this might prove distracting.Ī) Column A contains some sort of Identifier, which is only entered when a new record is enteredī) Column C contains your drop down, pre-populated with the number 30, but allowing you to also select 10, 15, 20 or 25Ĭ) Column B must only populated AFTER Column A – this is because Column B is tested every time the macros is run. Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ If IsEmpty(Cells(r, 2)) = True Then 'This indicates it's a NEW record Private Sub Worksheet_Change(ByVal Target As Range) In fact, thinking about it, Worksheet_Change will work a lot better. Option 1 can be triggered either by a specifically written macro, or less commonly, you could write a little bit of code to create & populate your drop down by using either the ‘Worksheet_Change’ or the ‘Worksheet_SelectionChange’ built in macros. I’m assuming that each row in your spreadsheet equates to a unique data record.Ģ – you pre-populate as many rows as you want, but the actual text, default or otherwise is ‘hidden’ by making the text colour exactly the same as the cell background colour, then setting it back to normal text (black?) when a new row is entered. There are only 2 distinct methods to achieve what you want.ġ – your drop down list (irrespective of defaults or other values) is ONLY populated when a new row (record) is entered. I’m just thinking out loud on this one, so please bear with me.
If the only solution is data validation, I need specific instructions on how to make my default value automatically populate the cell as I've not been able to find that anywhere. Since the user will be either using or to move from cell to cell, I don't want to make the user go to the mouse to select the value. I would like this value to automatically populate the cell, but it can be changed the other 20% of the time as needed. Of the 4 potential values for this cell, one will be used about 80% of the time.
I've tested this solution by creating a dropdown box that lists the potential values (text), but it is not automatically inserted into the cell. I don't see this solving this problem in my version. I've searched the web and groups and keep finding the solution of data validation. Them to see this value all the way down the spreadsheet for the maximum potential number of lines in the spreadsheet." It can default to 30 (years), but can also be over-ridden as necessary.
The length of a mortgage to be calculated for a loan. "I want to have default values in a cell (a column) unless the user changes the value. I have the same problem as originally reported except in Excel 2003.