latihan excell

56
Excel Tutorials - Data Validation Data Validation Basics Data Validation - Create Dependent Lists Data Validation - Dependent Dropdowns from a Sorted List Data Validation - Dependent Lists With INDEX Hide Previously Used Items in a Dropdown List Data Validation - Display Messages to the User Data Validation - Display Input Messages in a Text Box Data Validation - Use a List from Another Workbook Data Validation Criteria Examples Data Validation Custom Criteria Examples Data Validation Tips Data Validation Documentation Data Validation Combo Box Data Validation Combo Box - Named Ranges Data Validation Combo Box -- Click Data Validation - Add New Items Data Validation Excel 2003 -- Introduction What is Data Validation? Video: Create a Drop Down List in Excel 2003 Create a Drop Down List - Instructions Download the Sample File More Data Validation Tutorials For the Excel 2010 video and instructions, click here. What is Data Validation? In this tutorial, you'll see how to create a drop down list of choices in a cell. Video: Create a Drop Down List in Excel 2003 Watch this video to see the steps for creating a drop down list in Excel 2003. Written instructions are below the video. Create a Drop Down List - Instructions Use Data Validation to create a drop down list of options in a cell. List items can be typed in a row or column on a worksheet, or typed directly into the Data Validation dialog box. Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Edit|Clear|ClearAll 1. Create a List of Items If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists.

Upload: bakti-nusantara

Post on 27-Oct-2015

50 views

Category:

Documents


4 download

DESCRIPTION

excell

TRANSCRIPT

Page 1: Latihan Excell

Excel Tutorials - Data Validation

Data Validation BasicsData Validation - Create Dependent ListsData Validation - Dependent Dropdowns from a Sorted List Data Validation - Dependent Lists With INDEX Hide Previously Used Items in a Dropdown ListData Validation - Display Messages to the UserData Validation - Display Input Messages in a Text Box Data Validation - Use a List from Another Workbook Data Validation Criteria Examples Data Validation Custom Criteria ExamplesData Validation TipsData Validation DocumentationData Validation Combo Box Data Validation Combo Box - Named Ranges Data Validation Combo Box -- Click Data Validation - Add New Items

Data Validation Excel 2003 -- Introduction

What is Data Validation?

Video: Create a Drop Down List in Excel 2003

Create a Drop Down List - Instructions

Download the Sample File

More Data Validation Tutorials

For the Excel 2010 video and instructions, click here.

What is Data Validation?

In this tutorial, you'll see how to create a drop down list of choices in a cell.

Video: Create a Drop Down List in Excel 2003

Watch this video to see the steps for creating a drop down list in Excel 2003. Written instructions are below the video.

Create a Drop Down List - Instructions

Use Data Validation to create a drop down list of options in a cell. List items can be typed in a row or column on a worksheet, or typed directly into the Data Validation dialog box.

Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Edit|Clear|ClearAll

1. Create a List of Items

If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists.

In single row or column, type the entries you want to see in the drop-down list. (Note: The list must be in a single block of cells -- e.g. you can use A2:A6, but not A2, A4, A6, A8.)

2. Name the List Range

If you type the items on a worksheet, and name the range, you can refer to the list from any worksheet in the same workbook.

Select the cells in the list.

Click in the Name box, to the left of the formula bar

Page 2: Latihan Excell

Type a one-word name for the list, e.g. FruitList.

Press the Enter key.

Note: To create a named list that automatically expands to include new items, use a dynamic range.

3. Apply the Data Validation

Select the cells in which you want to apply data validation

From the Data menu, choose Validation.

From the Allow drop-down list, choose List

In the Source box, type an equal sign and the list name, for example: =FruitList

Click OK.

Tip: To select a range name, instead of typing it:

In the Data Validation dialog box, under Allow, select List

Click in the Source box, and on the keyboard, press the F3 key

From the Paste Name list, select a named range, and click OK.

Click OK, to close the Data Validation dialog box.

To view the Excel 2003 steps in a short data validation basics video, click here

4. Using a Delimited List

Instead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas. For example:

Page 3: Latihan Excell

Yes,No,Maybe

Note: This method of Data Validation is case sensitive -- if a user types YES, an error alert will be displayed.

5. Allow Entries that are not in the List

To allow users to type items that are not in the list., turn off the Error Alert.

6. Protect the List

To protect the list from accidental damage, if you have entered it on a different worksheet, you can hide that sheet.

Select the sheet that contains the list

Choose Format | Sheet | Hide

Download the Sample File

Download the zipped Excel 2003 sample workbook

Excel Data Validation -- Create Dependent Lists

Create Named Lists Apply the Excel Data Validation Test the Excel Data Validation Video: Dependent Data Validation

Adding a Third Dependent ListUsing Two-Word ItemsUsing Items with Illegal CharactersUsing Dynamic Lists

Download the Sample File

More Data Validation Tutorials

You can limit the choices in an Excel data validation list, by using named ranges and the INDIRECT function, to create dependent data validation lists. In this example, if Fruit is selected as the Category, only Fruit appears in the Item drop-down list.

Another method is to use the OFFSET function, to extract items from a sorted list, as described here: Dependent Dropdowns from a Sorted List

Create Named Lists

Page 4: Latihan Excell

Start by creating Named Lists, which will be the choices in the dependent data validation drop down lists. In this example, the first list will be named Produce. It contains the Produce categories -- Fruit and Vegetable.

1. Create the first Named List

In an empty area of the workbook, type the entries you want to see in the drop-down lists. These should be one-word entries, to match the dependent list names that will be created. If you need to use multiple word entries, see:Using Two Word Items

Select the cells in the list (but not the heading).

Click in the Name box, to the left of the formula bar

Type a one-word name for the list, e.g. Produce.

Press the Enter key.

2. Create the supporting Named Lists

Type the entries you want to see in the Excel data validation drop-down list for one of the Produce 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. Fruit. This name must be exactly the same as the matching entry in the Produce list.

Press the Enter key.

Create another list with the items for the next category -- Vegetable in this example

Apply the Excel Data Validation

The cells in the Category column will allow a List. The cells in the Item column will use the INDIRECT function to select a list.1. Apply the Excel Data Validation

Select the cells in which you want to apply Excel data validation using the Category List

Page 5: Latihan Excell

From the Data menu, choose Validation.

From the Allow drop-down list, choose List

In the Source box, type an equal sign and the list name, for example: =Produce

Click OK.

2. Create the Dependent Data Validation

Select the cells in which you want to apply dependent data validation using the Fruit or Vegetable List, dependent on which Category has been selected

From the Data menu, choose Validation.

From the Allow drop-down list, choose List

In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Category column: =INDIRECT(A2)

Click OK.

Note: If cell A2 is empty, you'll see the message shown below. Click Yes to continue.

Test the Dependent Data Validation

Click on a cell in the Category column, and select either Fruit or Vegetable, from the Produce drop down list.

Press the Tab key on your keyboard, to move to the Item column, in the same row.

In the Item column, click the drop down arrow, and select an item. The drop down list shows either Fruit or Vegetable items, depending which has been selected in the Category column

Page 6: Latihan Excell

NOTE: If a Category has not been selected, the Item drop down in that row will not work.

Video: Dependent Data Validation

In this dependent data validation example, if a country is selected from the first drop down, only that country's cities appear in the next column's drop down list.

However, you can use the Excel IF function to make the selection more flexible. If no country is selected, the City column will show a list of world cities in the dependent drop down list.

Adding a Third Dependent List

You could add another set of dependent data validation dropdown lists that depend on the selections in the first two dropdowns. For example, select a country and region, then select a city in that region.

Create the two named ranges and dropdown lists as described above.

Create another set of named ranges, naming them for the available combinations in the first two Excel data validation drop-downs. For example, you might create ranges named CanadaOntario and USANewYork.

For the third dropdown, choose to Allow: List, and use a formula that combines the entries in the first two columns, and removes the spaces from the names. For example, in cell D2, the data validation formula would be:

  =INDIRECT(SUBSTITUTE(B2&C2," ",""))

Using Two-Word Items

You may need to have two-word items in the first Excel data validation drop-down list. For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit'

Create the first named range and dropdown list as described above.

Create the supporting named lists, using one-word names, e.g. RedFruit, GreenFruit, YellowFruit

For the second dropdown, choose to Allow: List, and use a formula that removes the spaces from the names. For example:

  =INDIRECT(SUBSTITUTE(A2," ",""))

Page 7: Latihan Excell

Using Items with Illegal Characters

You may need items in the first Excel data validation drop-down list that contain characters not allowed in range names, such as the ampersand (&).

For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow & Orange Fruit'. For the dependent lists, you can create ranges with one-word names, such as YOFruit. Then, you can create a lookup table, which lists each item in the first Excel data validation drop-down list, and the range where its dependent items will be stored.

To start, create the item lists and the first Excel data validation drop-down:

Create the first named range and drop-down list as described above. In this example, the range is named FruitList, with values in cells A6:A8. The drop-down list in cell A2 uses FruitList as its source.

Create the dependent lists, and name them, using one-word names, e.g. RedFruit, GreenFruit, YOFruit. In this example, RedFruit is in A11:A12, YOFruit is in B11:B12 and GreenFruit is in C11:C12.

Select an item from the drop-down list in cell A2.

Next, you'll create the lookup table, to match each item with its dependent items' range name.

