Updating a validation list macro

[rammieib]

Hi

I cannot find this in the groups anywhere.

I would like to update a validation list, which is in cells "FR2" to
"FR26" which contains a list of names. I would like a user to be able
to click on a button which pops up with a message saying "Enter New
Buyer Name", they enter the name, click ok and then the validation list
has been updated in alphbetical order with that persons name.

Could someone help me with the coding for this pls.

Thanks in advance.

roger

 

[Bob Phillips]

Dim ans
ans = InputBox("New value")
With ActiveCell
ans = .Validation.Formula1 & "," & ans
.Validation.Delete
.Validation.Add Type:=xlValidateList, Formula1:=ans
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[rammieib]

Bob

Thanks for your reply. I have been unable to make it work. After
entering the name into the inputbox, I cannot get that name to appear
in the cell when I push the validation list down one. I have the
following:

Sub EnterNewBuyer()

Dim ans
ans = InputBox("New value")
Range("FR2").Select
Selection.Insert Shift:=xlDown
With ActiveCell
ans = .Validation.Formula1 & "," & ans
.Validation.Delete
.Validation.Add Type:=xlValidateList, Formula1:=ans


Range("FR2:FR34").Select
Selection.Sort Key1:=Range("FR2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("B5").Select

End With
End Sub

 

[Bob Phillips]

Is it a text value you are entering, or a number? Also, is your original
list in the DV, or referencing a range somewhere?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[rammieib]

Bob

Entering a text value, it would be the name of someone new joining the
company for instance. I want that name putting into the range F2:F34
which is the list of current names. This is my DV list.

Range("f2") = InputBox("Enter name")

That line does not put whatever name entered into cell F2 when you
click ok. Why? This was the code I next tried after yours.

Thanks

 

[Bob Phillips]

Try this

Cells(Cells(Rows.Count, "F").End(xlUp).Row + 1, "F").Value =
InputBox("Enter Name")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

You could also do this with a dynamic list instead of re-setting your
validation routine(s) every time you add a name to the list.

Like so,

Say you have R1-R26 as your range, define the following as a named range

myRange =OFFSET(R1, 0, 0, COUNTA(R1:R$65536), 1)

then set your Cell's validation = List and set to myRange.

What myRange is now - a list of everything non-blank in Column R. If you
add values to the end of the list, anything that references this range will
automatically show up in the validation list.

I have a number of dynamic ranges in several applications that do this.
(Easiest to have a hidden worksheet that holds these values.)

Hope this helps,
Chad