User Input Box to add item to named list

[bms66]

Hi

I am looking for a macro that will pop up an input box so the user can add an item to a named range used in a drop down list. E.g. sheet one column A is populated via a named range called colours which has three items. If the user enters another colour (via and input box) in list box i need it to check it doesn't already exist and then add it to the list and increase the range for the list box.

Any help appreciated

Thank you

Mykal

 

[bms66]

Not sure how to mark this as solved or edit to reflect the same. I've managed to figure this one out now

Sub Button1_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String


strName = InputBox(Prompt:="Add Additional Colour", _
Title:="ADD COLOUR", Default:="Enter Colour Here!")
If strName = "Enter Colour Here!" Or _
strName = vbNullString Then
Exit Sub
Else

On Error Resume Next
Result = WorksheetFunction.Match(strName, Range("Colours"), False)
If Err = 0 Then
MsgBox "The Colour you have entered already exists"
Else

newEntry = strName
rws = Range("Colours").Rows.Count
Range("Colours").Cells(1, 1).Offset(rws, _
0).Value = newEntry
With Range("Colours").Name
adr = .RefersTo
.Delete
End With
Range(adr).Resize(rws + 1, 1).Name = "Colours"
Err.Clear
End If
On Error GoTo 0
End If
End Sub