In the column to the right of the FruitList range, enter the range name for each item's dependent list. For example, YOFruit is entered as the dependent range name for Yellow & Orange Fruit.

Name the lookup table. In this example, the range A6:B8 is named NameLookup.

Select cell B2, and from the Data menu, choose Validation.

Choose to Allow: List.

For the Source, enter a formula that uses a VLookup formula to find the dependent list's range name. For example:   =INDIRECT(VLOOKUP(A2,NameLookup,2,0))

With Green Fruit selected in cell A2, the VLookup formula will return GreenFruit as the range name for the dependent list. The GreenFruit list will be displayed in cell B2's drop-down.

Using Dynamic Lists

Because the INDIRECT function only works with references, not formulas, the previous method for dependent data validation won't work with dynamic lists. Instead, you can use the following method:

Create the first named range and dropdown list as described above.

Create the supporting named lists, and name the first cell in each range, e.g. cell B1 is named Fruit and cell C1 is named Vegetables.

Page 8: Latihan Excell

Name the column in which each list is located, e.g. column B is named FruitCol and column C is named VegetablesCol

For the second dropdown, choose to Allow: List, and use a formula that calculates the lookup range. For example, if the first dropdown list is in cell E2:

=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1)

if two-word items will be used, you can include the SUBSTITUTE function in the formula:

=OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1)

Download the Sample File

Download a sample file for dependent data validation

Download a sample file for dependent data validation with Dynamic Lists

Excel Data Validation -- Dependent Dropdowns from a Sorted List

You can limit the choices in an Excel Data Validation list, by using named ranges and the INDIRECT function, as explained here: Data Validation -- Create Dependent Lists

Another method is to use the OFFSET function, to extract items from a sorted list, as described below. In this example, a region is selected in one column, and the customers in that region will appear in the data validation list in the adjacent cell.

Set up the Workbook

Two worksheets are required in this workbook.

Delete all sheets except Sheet1 and Sheet2

Rename Sheet1 as ValidationSample

Rename Sheet2 as ValidationLists

On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:

In cells A1:B20 type a list of Regions and Customers Note: This list must be sorted by Region

 

Page 9: Latihan Excell

In cells D1:D4 type a list of Regions

Name the following ranges (there are Naming instructions here: Name a Range):

Name cell A1 as RegionStart

Name column A as RegionColumn

Name column B as CustColumn

Name range D2:D4 as RegionList    

 

Create a Data Validation Dropdown List

On the ValidationSample sheet, type the headings Region and Customer, in cells B1 and C1.

The next step is to create the Region data validation dropdown lists in column B.

Cells B2:B10 have data validation lists with the source RegionList. When a cell in this range is selected, a dropdown list of Regions is available.

The formula for the list is:=IF(C2="",RegionList,INDEX(RegionColumn,MATCH(C2,CustColumn,0)))

The complete region list is shown if no customer has been selected. However, if a customer has been selected in the adjacent cell, only that customer's region is shown in the Region dropdown list.

There are detailed instructions for creating data validation lists here: Data Validation -- Introduction   

Create a Dependent Dropdown List

The next step is to create the dependent data validation dropdown lists in column C.

In cell B2, select Ontario from the dropdown list. (If the cell is left empty, an error message may occur, when creating the dependent validation in column C.)

Select cells C2:C10

Choose Data | Validation

From the Allow dropdown, choose List

In the Source box, type the following formula:

=OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,B2),1)

 

Page 10: Latihan Excell

The OFFSET function has the following arguments:

We want the OFFSET function to return a reference to the range of cells that contains the Ontario customers.Reference:  In our formula, the reference is RegionStart, cell A1 on the ValidationLists sheet.Rows:  How many rows down from the reference cell should our range start? The MATCH function finds the first instance of Ontario in the RegionColumn, in row 6. We subtract 1 from this number, because the starting cell is in row 1.Columns:   We want a range that is 1 column to the right of the RegionStart reference.Height:  The COUNTIF function counts the number of times that region is entered in the RegionColumn. There are 9 customers in the Ontario region. Width:  We want a range that is 1 column wide

Click OK   

Test the Validation

Select cell C2

Click the data validation dropdown arrow

A list of Ontario customers is displayed.

Download the zipped sample file

 

Excel Data Validation -- Create Dependent Lists With INDEX

As an alternative to using INDIRECT to create dependent Excel data validation lists, you can use the non-volatile INDEX function.

In this example, just 4 dynamic range names are used. Of these, 3 are used to create the basic framework of the method.

The fourth permits any number of subsidiary lists to be created to act as Dependent dropdown lists from the entry in the previous column, without having to define individual names for each list.

 

Create Named Lists

In a new Workbook, rename Sheet 1 as Data Entry

 

Page 11: Latihan Excell

Rename Sheet2 as Lists

Enter Headings on the Data Entry sheet in cells A1:D1 -- Region, Country, Area and City

Data Entry Sheet

On the Lists sheet in cell A1 add the headings Regions.

In cells A2:A5 enter APAC, MEA, Europe and Americas. (These first 2 headings represent Asia & Pacific, Middle East & Africa)

Lists Sheet

 

Create the Dynamic Ranges

Select sheet Lists and then go through the following processes to create the 4 Dynamic range names that are required:

Master - which will be used for the validation in column A of Data Entry

ValData - which will define the range on Sheet Lists that contains the Validation Data

Counter - which will be used to determine the length of each Validation List

UseList - which is used for all columns where validation is required on Sheet Data Entry, apart from column A

 

 

Create the First Dynamic Range

Our first List is going to be called Master

Choose Insert | Name | Define

Type a name for the first range -- Master

In the Refers To box, enter an Index formula that defines the range size, based on the count of items in column A of the Lists sheet:   =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))

This will give a dynamic List starting with cell A2 and extending for as many entries as we create in column A

 

Add More Headings and Entries to Lists  

Page 12: Latihan Excell

Enter all the Region names as headings in cells B1:E1 of the Lists Sheet.

Then, under each Region heading, enter one or more countries from that region. (See screen shot below, or see the sample file)

 Create the ValData Dynamic Range

On the Lists sheet is the range of data that will be used in all validations for the Data Entry sheet. Instead of referring to the sheet name as range for this data, which will grow as more validations are added, you'll create a Dynamic range.

Choose Insert | Name | Define

Type a name for the range -- ValData

In the Refers To box, enter an Index formula that defines the range size, choosing say 100 rows and a count of the columns on the sheet =Lists!$A$2:INDEX(Lists!$1:$100,100,COUNTA(Lists!$1:$1))

This will give a dynamic List starting with cell A2 and extending for 100 rows and as many entries as we create in Row 1

Click the Add button

The choice of 100 rows is arbitrary, and should just be sufficient to contain whatever is going to be the longest of your lists.

Next, because each individual List can have a different number of values, we need to create a Counter.

 

 

Create a Counter

The Counter is a variable which defines the length of each individual List. It is dynamic, and will alter dependent upon which column is being used, the column being returned by the Match function. A dynamic range will be created for the Counter.

Note: The range is relative, and refers to the cell to the left (A2) to determine its values, so it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.

On the Data Entry sheet, type Europe in cell A2 (this is a temporary value, and you can delete it later)

 

Page 13: Latihan Excell

On the Data Entry sheet, select cell B2

Choose Insert | Name | Define

Type a name for the range -- Counter

In the Refers To box, enter an Index formula that defines the range size, based on the count of numbers in the relevant column: =COUNTA(INDEX(ValData,,MATCH('Data Entry'!A2,Lists!$1:$1,0)))

Click the Add button

 Create the UseList Dynamic Range

This is the universal list name that will apply to all lists that are added to the sheet other than the Master list as previously described.

As new columns are added to the Lists sheet, their relevant used range is automatically dynamically created, and using UseList as the data source will apply to the whole of the validation areas on sheet Data Entry.

Note: Because the range is relative, and refers to the cell to the left (A2) to determine its values, it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.

Place your cursor in cell B2 of Data Entry sheet.

Choose Insert | Name | Define

Type a name for the range -- UseList

In the Refers To box, enter the following formula =INDEX(ValData,1,MATCH('Data Entry'!A2,Lists!$1:$1,0)): INDEX(ValData,Counter,MATCH('Data Entry'!A2,Lists!$1:$1,0))

Click the Add button

Why the Cursor Placement is Important

ValData has previously been defined as a range beginning in Row 2 of the Lists sheet.

The UseList formula indexes the Valdata range, with 1 fixed value for the starting row element, in the first part of the formula and 1 dynamic value for the ending row element, in the second part of the formula.

The formula has 1 relative value for the column element of the Index in both parts of the formula.

Row in the first part is determined by the 1 in (Valdata,1, ...) and will therefore refer to Row 1 of ValData (therefore, row 2 of the Lists sheet).

Row in the second part is determined by Counter in (Valdata,Counter, ...) , and Counter is a previously defined count, which will vary according to the number of entries in the

Page 14: Latihan Excell

validation list for the relative column.

Column is determined by the Match of whatever is in the cell to the left of the cell using this dynamic range, MATCH('Data Entry'!A2,Lists!$1:$1,0) with the values of the List headings held in row 1 of the Lists sheet.

That is why it is so important to place the cursor in the correct starting cell when creating this named range.

 Setup the Validation cells for Column A on Data Entry

Now that you've defined the dynamic ranges, the next step is to set up the validation ranges on the Data Entry sheet.

