VBA to add entry to dynamic data validation

[spena129]

Hi All,

I am sure this question has been posed and answered before, so feel free to post a link.

I have a dynamic data validation [=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),1] in cell D4 with a named range (lstAgencies) that refers to a list on a separate sheet. I would like the ability to add entries not found in the list within the cell (D4) that has the data validation. Obviously, the validation allows the user to manually type an entry if not found in the original list.

Example: My list on sheet "Lists" has 3 entries in column C (Agency 1, Agency 2, Agency 3). Within the data validation cell D4 on my original sheet "Input", I want to be able to add new agencies to the list if not on "Lists" by inputing in cell D4.

I feel like I did a poor job of describing, so let me know if more info is needed.


Thanks in advance!

 

[Luke M]

If you can input any value into D4 (it will either be on the list = ok, not on list = add to list) then why have the validation at all?

 

[vasim]

Add a command button from control toolbox and the following macro


Private Sub CommandButton1_Click()

Sheets("Sheet1").Select

Range("G1").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = Range("A1")


End Sub

 

[Luke M]

@Vasim

In the last line:

ActiveCell.Value = Range("A1")


Range will refer to the active sheet since there's no sheet object attached to it. Shorter code without having to do all the selects would be:


Private Sub CommandButton1_Click()

Sheets("Sheet1").Range("G1").End(xlDown).Offset(1, 0) = Range("A1").Value

End Sub


@Steve,

If vasim's solution doesn't work out, Debra's posted a full article on how to add to data validation list here:

http://www.contextures.com/excel-data-validation-add.html

 

[spena129]

Thanks to you both!