Dropdown menue, if new item how to ask for all credentials

RAMatgreenleaf

Board Regular
Joined
Sep 23, 2005
Messages
84
Ok,
so i have a drop down menue, it has a list off all the plants, and there corresponding prices
now i have a button that when i press it, it runs a macro which clears the validification of the cell so i can type in a new plant that is not on the list

how can i make it so when i press the macro button, it asks me in a popup for the name of the plant and its price, and then takes that info, and puts it in my list of where my drop down menu takes its info from? so that it is avaliable for later use?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
This is the way I have one set up.
On sheet1 in col A I have the list (for this example; named range 'Plants')place this code in the 'Refers To' box
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

Create a userform to add your plants, and place this code on a button (Add to Plant List (?))

Private Sub cmdAddtoPlants_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'find first empty row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(irow, 1).Value = Me.textbox1.Value
'clear the data
Me.textbox1.Value = ""
End Sub

You may have to amend the range, but it should get you started.

Colin.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,033
Messages
5,569,777
Members
412,291
Latest member
marypolitan
Top