On the Data Entry sheet, select cells A2:A12 (or as many rows as you require)

Choose Data | Validation

Choose Allow | List

In the Source box, type =Master

Click OK

 

 

Setup the Validation cells for all other columns on Data Entry

The next step is to set up the remaining validation ranges on sheet Data Entry

On the Data Entry sheet, select cells B2:D12 (or as many rows as you require)

Choose Data | Validation

Choose Allow | List

In the Source box type =UseList

Click OK

 

 

The finished Data Entry sheet with sample Data  

 

 

 

Page 15: Latihan Excell

 Expanding Columns on sheet Lists

You can add as many extra columns as you wish on sheet Lists. Each column has to have a heading which is a member of one of the other columns on the sheet, so that the new column added will be Dependent upon that heading.

For example, you could add a heading for US, and type a list states in that column. Next, you could add the state names as headings in row 1, and type a list of city names for each state.

You do not need to define any new names, as the Uselist dynamic range name will automatically deal with that for you.

 

 

Download a sampleExcel data validation file

 

 

Download a sample Excel data validation file

 

   You can limit the choices in an Excel Data Validation list, hiding items that have been previously selected. For example, if you are assigning employees to a shift, you want to avoid assigning the same employee twice.

In the dropdown list, the names that have been used are removed.

Set up the Main Table

Start by setting up the table in which you want to use the Excel Data Validation. In this example, the worksheet is named 'Schedule' and the range A1:C7 is being used.

Column B will have Data Validation applied.Create the List of Items

Create a list which contains the items you want to see in the Excel data validation dropdown list. Here, the employee names have been entered in cells A1:A6, on a sheet named 'Employees'

Create the Validation List

A) Enter a formula to calculate if a name has been used.

1. On the Employees sheet, in cell B1, enter the following formula:

=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())

Page 16: Latihan Excell

2. Copy the formula down to cell B6.

This formula counts the occurrences of "Bert" in cells B2:B7 on the Schedule worksheet. If the count is greater than or equal to 1, the cell will appear blank. Otherwise, the row number will be displayed.

B) Create the list of unused names

The next step is to create a multi-cell array formula which will move any blank cells to the end of the list.

Select cells C1:C6

Enter the following array formula (the formula is long, and should be all on one line)

=IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"",    INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6))))))

3. Press Ctrl+Shift+Enter to enter the array formula in cells C1:C6

 Single-Cell Formula Alternative

If you'd prefer a single-cell formula (easier to edit), you could use this formula, also by Daniel.M. He recommends it for small ranges (<=200 cells):

Select cell C1

Enter the following formula (the formula is long, and should be all on one line)

=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

Press Enter

Copy the formula down to row 6

 

Name the Excel Data Validation List

1. Choose Insert>Name>Define2. In the Names in workbook box, type a one-word name for the range, e.g. NameCheck.3. In the Refers to box, type the following formula (all on one line):

=OFFSET(Employees!$C$1,0,0,COUNTA(Employees!$C$1:$C$6)-COUNTBLANK(Employees!$C$1:$C$6),1)

4. Click OK

 

Page 17: Latihan Excell

Apply the Excel Data Validation

1. Select the cells in which you want to apply data validation using the Excel data validation list2. From the Data menu, choose Validation.3. From the Allow dropdown list, choose List4. In the Source box, type an equal sign and the list name, for example: =NameCheck 5. Click OK.  

Test the Excel Data Validation

The dropdown list in column B shows only the names that haven't been used. Other names have been removed.

To download a zipped sample file, click here: Data Validation -- Hidden Items -- Sample

Dependent Data Validation

This technique can be modified, and used with Dependent Data Validation, as in the zipped sample workbook found here: Hide Previously Used - Dependent

 

 

Multiple Column Data Validation

This technique can be modified, and used with multiple columns. For example, employee names are removed from data validation dropdown lists after they've been assigned to a daily task.

To download this example, go to the Excel Sample Spreadsheets page, and in the Data Validation section, look for DV0016 - Assign Employees to Single Task Per Day

 

Show Data Validation Messages

Data Validation Messages Video: Create an Input Message Create an Input Message Create an Error Alert

Data Validation Messages

With the options available in data validation, you can display messages to give instructions to the people who use your spreadsheet.

An Input Message can be displayed when a cell is selected.

An Error Alert can be displayed if invalid data is entered in a cell.

Page 18: Latihan Excell

Video: Create an Input Message

To see the steps for creating an input message, watch this short video tutorial. The written instructions are below the video.

Create an Input Message

To help people know what data should be entered in a cell, you can set up an Input Message that is displayed when the cell is selected.

Follow these steps to show a short message when a cell is selected.

1. Select the cells in which you want to apply data validation2. On the Ribbon, click the Data tab, and click Data Validation3. (optional) On the Settings tab, choose the data validation settings 4. Click on the Input Message tab, and add a check mark to Show input message when cell is

selected

5. Type your message heading text in the Title box. This text will appear in bold print at the top of the message.

6. Type a short message in the Input message box. The limit is 256 characters

7. Click OK or follow the steps below to add an Error Alert. 8. Now, when you click on the cell, the Input Message will appear.

Create an Error Alert

When you add data validation to a cell, the Error Alert feature is automatically turned on. It blocks the users from entering invalid data in the cell.

Page 19: Latihan Excell

 

Or, change the type of Error Alert, by following the instructions below.

1. Select the cells in which you want to apply data validation2. On the Ribbon, click the Data tab, and click Data Validation3. On the Settings tab, choose the data validation settings 4. Click on the Error Alert tab, and add a check mark to Show error alert after invalid data is

entered .

1. 2. c) Choose an Error Alert Style from the dropdown list.

1. Stop: This prevents the entry of invalid data. If the Retry button is clicked, the invalid entry is highlighted, and can be overtyped. If the Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.The user cannot leave the invalid entry in the cell

2. 3. Warning: This discourages the entry of invalid data.

If the Yes button is clicked, the invalid entry is accepted, and the next cell is selected. If the No button is clicked, the invalid entry is highlighted, and can be overtyped.If the Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.The user can choose to leave the invalid entry in the cell.

3. 1. Information: This announces the entry of invalid data.

If the OK button is clicked, the invalid entry is accepted, and the next cell is selected. If the Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.The user can choose to leave the invalid entry in the cell.

4. 5. d) Type your message heading text in the Title box. This text will appear in bold

print at the top of the message.f) Type a short message in the Error message box. The limit is 256 charactersg) Click OK

Turn Error Alert Off

Page 20: Latihan Excell

You can turn Error Alert off, to allow people to enter invalid data. For example, if the data validation cell contains a dropdown list, turn off the Error Alert to allow users to type items that are not in the list.

To turn off Error Alert:

1. Select the cells in which you want to turn off Error Alert2. On the Ribbon, click the Data tab, and click Data Validation3. Click on the Error Alert tab, and remove the check mark from Show error alert after

invalid data is entered

Data Validation -- Display Input Messages in a Text Box

Set up the Workbook     Create a Data Validation Dropdown List      Add the Text Box    Name the Text Box    Add the Code    Test the Code    Show Longer Messages Download the Sample File More Data Validation Tutorials

You can use a Data Validation Input Message to display a message when a cell is selected. However, the font can't be changed, nor can message box size be controlled.

To overcome these limitations, you can create a text box to display the message, and use programming to make it appear if cells that contain a data validation Input Message are selected.

Note: The technique shown here will only show the input message text, from the data validation setup window. If you want to show a longer message, download the Long Message sample file.

Set up the Workbook

Two worksheets are required in this workbook.

1. Delete all sheets except Sheet1 and Sheet22. Rename Sheet1 as ValidationSample3. Rename Sheet2 as ValidationLists

On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:

Tip: Use the AutoFill feature to create the lists

1. In cells A1:A7 type a list of weekdays 2. In cells C1:C12 type a list of months

Name the lists (there are Naming instructions here: Name a Range):

Page 21: Latihan Excell

1. Name the range A1:A7 as DayList 2. Name the range C1:C12 as MonthList    

Create a Data Validation Dropdown List

The next step is to create the data validation dropdown lists.

There are detailed instructions here: Data Validation -- Introduction 

Cells C5:C15 have data validation lists with the source DayList. When a cell in this range is selected, a dropdown list of weekdays is available. Include an Input Message, as described here:  Display Messages to the User . The message used in the sample file is:Title: Activation Day Message: Please select the weekday in which the product was originally purchased, not the weekday in which it was first used. If you are not sure, please leave this cell blank, then check with your manager, and fill in the weekday later.

Cells D5:D15 have data validation lists with the source MonthList. When a cell in this range is selected, a dropdown list of months is available. Include an Input Message. The message used in the sample file is:Title: Activation Month Message: Please select the month in which the product was originally purchased, not the month in which it was first used. If you are not sure, please leave this cell blank, then check with your manager, and fill in the month later. 

Add the Text box

1. If the Drawing Toolbar is not visible, display it (View | Toolbars)2. On the Drawing Toolbar, click the Text Box tool.

3. Draw a text box at the top of the worksheet, large enough to hold your messages.4. Type some sample text, e.g. "This is the Input Message"

5. Format the text box with the font and font size you'd like.6. Right-click on the border of the text box, and choose Format Text Box7. Select the Properties tab8. Select Don't move or size with cells9. Remove the check mark from Print object10. Click OK  

Name the Text box

