VBA Option for Protecting Input Cells

VBA to protect select worksheet cells.

An internal control requirement at work states that all worksheets that serve as a source for a journal entry must be protected. Although this control might be adequate for protecting a sheet against another user, it does little to protect it against errors made by the owner.

There are many ways to implement controls that alert the user against unintended changes. A few examples are "If - Then" formula logic, conditional formatting, and data validation. In this case we wanted to protect the worksheet against changes to statistical and financial data that had already been recorded in the past. That data was contained in "input cells" would always be in the form of a constant. We wanted this data to always be safe but still allow changes to all other cells. Since there are a lot of input cells that reside in many areas of the sheet, we wanted to automate the process if possible.

First of all, we knew that the special cells feature will select only those cells that contain numbers. The dialog box that shows this option is below (In Excel 2003, go to Edit -> GoTo -> Special).

The automation would be achieved through VBA. The procedure below selects the range A1:G20 and protects any cell containing a constant.

Sub LockConstants()

    Application.ScreenUpdating = False

    On Error Resume Next
    
    Dim Rng As Range
    
    Set Rng = ActiveSheet.Range("A1:G20")

    'Unprotect the sheet

    ActiveSheet.Unprotect

    'Unlock all cells within the stated range
    
    Cells.Select
    Cells.Locked = False

    'Select constants only

    Rng.SpecialCells(xlCellTypeConstants, 1).Select

    'Lock select cells
    
    Selection.Locked = True

    'Apply protection
    
    ActiveSheet.Protect DrawingObjects:=False, _
                            Contents:=True, _
                            Scenarios:=False, _
                            AllowFormattingCells:=True, _
                            AllowFormattingColumns:=True, _
                            AllowFormattingRows:=True, _
                            AllowInsertingColumns:=True, _
                            AllowInsertingRows:=True, _
                            AllowInsertingHyperlinks:=False, _
                            AllowDeletingColumns:=False, _
                            AllowDeletingRows:=False, _
                            AllowSorting:=False, _
                            AllowFiltering:=False, _
                            AllowUsingPivotTables:=False
End Sub

Finally, the general process works like this:

When the user opens the workbook, the LockConstants procedure is applied to the active sheet. Data (in the form of constants) can then be added into the input cells. However, data that was entered at an earlier time is protected via the procedure. When the workbook is closed, another procedure protects all of the sheets so they remain in compliance with the control.

Multiple New Entries in RSS Feed Error

Announcement of the correction of this error.

If you use Google Reader to view blog entries from this site, you've probably noticed that the reader picks up old entries as new. The problem seems to be due to the RSS template regarding an old entry that was modified in any way (to correct a spelling error, add or delete a sentence, etc.) as new. I've recently made changes to the template which I hope will correct this behavior.

Partial Year Spreads

Formulas to calculate a 12-month spread given partial year results.

Forecasting annual spreads can be difficult when trends are inconsistent and only a partial year of data is available. As an example, the chart below presents staffing for two full years (2007 and 2008) and a partial year (2009 - the red bars). The trend in staffing averages at lower levels for the first half of the year (the black line) and then picks up during the second half (the red line).

The underlying data for the chart is below:

Given data available for January through June of 2009 and an annual projection (16.3), the formula below entered into cells I5:N5 will produce monthly amounts that mirror the past two years.

=($O$5*12-SUM($C$5:$H$5))*SUM(I$3:I$4)/SUM($I$3:$N$4)

After letting the formula do its work, the chart looks like this:

Auto-Update Column

A recent post to the Microsoft Excel Charts Discussion Group asked " I have a cell with a DDE link from another program, which is sample data that I need to produce a real time graph. To do so I need to copy the value of cell A1 and paste it into cell B1. When the cell A1 value changes I need to copy the new value from A1 into B2 and so on. I can then produce a real time graph from the information in column B. How can I accomplish this?"

I responded with the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim Rng As Range

    Set Rng = Range("B65536").End(xlUp)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("B1").Value = vbNullString Then
            Range("B1").Value = Range("A1").Value
        Else
            Rng.Offset(1, 0).Value = Range("A1").Value
        End If
    End If

End Sub

In reference to "vbNullString" above, The Daily Dose of Excel has some great posts on how to test for empty cells:

• Testing for Empty Cells I
• Testing for Empty Cells II

Just documenting for my own reference.

A Dicey Line Graph

Slicing and dicing data to predict the future can get dicey.

I'm always interested in the incorrect use of correlation analysis. The graph below recently appeared in an article written by Jason Zweig and published in the on-line version of The Wall Street Journal. Although the author writes about the stock market, his conclusions can be applied universally.

The stock market generates such vast quantities of information that, if you plow through enough of it for long enough, you can always find some relationship that appears to generate spectacular returns -- by coincidence alone. This sham is known as "data mining."

The article goes on to say:

That points to the first rule for keeping yourself from falling into a data mine: The results have to make sense. Correlation isn't causation, so there needs to be a logical reason why a particular factor should predict market returns. No matter how appealing the numbers may look, if the cause isn't plausible, the returns probably won't last.

The second rule is to break the data into pieces. Divide the measurement period into thirds, for example, to see whether the strategy did well only part of the time. Ask to see the results only for stocks whose names begin with A through J, or R through Z, to see whether the claims hold up when you hold back some of the data.