Search Contextures Sites

Excel Data Validation - Add New Items - Specific User

	Introduction Set Up the Data Entry Sheet Add the Data Entry Code Test the Data Entry Code How the Data Entry Code Works Download the zipped sample Data Validation Add Items-User file
	Introduction In this Excel data validation example, you'll create an Excel Data Validation drop down list that allows one specific user 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. This technique isn't foolproof, and anyone who's determined to circumvent it would be able to. But, it's a good way to remind people that they can't update the list without permission. Set Up the Data Entry Sheet For instructions on setting up the worksheets and List sheet sort code, see the Excel Data Validation - Add New Items page. Follow those instructions, up to the Data Entry Code section. Then return to this page for the Data Entry Code. The Data Entry code shown here will allow only one specific user to add new items. All other users will see a message that says they're not allowed to add items.
	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. To add the Excel VBA code, follow these steps:
	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. 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. When the cursor is flashing, type or paste the following code, between the Private Sub Worksheet_Change and End Sub lines. Then, change the strAuth to the person you want to allow to add new items.
	Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim ws As Worksheet Dim str As String Dim i As Integer Dim rngDV As Range Dim rng As Range Dim strUser As String strAuth = "Debra Dalgleish" strUser = Application.UserName Application.EnableEvents = False If Target.Count > 1 Then GoTo exitHandler Set 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 GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then GoTo exitHandler 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 GoTo exitHandler If Application.WorksheetFunction _ .CountIf(rng, Target.Value) Then GoTo exitHandler Else If strUser <> strAuth Then MsgBox "You do not have authority to add Items. " _ & vbCrLf _ & vbCrLf _ & "Please check with Administrator." Application.Undo GoTo exitHandler 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), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End If End If exitHandler: Application.EnableEvents = True Exit Sub errHandler: MsgBox "Could not add new item" Resume exitHandler End Sub
	Test the Data Entry Code To see how the sort code works, add your name as the authorized user, and 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. If your name is different than that of the authorized user, you won't be able to add an item to one of the lists: In the the Client column, type Joe, then press the Enter key. A warning message appears, and the invalid entry is removed from the data validation cell.
	How the Data Entry Code Works First, the code finds the user name associated with the copy of Microsoft Excel: strUser = Application.UserName The EnableEvents property is set to False, so any other changes to the worksheet will be ignored, while this code is running. Application.EnableEvents = False Then, the code checks to see if more than one cell was changed. If so, the macro stops running: If Target.Count > 1 Then GoTo exitHandler 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 GoTo exitHandler 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 GoTo exitHandler 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 GoTo exitHandler Then, the code checks that range, by using the COUNTIF worksheet function, 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 GoTo exitHandler If the changed cell contains a new item, the code checks to see if the user is authorized to make the change: If strUser <> strAuth Then MsgBox "You do not have authority to add Items. " _ & vbCrLf _ & vbCrLf _ & "Please check with Administrator." Then the code removes the unauthorized item from the data validation cell: Application.Undo GoTo exitHandler For an authorized user, 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 In the exitHandler, the EnableEvents property is set to True, so changes to the worksheet be detected again, when this procedure stops running. Application.EnableEvents = True Download the zipped sample Excel Data Validation Add Items-User file

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

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish

Last updated: September 6, 2010