1. Click on the border of the text box, to select it2. Click in the Name Box, at the left of the Formula Bar3. Type the text box name:   txtInputMsg4. Press the Enter key  

Add the Code

Visual Basic for Applications (VBA) code is required to make the text box appear when you select a cell that contains a data validation input message. It copies the data validation Input Message and Input Title to the text box, and makes the title bold.

Copy the following code, and follow the instructions below, to add it to the workbook:

'=========================================Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim strTitle As StringDim strMsg As StringDim lDVType As LongDim sTemp As ShapeDim ws As Worksheet

Application.EnableEvents = FalseSet ws = ActiveSheetSet sTemp = ws.Shapes("txtInputMsg")On Error Resume NextlDVType = 0lDVType = Target.Validation.TypeOn Error GoTo errHandler

If lDVType = 0 Then sTemp.TextFrame.Characters.Text = "" sTemp.Visible = msoFalse Else

Page 22: Latihan Excell

If Target.Validation.InputTitle <> "" Or _ Target.Validation.InputMessage <> "" Then strTitle = Target.Validation.InputTitle & Chr(10) strMsg = Target.Validation.InputMessage With sTemp.TextFrame .Characters.Text = strTitle & strMsg .Characters.Font.Bold = False .Characters(1, Len(strTitle)).Font.Bold = True End With sTemp.Visible = msoTrue Else sTemp.TextFrame.Characters.Text = "" sTemp.Visible = msoFalse End If End If

errHandler: Application.EnableEvents = True Exit Sub

End Sub '====================================

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code.

2. Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing. 3. Choose File | Close and Return to Microsoft Excel.

Test the Code

1. Select one of the cells that contains a data validation input message.2. The text box will appear3. Select a cell that doesn't contain a data validation in put message4. The text box disappears.

Show Longer Messages

The data validation input message is limited to 255 characters, so the technique show above is subject to that limit too.

If you need to show longer messages in the text box, you can download the Longer Message sample file, from the Download section, below.

This example uses most of the code from the original example, and adds a new feature. There is a worksheet where you can enter a list of Input Message Titles, and the message that you want to display for each of those titles.

When you click on a data validation cell in the Longer Message sample file, the Input Message title is used as a lookup in the messages table.

If there is an entry for that title, the Additional Message text is added to the end of any existing Input Message text. Then, the entire text string is shown in the text box at the top of the worksheet.

So, with this version of the sample file, you can display much longer messages when someone clicks on a data validation cell. You'll have to adjust the size of the text box, to fit the longer messages, or reduce the font size, so more characters will fit.

Download the Sample File

For short messages (the technique described in the instructions above), download the zipped sample file. This file is in Excel 2003 format (xls), and contains macros.

For longer messages, download the Long Message sample file. This has an extra sheet where you can enter longer messages, and they will be displayed in a text box, based on the data validation input message's Title. This file is in Excel 2007 format (xlsm), and contains macros.

Page 23: Latihan Excell

Excel Data Validation List from Another Workbook

Create the Excel Data Validation Source ListCreate a Reference to the Source ListCreate the Dropdown ListCreate a Dynamic Range from Another Workbook

You can use a list from another workbook as the source for a Data Validation dropdown list.

For data validation to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists. You could create the list in a workbook that is always open, but hidden, such as the Personal.xls workbook.

The following instructions are for Excel 2003 and earlier versions. On the Contextures blog there are instructions for using a list from another workbook in Excel 2007. 

Create the Excel Data Validation Source List

The following instructions assume you have a workbook named DataValWb.xls, which contains a range named CustName.

For instructions on creating a named range, refer to Naming Ranges .

 

 

Create a Reference to the Source List

1. Open the workbook that contains the source list -- DataValWb.xls in this example. 2. Open the workbook in which you wish to use the list in Data Validation.3. Choose Insert>Name>Define4. Type a name for the List, e.g. MyList5. In the refers to box, type a reference to the named range. Start with an equal sign, then the

workbook name and an exclamation mark, followed by the range name, e.g. =DataValWb.xls!CustName

6. Click OK

 

Create the Dropdown List

1. Select the cells in which data validation will be set. 2. Choose Data>Validation 3. In the Allow box, choose List 4. In the Source box, type the list name, preceded by an equal sign, e.g.: =MyList 5. Click OK

Watch the Data Validation Video

To see the steps for creating the data validation drop down from a list in another workbook, you can watch this short Excel video tutorial.

 

Create a Dynamic Range from Another Workbook

You can create a dynamic range that refers to a dynamic range in another (open) workbook.

1. Create and save a workbook (MyLists.xls, in this example)2. Enter a list of names in cells A1:A10 on Sheet 1.3. To create a dynamic range, choose Insert|Name|Define

Use Employees as the range name, and the following formula:     =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))

4. Keep MyLists.xls open, and create and save a new workbook (Schedule.xls)5. In Schedule.xls, create a range named EmployeeList with this formula:

     =MyLists.xls!Employees 6. In cell A1 of sheet1, enter the following formula:

     =EmployeeList7. Copy the formula down to row 200 (or any row beyond the length of the dynamic range in

MyList.xls). Note: many of the rows will contain a #VALUE! error. 8. In Schedule.xls, create another range, with the name NoErrors, and the formula:

     =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A$1:$A$300,"#VALUE! "))      (all one line)

8. Use NoErrors as the source for your Data Validation list.

Page 24: Latihan Excell

Use the Data Validation List

To use the data validation drop down lists, both workbooks must be open.

1. Open both workbooks -- the one that contains the drop down lists, and the workbook that contains the original source list.

2. Select an item from the data validation drop down list.

 

Data Validation Examples Criteria

Whole Number Decimal List Date

o Date Video o Create Date Range Validation o Date Validation Formula

Time Text Length More Data Validation Tutorials

Use Data Validation to allow specific entries in cells on the worksheet. For example, you can limit the cells to whole numbers, or text of specific length.

Listed below are the different settings that you can allow, with an example of each setting.

Whole Number

If you allow Whole numbers, you can set or exclude a range of numbers, or specify a minimum number or maximum number.

To set the allowed values, you can

1. Type the values into the Data Validation dialog box, OR2. Refer to cells on the worksheet, OR3. Use formulas to set the values. For example, in the screen shot shown here, the MAX and MIN

functions are used to set the minimum and maximum values.

Decimal

If you allow Decimals, you can set or exclude a range of numbers, or specify a minimum number or maximum number.

1. Type values into the Data Validation dialog box, OR2. Refer to cells on the worksheet, OR3. Use formulas to set the values

List

You can create a drop down list of items to select in a cell, or group of cells. See the examples and instructions here: Data Validation

Date

If you allow Dates, you can set or exclude a range of dates, or specify a minimum date or maximum date.

1. Type the dates into the Data Validation dialog box, OR2. Refer to cells that contain dates on the worksheet, OR3. Use formulas to set the dates

Date Video

To see the steps for creating data validation for a date range, please watch this short video tutorial. The written instructions are below the video.

Create Date Range Validation

To set up date validation, you can enter a start and end date on the worksheet. In this example, those

Page 25: Latihan Excell

dates are entered in cells E1:E2.

Next, follow these steps to set up the data validation:

1. Select the cells where the data validation will be applied – cells B2:B6 in this example. 2. On the Excel Ribbon, click the Data tab, and click Data Validation 3. From the Allow drop down, select Date 4. From the Data drop down, select Between 5. Click in the Start Date box, and click cell E1, where the Start Date is entered. 6. Press the F4 key, to change the cell reference to an absolute reference -- $E$1 7. Click in the End Date box, and click cell E2, where the End Date is entered. 8. Press the F4 key, to change the cell reference to an absolute reference -- $E$2 9. Click OK, to close the Data Validation window.

Use a Date Formula

Instead of entering dates on the worksheet, you could use a formula to set the start and end dates.

For example, enter the TODAY function as the Start date: =TODAY()

For the End date, use a formula to calculate 6 days from the current date: =TODAY() + 6

Time

If you allow Times, you can set or exclude a range of numbers, or specify a minimum number or maximum number.

1. Type the times into the Data Validation dialog box, OR2. Refer to cells that contain times on the worksheet, OR3. Use formulas to set the times

The TIME formula in this example uses the current time as an end time, so all entries have to be before the current time:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Text Length

If you allow Text length, you can set or exclude a range of lengths, or specify a minimum number or maximum length.

1. Type the textlength into the Data Validation dialog box, OR2. Refer to cells that contain a value on the worksheet, OR3. Use formulas to set the text length

Page 26: Latihan Excell

Excel Data Validation Examples Custom Criteria Prevent Duplicates With Data ValidationLimit the TotalNo Leading or Trailing SpacesNo Spaces in TextProhibit Weekend Dates

Prevent Duplicates With Data Validation

You can use Excel Data Validation to prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10, and you can prevent the same number from being entered twice..

1. Select cells B3:B102. Choose Data|Data Validation3. Choose Allow: Custom4. For the formula in this example,

we use the COUNTIF function to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The formula's result must be 1 or 0:   =COUNTIF($B$3:$B$10,B3) <= 1

   

Limit the Total

Prevent the entry of a value that will cause a range to exceed a set total. In this example, the total budget cannot exceed $3500. The budget amounts are in cells C3:C7, and the formula checks the total in those cells

1. Select cells C3:C72. Choose Data|Data Validation3. Choose Allow: Custom4. For the formula, use SUM to total

the values in the range $C$3:$C$7. The result must be less than or equal to $3500:   =SUM($C$3:$C$7) <= 3500

  

No Leading or Trailing Spaces

You can prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text, and any extra spaces within the text.

The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell.

1. Select cell B22. Choose Data|Data Validation3. Choose Allow: Custom4. For the formula, enter:

   =B2 = TRIM(B2)

  

No Spaces in Text

Thanks to Jerry Latham for this example.

You can prevent users from adding ANY spaces in a text string. The SUBSTITUTE function replaces each space character – " " – with an empty string – ""

The formula in this example checks that the entry in cell B3 is equal to the entry without spaces.

1. Select cell B32. On the Ribbon, click the Data tab,

then click Data Validation

  

Page 27: Latihan Excell

3. Choose Allow: Custom4. For the formula, enter:

   =B3=SUBSTITUTE(B3," ","")

No Spaces in Cell

You can prevent users from adding ANY spaces in the cell -- whether the entry is text, numbers, or a combination of both. Here are two formulas that check for spaces.

Example 1

Thanks to Jerry Latham for this example.

The LEN function counts the number of characters entered in cell B3, and compares that to the number of characters after SUBSTITUTE removes the space characters.

1. Select cell B32. On the Ribbon, click the Data tab,

then click Data Validation3. Choose Allow: Custom4. For the formula, enter:

   =LEN(B3)=LEN(SUBSTITUTE(B3," ",""))

  

 

Example 2

The FIND function looks for the space character – " " – and the ISERROR function result is TRUE, if the space character is not found.

The formula in this example checks that the entry in cell B3 is equal to the entry without spaces.

1. Select cell B32. On the Ribbon, click the Data tab,

then click Data Validation3. Choose Allow: Custom4. For the formula, enter:

   =ISERROR(FIND(" ",B3))

Restrict Date Range

Instead of limiting dates to a specific date range, you can limit to within a specific number of days from the current date.

In this example, the earliest start date allowed is 60 days prior to the current date:

=TODAY()-60

The latest end date allowed is 60 days after the current date:

=TODAY()-60

Prohibit Weekend Dates

Prevent entry of dates that fall on Saturday or Sunday. The WEEKDAY function returns the weekday number for the date entered, and values of 1 (Sunday) and 7 (Saturday) are not allowed.

1. Select cell B22. Choose Data|Data Validation3. Choose Allow: Custom4. For the formula, enter:

  

=AND(WEEKDAY(B2) <> 1, WEEKDAY(B2) <> 7)

Data Validation Tips and Quirks

Refer to a Source List on a Different Worksheet Use Dynamic ListsDrop Down List Opens With Blank Selected Watch the Dynamic Range Video Data Validation Font Size and List LengthData Validation List With Symbols Data Validation Dropdowns and Change Events Data Validation Dropdown Arrows Not Visible 

Page 28: Latihan Excell

Invalid Entries are Allowed    -- Video: Ignore Blank in Data ValidationData Validation on a Protected Sheet Data Validation Dropdowns are Too Wide  Make the Dropdown List Temporarily WiderMake the Dropdown List Appear Larger   -- Zoom in when specific cell is selected   -- Zoom in when specific cells are selected   -- Zoom in when any cell with data validation is selected

Refer to a Source List on a Different Worksheet

When you try to create an Excel data validation dropdown list, and refer to a source list on a different worksheet, you may see an error message: "You may not use references to other worksheets or workbooks for Data Validation criteria."

To avoid this problem, name the list on the other worksheet, then refer to the named range, as described here:  Excel Data Validation 

If the list is in a different workbook, you can use the technique described here: Use a List from Another Workbook

Use Dynamic Lists

Some lists change frequently, with items being added or removed. If the list is the source for a Data Validation dropdown, use a dynamic formula to name the range, and the dropdown list will be automatically updated.

For instructions, view this page:   Create a Dynamic Range

Drop Down List Opens With Blank Selected

When you click the arrow to open a drop down list, the selection might go to a blank at the bottom of the list, instead of the first item in the list.

To download the sample file, click here: Remove Blanks With Dynamic Range Sample File

Why does this happen, and how can you prevent it?

In the example shown above, the drop down list is based on a range named Products. The person who set up the list left a few blank cells at the end, where new items could be added.

If there's a blank cell in the source list, and the cell with the data validation list is blank, the list will open with the blank entry selected.

Prevent the Problem

To prevent this, either enter a default value in the data validation cell, or remove the blanks from the source list.

Create a Default Item at Top of List

Or, make " --Select--" the top item in the Product list, and set up the worksheet with "--Select--" entered in each product cell, as the default entry.

Page 29: Latihan Excell

Remove Blanks With Dynamic Named Range

So, in this example, you could change the Products list to a dynamic range, which will adjust automatically when items are added or removed.

The OFFSET formula used in this example is:

=OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)

Watch the Dynamic Range Video

To see the steps for setting up a dynamic named range, please watch this short video tutorial.

Data Validation Font Size and List Length

The font size in a data validation list can't be changed, nor can its default list length, which has a maximum of eight rows.

If you reduce the zoom setting on a worksheet, it can be almost impossible to read the items in the dropdown list, as in the example below.

One workaround is to use programming, and a combo box from the Control Toolbox, to overlay the cell with data validation. If the user double-clicks on a data validation cell, the combobox appears, and they can choose from it. There are instructions here.

Data Validation List With Symbols

Unfortunately, you can't change the font in a data validation list, as you saw in the previous section. However, you can use symbol characters from the Tahoma font, such as arrows, circles, and squares.

To create a list of symbols:

1. On the worksheet, select a cell where you want to start the list of symbols2. Press the Alt key, and on the number keypad, type a number for the symbol that you want to insert. A few

examples are shown in the list below, and you can experiment to find other symbols.Note: To see all the graphic characters, go to the Code Page 437 entry in Wikipedia.

Page 30: Latihan Excell

3. Press Enter, and enter other symbols in the cells below. In the list shown above, the Alt key was used with numbers 30, 29 and 31, to create a list with up and down arrows, and a two-headed arrow.

To create a drop down list with the symbols:

1. Select the cell where you want the drop down list2. On the Ribbon's Data tab, click Data Validation3. From the Allow drop down, select List4. Click in the Source box, and on the worksheet, select the cells with the list of symbols, then click OK

To see the example, you can download the sample file: Data Validation List With Symbols

Data Validation Dropdowns and Change Events

In Excel 2000 and later versions, selecting an item from a Data Validation dropdown list will trigger a Change event. This means that code can automatically run after a user selects an item from the list.

To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList.zip file.

In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. In this version, you can add a button to the worksheet, and run the code by clicking the button. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList97.zip file.

Another option in Excel 97 is to use the Calculate event to run the code. To do this, refer to the cell with data validation in a formula on the worksheet, e.g. =MATCH(C3,CategoryList,0). Then, add the filter code to the worksheet's Calculate event. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList97Calc.zip file.

Data Validation Dropdown Arrows Not Visible

Occasionally, data validation dropdown arrows are not visible on the worksheet, in cells where you know that data validation lists have been created. The video shows the most common reasons for missing arrows, and the written instructions for fixing the problems are below the video.

Watch the Missing Arrows Video

Active Cell OnlyOnly the active cell on a worksheet will display a data validation dropdown arrow. To mark cells that contain data validation lists, you can colour the cells, or add a comment.

If you require visible arrows for all cells that contain lists, you can use combo boxes instead of data validation, and those arrows will be visible at all times. To create a combo box, choose View|Toolbars, and select either the Control Toolbox or the Forms toolbar.

Hidden ObjectsIf objects are hidden on the worksheet, the data validation dropdown arrows will also be hidden. To make objects visible, choose Tools|Options, and on the View tab, under Objects, select Show all.

Dropdown OptionIn the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on:

1. Select the cell that contains a data validation list 2. Choose Data|Validation3. On the Settings tab, add a check mark to In-cell dropdown4. Click OK

Freeze PanesIn Excel 97, if a Data Validation dropdown list is in a frozen pane of the window, the dropdown arrow does not appear when the cell is selected. As a workaround, use Window|Split instead of Window|Freeze Panes

Page 31: Latihan Excell

This problem has been corrected in later versions.

Without frozen panes

 With frozen panes

CorruptionIf none of the above solutions explains the missing dropdown arrows, the worksheet may be corrupted. Try copying the data to a new worksheet or workbook, and the dropdown arrows may reappear.

Or, try to repair the file as you open it:

1. In Excel, choose File | Open2. Select the file with the missing data validation arrows3. On the Open button, click the arrow4. Click Open and Repair5. When prompted, click Repair. 

Invalid Entries Are Allowed

Although you have created data validation dropdown arrows on some cells, users may be able to type invalid entries. The following are the most common reasons for this.

Error AlertIf the Error Alert is turned off, users will be able to type any entry, without receiving an error message. To turn the alert on:

1. Select the cell that contains a data validation list 2. Choose Data|Validation3. On the Error Alert tab, add a check mark to the Show error alert after invalid data is entered box.4. Click OK

Blank Cells in Source ListIf the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this:

1. Select the cell that contains a data validation list 2. Choose Data|Validation3. On the Settings tab, remove the check mark from the Ignore blank box.4. Click OK

Note: If the source list contains blank cells, and is a range address, e.g. $A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Video: Ignore Blank in Data Validation

