#1
|
|||
|
|||
Add to list using new entry in cell
Hi Guys,
Is it possible to add to an existing data validation list the value I just entered into the cell and then have that value available from the dropdown selection next time, even if it means using a macro button to add the value, without having to readjust the list each time I have a new value to add to the list. My only thought at the moment would be to record an update to the list then try and modify this to suit my needs. Any ideas? regards Trevor |
#2
|
||||
|
||||
There might be some hints at https://www.contextures.com/xlDataVal08.html#Dynamic
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Thanks for the reply
I had a look at the page you showed, too much for me to look at right now, managed to record creating a list and this alone has given me a solution...
in vba when you redefine a list it shows the current list before deleting it and creating a new one even if it's the same. Using this information I can add the current cell contents to a new redefined list. I'll post the finished code in the morning as I'm not at work, and my vba cade already has a lot of dynamic addressing involved that i intend to make use of in this, so I can use the same vba macro for updatating any list with the currently selected cell, if it has a dropdown list to select from my code will update that list. regards Trevor And thanks for your input, I did get some extra ideas from there as well. |
#4
|
|||
|
|||
Here is my solution... Crude but it works
Here is my solution...
using a to hold value next row of list, xx is the new value for the list. First check to see if it's in the list, if not add it, update a for next time, then go back to selection zz. Crude but it works Code:
Sub Macro4() a = Mid(Range("C1"), 2) xx = ActiveCell.Value zz = ActiveCell.Address With Sheets("Automation Data").Range("B20:B100") Set Rng = .Find(What:=xx, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not Rng Is Nothing Then MsgBox "Already in List" Exit Sub Else ttt = MsgBox("Not in List, Add now?", vbYesNo) If ttt = 6 Then Sheets("Automation Data").Range("B" & a) = xx Range("C4:C5000").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='Automation Data'!$B$20:$B$" & a .IgnoreBlank = True .InCellDropdown = True .ShowInput = False .ShowError = False End With Sheets("Customer").Range("C1") = "C" & a + 1 End If End If Range(zz).Select End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
select from list and allow entry not in list | trevorc | Excel | 1 | 08-01-2018 04:36 AM |
Allow Entry In Cell Based On Value In Another Cell | koltanga | Excel | 1 | 05-07-2017 10:26 AM |
Cell won't accept entry | ottiliegirl | Excel | 0 | 04-21-2017 10:27 AM |
How to have one cell show a value based on the entry of another cell? | Marvinapplegate1964 | Excel | 1 | 05-04-2016 01:49 PM |
Restrict Cell Entry | Cardinal2 | Excel Programming | 1 | 01-30-2015 08:01 AM |