Suppose you have a spreadsheet where you are entering a list of unique values, such as invoice numbers, customer names, etc. You don’t want to duplicate any of the data, as this would cause problems down the road, but at the same time the list might be too large for you to manually verify that no duplicate values have been entered.
You can use Data Validation to ensure that no duplicate values can be entered into your list. If a user accidentally enters a duplicate, an error message will pop-up telling them that they have entered an invalid value.
To do this, follow the steps below.
- Select the column where you want your list of unique values to appear (for illustration purposes we’ll assume it is column A).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: Custom.
- In the Formula box, type the following formula: =MATCH(A1,$A:$A,0)=ROW(A1) (if you’re using this in a different column than column A, change the “A1″ and “$A”‘s in the formula to whichever column you’re using)
- Click OK.
Now begin to type your list of unique values. If you should happen to accidentally enter one twice, you’ll see the following error message pop-up:
You can click “Retry” to re-enter a value in the same cell, or “Cancel” to delete the duplicate value.
**UPDATE**
To have a “one click” availability to this tip, use the following macro code. Again, like the formula above, change the “A1″ and “$A”‘s to whichever column you’re using.
Sub Duplicate_Validation()
'
'MACROS BY EXCELZOOM.COM
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MATCH(A1,$A:$A,0)=ROW(A1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
As discussed in a previous tip, adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.
The example in the previous tip emphasized the importance of having consistent information when summarizing information in a PivotTable. Another use is to store lists of information, which could be anything, but in this example, I’m going to use website addresses.
Say you do a lot of research on several different topics, and summarize the results in a different spreadsheet for each topic. You could store all the bookmarks in your web browser, and chances are you already do. The problem with this is that if you’re only using a handful of bookmarks for one topic out of the many you have saved, you could end up searching through a lot of bookmarks to find the sites you need.
If you instead take the time up front to compile a list of sites you use frequently for a particular topic, you can use data validation to display them in one cell on your spreadsheet with another “clickable” cell to open the site in your web browser.
To do this, follow the steps below.
- Select the cell where you want your list of websites to appear (for illustration purposes I’ll assume your list will appear in cell A1).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
- In the Source box, refer to the range of cells in the same worksheet that contains your website list, or refer to a named range elsewhere in your workbook.
- Click OK.
When you select cell A1, you should now see the list of websites when you click the arrow to the right of the cell. Now select cell B1. This is where we are going to put some text that becomes “clickable”, sort of like a link on a website.
In cell B1, type =HYPERLINK(A1,”GO”). Now whenever you change the website in cell A1, using the drop down list, you can click the words “GO” in cell B1 go to that site. It’s like having your own bookmark system in Excel!
Often times when you execute a macro, the process isn’t easily undone. You may have a macro that deletes certain information, or completely reformats your worksheet. In either case, you want to be sure that your workbook’s users are absolutely certain that they are ready to execute the macro before running it. If the macro can be run by clicking on an object within the worksheet, an accidental click may cause unintended consequences.
The code below will produce a pop up box as soon as the macro is run. It will ask the user if they are certain that they want to run the macro, and give them the option to proceed, or cancel it right there. Think of it like when you’re about to delete a file from your computer and Windows asks “Are you sure you want to move this file to the Recycle Bin?”. Clicking “Yes” will delete the file, and “No” will cancel the process.
You can edit the text to be displayed in between the quotes “Do you want to run this macro?”. The code will produce two options “OK” and “Cancel”. If you want it to produce “Yes” and “No”, change the vbOKCancel to vbYesNo, and vbCancel to vbNo. Both will do the same thing, it just depends on how you want to present the question.
Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module. Replace ** YOUR MACRO CODE HERE ** with your macro’s commands.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub Confirm_Macro()
'
'MACROS BY EXCELZOOM.COM
'
If MsgBox("Do you want to run this macro?", vbOKCancel + vbQuestion) = vbCancel Then
Exit Sub
Else
** YOUR MACRO CODE HERE **
End If
End Sub