Watch this short Excel tutorial video on the potential problems when Ignore Blank is turned off, and the Circle Invalid Data feature is used. 

 

Data Validation on a Protected Sheet

In Excel 2000 and earlier versions, you can change the selection in a data validation dropdown, if the list is from a range on the worksheet. If the list is typed in the data validation dialog box, the selection can't be changed.

In Excel 2002 and later versions, neither type of dropdown list can be changed if the cell is locked and the sheet is protected.

This MSKB article has information on the previous behaviour:

XL97: Error When Using Validation Drop-Down List Box http://support.microsoft.com/default.aspx?id=157484

Make the Dropdown List Temporarily Wider

Page 32: Latihan Excell

The Data Validation dropdown is the width of the cell that it's in, to a minimum of about 3/4". You could use a SelectionChange event to temporarily widen the column when it's active, then make it narrower when you select a cell in another column.

For example, with Data Validation cells in column D:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 4 Then Target.Columns.ColumnWidth = 20 Else Columns(4).ColumnWidth = 5 End If End Sub

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code. 2. Copy the code, and paste it onto the code module. 3. Change the column reference from 4 to match your worksheet.

Make the Dropdown List Appear Larger

In a Data Validation dropdown list, you can't change the font or font size.

To make the text appear larger, you can use an event procedure (three examples are shown below) to increase the zoom setting when the cell is selected. (Note: this can be a bit jumpy)

Or, you can use code to display a combobox, as described in the previous section.

Zoom in when specific cell is selected

If cell A2 has a data validation list, the following code will change the zoom setting to 120% when that cell is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code. 2. Copy the code, and paste it onto the code module. 3. Change the cell reference from $A$2 to match your worksheet.

Zoom in when specific cells are selected

If several cells have a data validation list, the following code will change the zoom setting to 120% when any of those cells are selected. In this example, cells A1, B3 and D9 have data validation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If End Sub

Zoom in when any cell with a data validation list is selected

The following code will change the zoom setting to 120% when any cell with a data validation list is selected.

Page 33: Latihan Excell

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lZoom As Long Dim lZoomDV As Long Dim lDVType As Long lZoom = 100 lZoomDV = 120 lDVType = 0

Application.EnableEvents = False On Error Resume Next lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType <> 3 Then With ActiveWindow If .Zoom <> lZoom Then .Zoom = lZoom End If End With Else With ActiveWindow If .Zoom <> lZoomDV Then .Zoom = lZoomDV End If End With End If

exitHandler: Application.EnableEvents = True Exit SuberrHandler: GoTo exitHandlerEnd Sub

More Data Validation Tutorials

Data Validation Basics Excel 2003Create Dependent Drop Down ListsDependent Dropdowns from a Sorted List  Dependent Lists With INDEX   Hide Previously Used Items in a Dropdown ListDisplay Data Validation Messages to the UserDisplay Input Messages in a Text Box Use a Data Validation List from Another Workbook Data Validation Criteria Examples Data Validation Custom Criteria ExamplesData Validation TipsData Validation DocumentationData Validation With Combo Box     Data Validation Combo Box - Named Ranges Data Validation Combo Box -- Click Data Validation - Add New Items  

Excel -- Data Validation -- Documentation

Document a Worksheet's Data Validation: Text FileDocument a Worksheet's Data Validation: Worksheet

Download the zipped sample Data Validation Documenter file

Document a Worksheet's Data Validation: Text File

The following procedure creates a text file with a list of the active worksheet's data validation. For example:

  A11 Whole Number Less Than or Equal to 3

  D4 List Yes,No

  D5 List =DaysList

  D8 Text Length Less Than 5

  D11 Custom =AND($A$1<>"",$A$3<>"")

Thanks to J.E. McGimpsey for generously sharing his code.

Download the zippedsample Data Validation

Documenter file

Sub DataValDocumenter() 'adapted from code posted by J.E. McGimpsey 2005-02-03 'http://www.mcgimpsey.com/excel/index.html Dim sVal(0 To 2) As Variant Dim rValidation As Range Dim rCell As Range Dim nFile As Long Dim sC As String Dim strDV As String sC = vbTab On Error Resume Next Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If Not rValidation Is Nothing Then nFile = FreeFile Open "test.txt" For Output As #nFile For Each rCell In rValidation With rCell.Validation sVal(0) = Choose(.Type + 1, "Input Only", _ "Whole Number", "Decimal", "List", "Date", _ "Time", "Text Length", "Custom") sVal(1) = .Formula1 sVal(2) = .Formula2

Select Case .Type Case xlValidateWholeNumber, xlValidateDecimal, _ xlValidateDate, xlValidateTime, xlValidateCustom Select Case .Operator

 

  

 

 

Page 34: Latihan Excell

Case xlAnd strDV = "Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlNotBetween strDV = "Not Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlEqual strDV = "Equal to" & sC & sVal(1) Case xlNotEqual strDV = "Not Equal to" & sC & sVal(1) Case xlGreater strDV = "Greater Than" & sC & sVal(1) Case xlLess strDV = "Less Than" & sC & sVal(1) Case xlGreaterEqual strDV = "Greater Than or Equal to" & sC & sVal(1) Case xlLessEqual strDV = "Less Than or Equal to" & sC & sVal(1) Case Else 'do nothing End Select Case Else strDV = sVal(1) End Select End With strDV = sC & sVal(0) & sC & strDV Print #nFile, rCell.Address(False, False) & strDV Erase sVal Next rCell Close #nFile End If

End Sub

Document a Worksheet's Data Validation: Worksheet

The following procedure creates a new worksheet in the active Excel file, with a list of the active worksheet's data validation.

 

 

Sub DataValDocumenterSheet() 'adapted from code posted by J.E. McGimpsey 2005-02-03 'http://www.mcgimpsey.com/excel/index.html ' downloaded from www.contextures.com Dim sVal(0 To 2) As Variant Dim rValidation As Range Dim rCell As Range Dim ws As Worksheet Dim lRow As Long' Dim nFile As Long Dim sC As String Dim strDV As String' sC = vbTab On Error Resume Next Set rValidation = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If Not rValidation Is Nothing Then Set ws = Worksheets.Add(Before:=Sheets(1)) With ws .Range("A1:C1").Value = Array("Cell", "DV Type", "Formula") lRow = 2 For Each rCell In rValidation With rCell.Validation sVal(0) = Choose(.Type + 1, "Input Only", _ "Whole Number", "Decimal", "List", "Date", _ "Time", "Text Length", "Custom") sVal(1) = .Formula1 sVal(2) = .Formula2 Select Case .Type Case xlValidateWholeNumber, xlValidateDecimal, _ xlValidateDate, xlValidateTime, xlValidateCustom Select Case .Operator Case xlAnd strDV = "Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlNotBetween strDV = "Not Between" & sC & sVal(1) & sC & "And" & sC & sVal(2) Case xlEqual strDV = "Equal to" & sC & sVal(1) Case xlNotEqual strDV = "Not Equal to" & sC & sVal(1) Case xlGreater strDV = "Greater Than" & sC & sVal(1) Case xlLess strDV = "Less Than" & sC & sVal(1) Case xlGreaterEqual strDV = "Greater Than or Equal to" & sC & sVal(1) Case xlLessEqual strDV = "Less Than or Equal to" & sC & sVal(1) Case Else strDV = sVal(1) End Select Case Else strDV = "'" & sVal(1) End Select End With .Range(.Cells(lRow, 1), .Cells(lRow, 3)).Value _ = Array(rCell.Address(False, False), sVal(0), strDV) Erase sVal lRow = lRow + 1 Next rCell .Rows("1:1").Font.Bold = True .Columns("A:C").EntireColumn.AutoFit End With End If

End Sub

 

  

 

 

Page 35: Latihan Excell

Excel Tutorials - Data Validation

Excel Data Validation Combo box

   

Create an Excel Data Validation Dropdown List  Add the Combo box  Open the Properties Window  Change the Combo box Properties  Exit Design Mode  Add the Code  Test the Code  

Download the zipped sample Excel Data Validation Combo box file

You can use Excel Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.

To overcome these limitations, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list. Double-click on a cell that contains a data validation list, and the combo box appears. The combo box's font size can be set, more than 8 rows can be displayed, and autocomplete can be enabled.

If you would prefer to see the combo box as soon as you click on a data validation cell, please use the instructions at the following page:

Excel Data Validation Combo Box Click    

Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

 

Create an Excel Data Validation Dropdown List

On Sheet1, type the lists that will be used in the data validation dropdowns:

Tip: Use the AutoFill feature to create the lists

1. In cells K2:K8 type a list of weekdays 2. In cells M2:M13 type a list of months 

The next step is to create the data validation dropdown lists. There are detailed instructions here: Data Validation -- Introduction 

Cells C2:C12 have data validation lists with the source K2:K8. When a cell in this range is selected, a dropdown list of weekdays is available.

Cells D2:D12 have data validation lists with the source M2:M13. When a cell in this range is selected, a dropdown list of months is available. 

Add the Combo box

To add or edit the Combobox, open the Control Toolbox, and enter Design Mode:

1. Choose View | Toolbars2. Select Control Toolbox 3. Click the Design Mode button4. Click on the Combo box button, to activate that tool.5. Click on an empty area of the worksheet, to add a combo

box

Open the Properties Window

To format the combo box, open the properties window:

