Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2018, 09:05 PM
trevorc trevorc is offline Add to list using new entry in cell Windows 7 32bit Add to list using new entry in cell Office 2013
Competent Performer
Add to list using new entry in cell
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 08-06-2018, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Add to list using new entry in cell Windows 7 64bit Add to list using new entry in cell Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 08-06-2018, 01:27 AM
trevorc trevorc is offline Add to list using new entry in cell Windows 7 32bit Add to list using new entry in cell Office 2013
Competent Performer
Add to list using new entry in cell
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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.
Reply With Quote
  #4  
Old 08-07-2018, 04:44 PM
trevorc trevorc is offline Add to list using new entry in cell Windows 7 32bit Add to list using new entry in cell Office 2013
Competent Performer
Add to list using new entry in cell
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
Reply

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
Add to list using new entry in cell How to have one cell show a value based on the entry of another cell? Marvinapplegate1964 Excel 1 05-04-2016 01:49 PM
Add to list using new entry in cell Restrict Cell Entry Cardinal2 Excel Programming 1 01-30-2015 08:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:13 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft