Drop Down Boxes

  • Thread starter Thread starter Serenity99
  • Start date Start date
S

Serenity99

I haven't done this in a long time and cannot remember how to do it.
How do I make drop down lists in cells
 
You can use Data Validation to restrict what they put into D5. Select
cell D5, then

Data > Validation

Then from the drop down list, choose List, and in the Source box, put
whatever you want, i.e. x. Then click OK.

Now whenever you try to enter a value that is not correct, you get a
warning. To help them even more, when the Valdiation dialog box is
open, select the last tab, Error Alert. Put a title for it (Exact entry
required) then in the message box put "You must enter a small x"
(without the quote marks).
 
shades said:
*You can use Data Validation to restrict what they put into D5
Select cell D5, then

Data > Validation

Then from the drop down list, choose List, and in the Source box, pu
whatever you want, i.e. x. Then click OK.

Now whenever you try to enter a value that is not correct, you get
warning. To help them even more, when the Valdiation dialog box i
open, select the last tab, Error Alert. Put a title for it (Exac
entry required) then in the message box put "You must enter a smal
x" (without the quote marks). *
Click to expand...

How would I do this so that whatever is entered in each cell i
automatically added to the list
 
Somehow responses from my other question about IF formulas found their
way into this thread.

Re drop down lists, I created a drop down list but I want to modify it
so that whatever is entered into each cell is automatically entered in
the list so it is in the list for selection for future cells.

How is this done? Right now, whenever there is a new entry I have to go
to Data >Validation and add it manually.
 
Create a Category List
-Type your list of categories
-Select the cells in the list
-Click in the Name box, to the left of the formula bar
-Type a one-word name for the list, e.g. Categories.
-Press the Enter key.

Apply Data Validation
-Select the cells in which the user will select or type a category.
-Choose Data>Validation
-From the Allow dropdown, choose List
-In the Source box, type: =Categories
-On the Error Alert tab, remove the check mark from 'Show error alert
after invalid data is entered
-Click OK

Add code to append new categories
-Right-click the sheet tab on the sheet with data validation
-Choose View Code, and paste in the following code
(In this example, the Categories list is on Sheet1, and the
Data Validation is on Sheet2, Column A)

Users can select or type a category. New categories will be
automatically added to the categories list.

'=============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet
Dim rng As Range
Set ws1 = Worksheets("Sheet1")
Set rng = ws1.Range("Categories")
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction _
.CountIf(rng, Target.Value) Then
'do nothing
Else
rng.Offset(rng.Cells.Count, 0) _
.Resize(1, 1).Value = Target.Value
rng.CurrentRegion.Name = "Categories"
Set rng = ws1.Range("Categories")
rng.Sort Key1:=ws1.Range("A1"), _
Order1:=xlAscending, Header:=xlNo
End If
End If
End Sub
'=================================
 
Back
Top