Drop Down Menus

  • Thread starter Thread starter cassy01
  • Start date Start date
C

cassy01

i want to create a cell that when i type in a word in cell B3 and click
on a button it will add it to a drop down menu.

Is this possible ??

Many Thanks:D
Benn
 
Benn
I take it that you have a Data Validation cell somewhere (other than B3)
and you want whatever you type in B3 to be added to the list for the Data
Validation cell.
One way:
Add a worksheet to your workbook and call it Utility. Put your list in
Column A starting in A1. Name the list TheList although the following macro
names it for you when you run the macro.
In your Data Validation cell, when you set it up, select List and in the
Source box type "=TheList" without the quotes.
Put this macro in a regular module. Place a button somewhere and assign
this macro to that button. HTH Otto
Sub UpdateList()
Dim Dest As Range
With Sheets("Utility")
If .[A1] = "" Then
Set Dest = .[A1]
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp)(2)
End If
[B3].Copy Dest
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Name = "TheList"
End With
End Sub
 
Back
Top