Add to list using new entry in cell

trevorc · #1 08-07-2018, 04:44 PM

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

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