In the screen shot below, you can see a simple UserForm.
Worksheet Data Entry
With the worksheet method, you can
hide the data sheets, and protect the data entry sheets, so users can only enter data in the unlocked cells.
add a few navigation and function buttons, to help users with basic Excel skills.
An advantage is that you’re using built-in Excel features, like data validation and formulas, so you can reduce the development time.
Excel UserForm
The UserForm method takes longer to develop, because you’re adding another layer to the project. Advantages to this method include:
combo boxes, which can be formatted, and have autocomplete (unlike data validation drop downs)
tab order control, which isn’t available on the worksheet, where pressing the Tab key simply takes you to the next unlocked cell.
Which Would You Pick?
Both methods work well, and can be customized to be user-friendly and fool-resistant (nothing in Excel is fool-proof!) Programming would be required in both versions, to help with navigation, and to move data to the storage worksheets.
The worksheet method is quicker and easier to create and maintain, and a project might take 4-5 hours to complete.
The UserForm method is more sophisticated, and takes longer to build and maintain. The UserForm version of the same project might take 8-10 hours.
In Excel 2007 and Excel 2010, you can use icon sets in conditional formatting. There are built-in icon sets, and in Excel 2010 you can Customize Excel Conditional Formatting Icons, to some extent. Here’s how to do that, and a workaround to create icons on the worksheet instead.
Data validation is one of the best features in Excel. You can use it to create drop down lists, or limit what users can enter in a cell.
Unfortunately, data validation isn’t perfect, or foolproof. Users can get around the limits, by pasting data into the cell, or by using the Clear All command in a data validation cell.
Data Validation Input Errors
Someone sent me a question this week, asking how to trap input errors, despite these data validation failings:
Hello! I was wondering, to you have any idea how to error trap a date input? I will enter dates on a specific column (complete dates such as 11/05/2010) and error trap if the input has the year of 2011 and not 2010. I know data validation does that, but only if the cell is manually inputted. If the input in the cell is pasted, it does not do that anymore.
Add the Data Validation
In this example, an order form has two cells for dates – an Order Date, and a Delivery Date. To ensure that a date for the current year is entered, you can use formulas in the data validation, to set a minimum and a maximum date.
Start Date formula: =DATE(YEAR(TODAY()),1,1)
End Date formula: =DATE(YEAR(TODAY()),12,31)
Check the Date
If you’re concerned that users might paste values into the cell, or clear the data validation, you can add a formula check, to ensure that a valid date was entered.
In the order form, a date check formula is entered in column G, which can be hidden.
The formula in cell G9 is:
=OR(C9=””,YEAR(C9)=YEAR(TODAY()))
The formula is copied down to cell G10, and the result is FALSE, because the date in C10 is not in the current year.
Date check formula entered in column G
Block the Invoice Total
In the Invoice total cell, the formula result is “Invalid Date”, if either of the date check cells contains FALSE. If both dates are in the current year, the total sum is shown.
If you’re building a new city, or plotting world domination, you’ll need a powerful project management tool, such as Microsoft Project.
For smaller projects, you can list your tasks in Excel, and create a Gantt chart, to show the timeline. Here’s how you can do simple project planning with Excel Gantt chart – watch the video and there are written steps too.
With Excel data validation, you can create drop down lists on a worksheet. However, the font size is very small, and can’t be adjusted, and you can only see 8 items at at time.
If you work with data in Excel, you know what a mess it can be. I help my customers clean up data that they’ve imported from another computer system, or from reports received from another department or group.
Those files can be filled with spelling mistakes, strange abbreviations, extra spaces or missing punctuation.
In the comments of the previous article, James asked how to keep those Slicers from overlapping the pivot tables.
Does anyone know how to stop slicers moving around when you make selections. This happens if the slicers are viewed on top of the pivot table data. As the pivot output data shrinks or expands the slicers move around and sometimes obscure each other. Any idea how to fix in place?
Pivot Table Update Event
One way to fix the problem of sliding Slicers is to automatically move the Slicers, any time the pivot table is updated.
To do that, you can use the PivotTableUpdate event, and a macro that moves the Slicer to the right side of the pivot table.
Slicer Caption
Each Slicer has a caption, and you can refer the the Slicer by that caption in the Excel VBA code.
In this example, the Slicer has a caption of “Region”, which is shown at the top of the Slicer.
The caption is also visible on the Excel Ribbon’s Options tab, when the Slicer is selected.
Slicer caption visible on Excel Ribbon’s Options tab
Macro to Move a Pivot Table Slicer
Here is the sample code that I used.
This macro moves a pivot table slicer to the right side of the pivot table, any time the pivot table is updated.
Sub MoveSlicer()
Dim wsPT As Worksheet
Dim pt As PivotTable
Dim sh As Shape
Dim rngSh As Range
Dim lColPT As Long
Dim lCol As Long
Dim lPad As Long
Set wsPT = Worksheets("PivotSales")
Set pt = wsPT.PivotTables("PivotDate")
Set sh = wsPT.Shapes("Region")
lPad = 10
lColPT = pt.TableRange2.Columns.Count
lCol = pt.TableRange2.Columns(lColPT).Column
Set rngSh = wsPT.Cells(1, lCol + 1)
sh.Left = rngSh.Left + lPad
End Sub
How the Code Works
In the code, a variable (pt) is set for the pivot table.
The code counts the columns in the pivot table’s TableRange2 range, which includes the Report Filters area. (TableRange1 does not include the report filters.)
lColPT = pt.TableRange2.Columns.Count
The code adds 1 to the column number that the last pivot table column is in.
Set rngSh = wsPT.Cells(1, lCol + 1)
A variable (lPad) sets the padding number — how much the Slicer will be moved to the right. In this example, the variable is set to 10
lPad = 10
Finally, the Slicer is positioned, in the column to the right of the pivot table. In that column, the Slicer’s left side is indented by the padding amount.
sh.Left = rngSh.Left + lPad
Pivot Table Update Code
The following code should be copied to the pivot table’s worksheet module.
It will run the macro to move a pivot table slicer (MoveSlicer), any time the PivotDate pivot table is updated.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotDate" Then
MoveSlicer
End If
End Sub
Download the Sample File
To see how the macro to move a pivot table slicer works, you can download the Excel Slicer Move Code sample workbook. The file is in xlsm format, and is zipped. You’ll have to enable macros, to test the code.
_______
Data validation is one of the best features in Excel. You can use it to 
Recently, we saw how you can 