1. Select the combo box2. On the Control Toolbox, click the Properties button

Change the Combo box Properties

Name the Combo box

Page 36: Latihan Excell

1. In the Properties window, click in the Name box2. Type a name for the combo box. In this example, the name

is: TempCombo

Change the Font and Font Size

1. In the Properties window, click in the Font property, and click the ... button 

2. In the Font dialog box, select a font, font size, and other settings that you want for your combo box.

 

3. Click OK  

Set the Number of Rows

1. In the Properties window, click in the ListRows box2. Type the number of rows that you want displayed in

the dropdown. In this example, the setting is: 12

Turn on AutoComplete

1. In the Properties window, click in the MatchEntry property

2. From the dropdown list, select 1-frmMatchEntryComplete

 

 

Exit Design Mode

1. Close the Properties window2. On the Control Toolbox, click the Exit Design Mode

button

Add the Code

Visual Basic for Applications (VBA) code is required to make the combo box appear when you double-click in a cell that contains a data validation list.

Copy the following code:

'==========================Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean)Dim str As StringDim cboTemp As OLEObjectDim ws As WorksheetSet ws = ActiveSheetCancel = TrueSet cboTemp = ws.OLEObjects("TempCombo") On Error Resume Next With cboTemp 'clear and hide the combo box .ListFillRange = "" .LinkedCell = "" .Visible = False End WithOn Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains a data validation list Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = ws.Range(str).Address .LinkedCell = Target.Address End With cboTemp.Activate 'open the drop down list automatically Me.TempCombo.DropDown errHandler: Application.EnableEvents = True Exit Sub

End Sub'=========================================Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim str As StringDim cboTemp As OLEObjectDim ws As WorksheetSet ws = ActiveSheet

 

  

 

 

Page 37: Latihan Excell

Set cboTemp = ws.OLEObjects("TempCombo") On Error Resume NextIf cboTemp.Visible = True Then With cboTemp .Top = 10 .Left = 10 .ListFillRange = "" .LinkedCell = "" .Visible = False .Value = "" End WithEnd If

errHandler: Application.EnableEvents = True Exit Sub

End Sub '===================================='Optional code to move to next cell if Tab or Enter are pressed'from code by Ted LanhamPrivate Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab ActiveCell.Offset(0, 1).Activate Case 13 'Enter ActiveCell.Offset(1, 0).Activate Case Else 'do nothing End SelectEnd Sub'====================================

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code. 2. Choose Edit | Paste, to paste the code onto the sheet

module, where the cursor is flashing. 3. Choose File | Close and Return to Microsoft Excel.

Test the Code

1. Double-click on one of the cells that contains a data validation list.

2. The combo box will appear3. Select an item from the combo box dropdown list4. Click on a different cell, to select it5. The selected item appears in previous cell, and the combo

box disappears.

Download the zipped sample Excel Data Validation Combo box file

 

Excel Tutorials - Data Validation

 Data Validation Combo Box using Named Ranges

Set up the Workbook    Create a Dropdown List    Add the Combo box  Open the Properties Window  Change the Combo box Properties  Exit Design Mode  Add the Code  Test the Code  Download the Sample FileMore Data Validation Tutorials

You can use Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.

To overcome these limitations, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list.

Double-click on a cell that contains a data validation list, and the combo box appears. The combo box's font size can be set, more than 8 rows can be displayed, and autocomplete can be enabled.

Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

Set up the Workbook

Two worksheets are required in this workbook.

1. Delete all sheets except Sheet1 and Sheet22. Rename Sheet1 as ValidationSample

Page 38: Latihan Excell

3. Rename Sheet2 as ValidationLists

On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:

Tip: Use the AutoFill feature to create the lists

1. In cells A1:A7 type a list of weekdays 2. In cells C1:C12 type a list of months

Name the lists (there are Naming instructions here: Name a Range):

1. Name the range A1:A7 as DayList 2. Name the range C1:C12 as MonthList    

Create a Dropdown List

The next step is to create the dropdown lists. There are detailed instructions here: Excel Data Validation Introduction 

Cells C2:C12 have data validation lists with the source DayList. When a cell in this range is selected, a dropdown list of weekdays is available.

Cells D2:D12 have data validation lists with the source MonthList. When a cell in this range is selected, a dropdown list of months is available. 

Add the Combo box

To add or edit the Combobox, open the Control Toolbox, and enter Design Mode:

1. Choose View | Toolbars2. Select Control Toolbox 3. Click the Design Mode button

4. Click on the Combo box button, to activate that tool.

5. Click on an empty area of the worksheet, to add a combo box

Open the Properties Window

To format the combo box, open the properties window:

1. Select the combo box2. On the Control Toolbox, click the Properties button

Change the Combo box Properties

Page 39: Latihan Excell

Name the Combo box

1. In the Properties window, click in the Name box2. Type a name for the combo box. In this example, the name is: TempCombo

Change the Font and Font Size

1. In the Properties window, click in the Font property, and click the ... button 

2. In the Font dialog box, select a font, font size, and other settings that you want for your combo box.

3. Click OK  

Set the Number of Rows

1. In the Properties window, click in the ListRows box2. Type the number of rows that you want displayed in the dropdown. In this example, the setting is: 12

Turn on AutoComplete

1. In the Properties window, click in the MatchEntry property2. From the dropdown list, select 1-frmMatchEntryComplete

Exit Design Mode

1. Close the Properties window2. On the Control Toolbox, click the Exit Design Mode button

Add the Code

Visual Basic for Applications (VBA) code is required to make the combo box appear when you double-click in a cell that contains a data validation list.

Copy the following code:

'==========================Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean)Dim str As StringDim cboTemp As OLEObjectDim ws As WorksheetDim wsList As WorksheetSet ws = ActiveSheetSet wsList = Sheets("ValidationLists")

Cancel = TrueSet cboTemp = ws.OLEObjects("TempCombo") On Error Resume Next With cboTemp 'clear and hide the combo box .ListFillRange = "" .LinkedCell = "" .Visible = False End WithOn Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains a data validation list

Page 40: Latihan Excell

Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = str .LinkedCell = Target.Address End With cboTemp.Activate 'open the drop down list automatically Me.TempCombo.DropDown

End If errHandler: Application.EnableEvents = True Exit Sub

End Sub'=========================================Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim str As StringDim cboTemp As OLEObjectDim ws As WorksheetSet ws = ActiveSheetApplication.EnableEvents = FalseApplication.ScreenUpdating = True

If Application.CutCopyMode Then 'allow copying and pasting on the worksheet GoTo errHandlerEnd If

Set cboTemp = ws.OLEObjects("TempCombo") On Error Resume Next With cboTemp .Top = 10 .Left = 10 .Width = 0 .ListFillRange = "" .LinkedCell = "" .Visible = False .Value = "" End With

errHandler: Application.EnableEvents = True Exit Sub

End Sub '===================================='Optional code to move to next cell if Tab or Enter are pressed'from code by Ted Lanham'***NOTE: if KeyDown causes problems, change to KeyUp

Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab ActiveCell.Offset(0, 1).Activate Case 13 'Enter ActiveCell.Offset(1, 0).Activate Case Else 'do nothing End SelectEnd Sub'====================================

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code.

2. Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing. 3. Choose File | Close and Return to Microsoft Excel.

Test the Code

1. Double-click on one of the cells that contains a data validation list.2. The combo box will appear3. Select an item from the combo box dropdown list4. Click on a different cell, to select it5. The selected item appears in previous cell, and the combo box disappears.

Download the Sample File

To test the combo box code, you can download the zipped sample file.

More Data Validation Tutorials

Page 41: Latihan Excell

Excel Data Validation Combo box ClickCreate an Excel Data Validation Dropdown List  Add the Combo box  Open the Properties Window  Change the Combo box Properties  Exit Design Mode  Add the Code  Test the Code  

Download the zipped sample file

 

 

You can use Excel Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.

To overcome these limitations, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list. Click on a cell that contains a data validation list, and the combo box appears. The combo box's font size can be set, more than 8 rows can be displayed, and autocomplete can be enabled.

If you would prefer to see the combo box only when you double-click on a data validation cell, please use the instructions at one of the following pages:

Data Validation -- Combo Box     Data Validation -- Combo Box - Named Ranges

Note: If the worksheet is protected, allow users to Edit Objects, and they will be able to use the combobox.

 

Create a Data Validation Dropdown List

On Sheet1, type the lists that will be used in the data validation dropdowns:

Tip: Use the AutoFill feature to create the lists

1. In cells K2:K8 type a list of weekdays 2. In cells M2:M13 type a list of months 

The next step is to create the data validation dropdown lists. There are detailed instructions here: Data Validation -- Introduction 

Cells C2:C12 have data validation lists with the source K2:K8. When a cell in this range is selected, a dropdown list of weekdays is available.

Cells D2:D12 have data validation lists with the source M2:M13. When a cell in this range is selected, a dropdown list of months is available. 

Add the Combo box

To add or edit the Combobox, open the Control Toolbox, and enter Design Mode:

1. Choose View | Toolbars2. Select Control Toolbox 3. Click the Design Mode button4. Click on the Combo box button, to activate that tool.5. Click on an empty area of the worksheet, to add a combo

box

Open the Properties Window

To format the combo box, open the properties window:

1. Select the combo box2. On the Control Toolbox, click the Properties button

Change the Combo box Properties

Name the Combo box

