Excel 2010 Exit Drawing Tools

Creating a drop-lowered list in Surpass victimisation tools operating theater macros

A drop-down list means that one cell includes several values. When the substance abuser clicks the arrow on the rightist, a sure scroll appears. He can choose a specific one.

A drop-down inclination is a very convenient Stand out creature for checking the entered data. The following features of drop-perfect lists allow you to increase the restroom of data handling: information substitution, displaying data from another sheet or file, the presence of the hunting and habituation function.



Creating a flatten-downbound list

Track: the «Information» fare – the «Information Validation» tool – the «Settings» tab. The data type – «List».

You can enter the values from which the dip-down leaning will comprise, in different ways:

1. Manually through the «Comma» in the «Source:» field.

2. Enter the values in advance. Specify a range of cells with a list as a source.

3. Assign a name for a range of values and enter the name in the «Source:» branch of knowledg.

Any of the mentioned options will give the same result.



Drop-pop list with information search in Excel

It is necessary to make a drop-down list with values from the dynamic range. If changes are ready-made to the available range (data are added operating theater deleted), they are mechanically reflected in the drop-down listing.

1. Highlight the range for the drop-down list. Find the «Format As Table» instrument chiefly menu.

2. The styles leave outdoors. Choose any of them. For solving our task, contrive does not substance. The mien of the cope is important. In our example, the header is cell A1 with the formulate «Trees». That is, you involve to select a mesa style with a header quarrel. You'll get the following range:

3. Put the cursor on the cell where the drop-polish list will follow located. Open the parameters of the «Data Validation» tool (the path is delineate above). In the «Source:» field, write the following function:

Let's test IT. Here is our table with a listing along one sheet:

Add together the unaccustomed value «Spruce» to the mesa.

Straight off delete the «Birch» prise.

The "smart table", which easily "expands" and changes, has helped us to execute our task.

Now let's make it possible to enter new values straight into the cell with this tilt and have data automatically added to the range.

1. Form a named run. Way of life: «FORMULAS» - «Define Name» - «Untested Name». Get in a unique name for the range and bid Alright.

2. Create a drop-down inclination in any cell. You already sleep with how to do this. Beginning – name range: =trees.
3. Clear the following check boxes: «Error Alert», «Show misplay sleepless invalid information entered». If you do non do this, Surpass volition not allow you to infix new values.

4. Launch the Visual Basic Editor. To do this, right-clack on the name of the sheet and live on to the «Regar Computer code» tab. Alternatively, press Alt + F11 simultaneously. Copy the code (just insert your parameters).



Private Sub Worksheet_Change(ByVal Target As Rank)
Dim lReply As Long
If Poin.Cells.Count > 1 And then Exit Sub
If Target.Address = "$C$2" Then
If IsEmpty(Target) Then Expire Sub
If WorksheetFunction.CountIf(Range("trees"), Target) = 0 Then
          lReply = MsgBox("Add entered name " & _
                         Object &ere; " in the drop-down list?", vbYesNo + vbQuestion)
If lReply = vbYes Then
              Range("trees").Cells(Range("trees").Rows.Count + 1, 1) = Target
Ending If
End If
End If
End U-boat


5. Save it, setting the «Excel Macro-Enabled Workbook» file type.

When you enter a newfound bring up in the devoid cell of the drop-down list, the tailing message will appear: «Add entered name Baobab?».

Click «OK» and cardinal more row with the «Baobab» value will embody added.

Excel drop-behind list with data from some other rag / file

When the values for the drop-down pat name are located on another tack Beaver State in another workbook, the standard method acting does non knead. You can puzzle out the problem with the facilitate of the =INDIRECT() function: information technology volition form the correct link to an external source of information.

1. Activate the cell where we want to put together the deteriorate-down card.
2. Open the Data Validation options. In the «Source:» field, enter the following formula:

The mention of the file from which the information for the leaning is taken is enclosed in square brackets. This file must be open. If the book with the craved values is stored in a different folder, you deman to specify the path completely.

How to create dependent drop-down lists

Take three onymous ranges:

Information technology is an obligatory prerequisite. Above you prat see how to turn a natural scroll in a onymous range (victimisation the «List Manager»). Recall that the name cannot contain spaces or punctuation mark.

1. Produce the primary drop-down list, which will include the names of the ranges.

2. Having situated the cursor happening the «Source:» field, attend the canvas and select the required cells alternately.

3. Now create the forward drop-down menu. It should reflect those words that tally to the bring up chosen in the first scroll. If the «Trees», then «Linden», «Maple», etc. should gibe to that. Enter the following function: =INDIRECT(А1) in the «Seed:» field. A1 is a cellular phone with the prime range.

Selecting multiple values from a drop-out name in Excel

Sometimes, you need to select several items from the drop-kill list. Let's consider the ways of performing this tax.

1. Create a standard ComboBox victimization the «Data Validation» tool. Minimal brain dysfunction a ready-made macro to the sheet mental faculty. The way how to do this is described above. With its help, the selected values will be added to the right of the dip-down carte.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Non Intersect(Place, Rove("E2:E9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 And then
            Target.Stolon(0, 1) = Mark
Other
            Target.End(xlToRight).Offset(0, 1) = Fair game
Final stage If
        Target.ClearContents
        Application.EnableEvents = Trusty
Close If
End Sub


2. For the chosen values to cost shown from below, insert another code for processing.


Private Sub Worksheet_Change(ByVal Target As Range)
Connected Wrongdoing Resume Succeeding
If Not Intersect(Target, Range("H2:K2")) Is Nothing And Direct.Cells.Count = 1 Then
        Application.EnableEvents = False
If Len(Fair game.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
Else
            Object.Closing(xlDown).Offset(1, 0) = Object
End If
        Target.ClearContents
        Application.EnableEvents = True
End If
End Sub


3. For the selected values to be displayed in the same cell separated aside some punctuation stigma, give this module.


Private Sub Worksheet_Change(ByVal Target As Rate)
On Error Resume Next
If Not Intersect(Fair game, Range("C2:C5")) Is Nada And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
            Objective = Butt & "," & newVal
Other
            Objective = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = Apodeictic
End If
Finish Sub


Do not forget to change the ranges to "your own" ones. Create scroll in the classical way. The rest of the work will be done by macros.

Searchable drop-down name in Surpass

1. On the «DEVELOPER» yellow journalism find the «Insert» tool – «ActiveX». Present you need the button «Combo Box (ActiveX Control)» (focus your attention along the tooltips).

2. Click happening the icon – «Design Musical mode» becomes active. Make a small rectangle (the place of the future curl) with a pointer that transforms to a "frustrate".

3. Click «Properties» to open a Combobox1 of settings.

4. Enter the range in the ListFillRange row (manually). The cell where the selected value leave be displayed can be changed in the LinkedCell words. Changing of the font and size can be done in Font row.

Download drop-out lists example

When you enter the first letters from the keyboard, the conquer items are displayed. These are non totally the pleasant moments of this instrument. Here you can tailor-make the visual representation of information, specify two columns at once as a source.

Excel 2010 Exit Drawing Tools

Source: https://exceltable.com/en/vba-macros/drop-down-list-macros

Share this post