1. In the Properties window, click in the Name box2. Type a name for the combo box. In this example, the name

is: TempCombo

Change the Font and Font Size

1. In the Properties window, click in the Font property, and click the ... button 

2. In the Font dialog box, select a font, font size, and other

Page 42: Latihan Excell

settings that you want for your combo box.

 

3. Click OK  

Set the Number of Rows

1. In the Properties window, click in the ListRows box2. Type the number of rows that you want displayed in

the dropdown. In this example, the setting is: 12

Turn on AutoComplete

1. In the Properties window, click in the MatchEntry property

2. From the dropdown list, select 1-frmMatchEntryComplete

 

 

Exit Design Mode

1. Close the Properties window2. On the Control Toolbox, click the Exit Design Mode

button

Add the Code

Visual Basic for Applications (VBA) code is required to make the combo box appear when you click in a cell that contains a data validation list.

Copy the following code:

'=========================================Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim str As StringDim cboTemp As OLEObjectDim ws As WorksheetSet ws = ActiveSheetOn Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo") On Error Resume NextIf cboTemp.Visible = True Then With cboTemp .Top = 10 .Left = 10 .ListFillRange = "" .LinkedCell = "" .Visible = False .Value = "" End WithEnd If

On Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains a data validation list Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 15 .Height = Target.Height + 5 .ListFillRange = ws.Range(str).Address .LinkedCell = Target.Address End With cboTemp.Activate 'open the drop down list automatically Me.TempCombo.DropDown End If

exitHandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit SuberrHandler: Resume exitHandler

End Sub '===================================='Optional code to move to next cell if Tab or Enter are pressed'from code by Ted Lanham

 

  

 

 

Page 43: Latihan Excell

Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab ActiveCell.Offset(0, 1).Activate Case 13 'Enter ActiveCell.Offset(1, 0).Activate Case Else 'do nothing End SelectEnd Sub'====================================

To add this code to the worksheet:

1. Right-click on the sheet tab, and choose View Code. 2. Choose Edit | Paste, to paste the code onto the sheet

module, where the cursor is flashing. 3. Choose File | Close and Return to Microsoft Excel.

Test the Code

1. Click on one of the cells that contains a data validation list.

2. The combo box will appear3. Select an item from the combo box dropdown list4. Click on a different cell, to select it5. The selected item appears in previous cell, and the combo

box disappears.

Download the sample file

 

Excel Tutorials - Data Validation

Excel Data Validation - Add New Items 

 

Add New Items to Data Validation Drop Down Set Up the Excel Workbook Create the Dynamic Named Ranges Set Up the Data Entry Sheet Add the Sort List Code Test the Sort List Code Add the Data Entry Code Test the Data Entry Code How the Data Entry Code Works Watch the Excel Video Tutorial

To view the steps in a short video, click here: Excel Data Validation Add Items Video

Download the zipped sample Excel Data Validation Add New Items file

Add New Items to Data Validation Drop Down

In this Excel data validation example, you'll create an Excel Data Validation drop down list that allows users to add new items.

The new data validation items will be automatically added to the drop down list, and the list will be sorted alphabetically.

Set Up the Excel Workbook

In the sample Excel workbook, there's a data entry sheet, named DataEntry. It has two data entry columns, with the headings Client and Fruit, and data validation drop down lists in those columns.

On another worksheet, named Lists, there are the source lists for the data validation drop down lists. The lists are in separate columns, with a blank column in between.

The lists do not have a heading.

Create the Dynamic Named Ranges

After you type the lists of items for the data validation drop down lists, create a dynamic named range for each list.

The formulas that were used for the two dynamic named ranges in the sample file were:

ClientList: =OFFSET(Lists!

Page 44: Latihan Excell

$D$1,0,0,COUNTA(Lists!$D:$D),1) FruitList: =OFFSET(Lists!

$B$1,0,0,COUNTA(Lists!$B:$B),1)

.

Set Up the Data Entry Sheet

In the DataEntry sheet, follow these instructions to add data validation drop down lists in the columns for Client and Fruit:

1. Select the cells where you want the Client drop down lists

2. On the Ribbon, click the Data tab, then click Data Validation (In Excel 2003, click the Data menu, then click Validation.)

3. From the Allow drop-down list, choose List 4. In the Source box, type an equal sign and the list

name, for example: =ClientList 5. On the Error Alert tab, remove the check mark to

allow invalid entries. 6. Click OK

Repeat the steps for the Fruit data entry cells, using =FruitList as the source.

Add the Sort List Code

On the worksheet code module for the Lists sheet, you'll add code that runs automatically if a change is made on the worksheet.

To add the Excel VBA code, follow these steps:

Right-click the Lists sheet tab, and click View Code

The Visual Basic Editor (VBE) window opens, and the empty code module for the Lists sheet is shown.

At the top left of the code window, click the arrow in the Object drop down, and click on Worksheet.

A couple of lines of code will be automatically added to the code module, and you can ignore that code, or delete it.

At the top right of the code window, click the arrow in the Procedure drop down, and click on Change.

Page 45: Latihan Excell

When the cursor is flashing, type or paste the following code, between the Private Sub Worksheet_Change and End Sub lines:

Private Sub Worksheet_Change(ByVal Target As Range) Columns(Target.Column).Sort _ Key1:=Cells(1, Target.Column), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottomEnd Sub

This code figures out which column the change was made in (Target.Column), and then sorts that column.

Test the Sort List Code

To see how the sort code works, you can make a change to one of the lists:

In the first blank cell at the end of the Fruit list, type Apricot, then press the Enter key. The Fruit list will be sorted automatically, so Apricot moves between Apple and Banana

Click on the cell that contains Apricot, then press the Delete key. The Fruit list will be sorted automatically, so the blank cell moves to the end of the list.

Add the Data Entry Code

On the worksheet code module for the DataEntry sheet, you'll add code that runs automatically if a change is made on the worksheet.

Note: For code that allows only one specific user to add new items, see Data Entry Code - Specific User

To add the Excel VBA code, follow these steps:

1. Right-click the DataEntry sheet tab, and click View Code. The Visual Basic Editor (VBE) window opens, and the empty code module for the DataEntry sheet is shown.

2. At the top left of the code window, click the arrow in the Object drop down, and click on Worksheet. A couple of lines of code will be automatically added to the code module, and you can ignore that code, or delete it.

3. At the top right of the code window, click the arrow in the Procedure drop down, and click on Change.

4. When the cursor is flashing, type or paste the following code, between the Private Sub Worksheet_Change and End Sub lines

Private Sub Worksheet_Change(ByVal Target As Range)On Error Resume NextDim ws As WorksheetDim str As StringDim i As IntegerDim rngDV As RangeDim rng As Range

If Target.Count > 1 Then Exit SubSet ws = Worksheets("Lists") If Target.Row > 1 Then On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then Exit Sub str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) On Error Resume Next Set rng = ws.Range(str) On Error GoTo 0 If rng Is Nothing Then Exit Sub If Application.WorksheetFunction _ .CountIf(rng, Target.Value) Then Exit Sub Else i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1 ws.Cells(i, rng.Column).Value = Target.Value rng.Sort Key1:=ws.Cells(1, rng.Column), _

Page 46: Latihan Excell

Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If

End If

End Sub

This code adds new items to the data validation lists, and sorts the source item list if an item was added. There is a detailed explanation of the code below.

Test the Data Entry Code

To see how the sort code works, you can add an item to one of the lists:

In the the Client column, type Ann, then press the Enter key.

Click the drop down arrow in the Client column, and you'll see that Ann now appears in the drop down list.

Check the Lists sheet, and you'll see that Ann was added to the ClientList range, between Al and Bea.

How the Data Entry Code Works

First, the code checks to see if more than one cell was changed. If so, the macro stops running:

If Target.Count > 1 Then Exit Sub

Next, the code checks to which row was changed. If it was row 1, where the headings are located, the macro stops running.:

If Target.Row > 1 Then...End If

Then, the code tries to set a range based on the data validation cells in the worksheet. If there are no data validation cells, the range can't be set, so the macro stops running:

On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub

Next, the code checks to see if the cell that was changed (Target) intersects with the range of cells that contain data validation. If it doesn't, the macro stops running:

If Intersect(Target, rngDV) Is Nothing Then Exit Sub

Then, the code creates a text string, based on the data validation formula in the changed cell. The Right function removes the first character from the string. For example, if the data validation formula is =ClientList the str variable would be set to ClientList:

str = Target.Validation.Formula1 str = Right(str, Len(str) - 1)

Then, the code tries to set a range based on the str variable. If there is no range with that name on the Lists sheet, the range can't be set, so the macro stops running:

On Error Resume Next Set rng = ws.Range(str) On Error GoTo 0 If rng Is Nothing Then Exit Sub

Then, the code checks that range, to see if the changed cell's value is already in that list. If so, the macro stops running:

If Application.WorksheetFunction _ .CountIf(rng, Target.Value) Then Exit Sub

If the changed cell contains a new item, the code finds the first empty row in the list's column on the Lists sheet:

i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1

The changed cell's value is added in that empty row on the Lists sheet:

ws.Cells(i, rng.Column).Value = Target.Value

Finally, the code sorts the revised list on the Lists sheet:

rng.Sort Key1:=ws.Cells(1, rng.Column), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom

Page 47: Latihan Excell

Watch the Excel Video Tutorial

To view the steps in a short video, click here: