A Lingering Weakness Of Excel

I work very hard to build bullet-proof applications that are as locked-down as possible from user meddling and just plain honest things that they MIGHT do in the course of their use of the application that might mess things up.   I have been able to control nearly everything I need to do so I can sleep nights with Excel apps in “The Wilds” of my clients’ networks (Workbook Protection, Worksheet Protection, Project Protection).  I also create my own Custom Menus or Ribbons to focus the user on the use of just the spreadsheet in front of them without any extra unnecessary menu or Ribbon items.  But there is one major hole in my armour that I cannot control and that is becoming more and more a problem for me.

Despite unlocking specific cells and then protecting a Worksheet with a password, a user who copies and pastes from a cell in a source Workbook into a cell in a target Workbook will find their unlocked cells become formatted with the source cell’s formatting – even though the protection of the Worksheet specifically says not to allow the user to change formats in the protected worksheet – so the user can’t even FIX the copied format themselves. 

Even better than that, if you copy from another instance of Excel (which does happen when you have customized menus and Ribbons) even the locked status of the target cell gets changed if the source cell is locked.  This is a BIG problem for me in several applications I have in place.

Here is an example:

Paste Issue

Figure 1 is the target Worksheet.  It is formatted, Cells B1 and B2 are unlocked and Worksheet protection is ON.

Figure 2 is the source Worksheet (in another Instance of Excel) with cell B1 formatted but NOT unlocked.

Figure 3 is the result of a Copy Paste from Figure 2 to Figure 1.  Notice that the formatting came along with the Paste, and although I can’t show it here, the target cell (B1) is now locked – so not only is the formatting messed up but it cannot be changed unless you remove the Protection (which kinda defeats the purpose methinks).

While this may seem like an obscure problem to many, I don’t think it is.  In actual fact copying and pasting from spreadsheet to spreadsheet has always been common.  It’s just that if you want to build a “bullet-proof” application where the user is protected from his or herself, then this is a major hole in the security of the file and my ability to assure the integrity of my application. 

IMHO this is a major weakness and even in Excel 2010 I do not see this fixed.  Sucks.

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

10 Responses to A Lingering Weakness Of Excel

  1. Jayson says:

    I agree. Huge hole. I’d forgotten about this, then recently had it rear its ugly head on a fairly large project. So now I face the solution of trapping all pastes and substituting a paste special. Not difficult, has the added benefit of keeping all of the native formatting, but still shouldn’t need to be done.

    Why is “protected” the default format anyway?

  2. Dick Moffat says:

    Yes I started to write some code to do that and it pissed me off so much doing it that I quit in disgust. Fortunately I’ve been able to control this with some user training as well but if I ever have any serious new Excel apps in the future I might just write something then.

    Maybe MS could gather some code to do this and post it on their Web-Site (??)… it would be a useful utility.

    Dick

  3. Marcus from London says:

    Dick, shame on you using the worksheet as a Data Entry GUI😛

    The level – or lack thereof – of control over such things is why I rarely use the grid for data entry resorting to UserForms instead. While there may be more work involved it is easier to control the flow of data entry and validation particularly when some entries/selections preclude others. Entered data is usually stored centrally in a database and occasionally on hidden worksheets.

    Cheers – Marcus

  4. Dick Moffat says:

    Hey Marcus ….. While I agree that great volumes of the data to be used ina spreadsheet should be pulled from or stored in a database and then analyzed in the spreadsheet, to say you should NEVER type values into a spreadsheet is a bit over the top (and i assume you’re being sarcastic).

    The spreadsheet grid is a great form in itself and provides much more capability than the User Forms and most of it is for free (no coding necessary).

    Sorry – too anal for my tastes🙂

    Dick

  5. Jayson says:

    Here’s the code I’ve used. in module call from worksheet_change. I’ve had no problems yet😉

    Sub pastefix()
    Dim UndoString As String
    Dim srce As Range

    On Error GoTo err_handler

    UndoString = Application.CommandBars(“Standard”).Controls(“&Undo”).List(1)

    If Left(UndoString, 5) “Paste” And UndoString “Auto Fill” Then
    Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo

    If UndoString = “Auto Fill” Then
    Set srce = Selection
    srce.Copy
    ActiveCell.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    Union(ActiveCell, srce).Select
    Else
    ActiveCell.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    End If

    Application.CutCopyMode = False

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Exit Sub

    err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    End Sub

  6. Dick Moffat says:

    Jayson:

    Thanx. Now it’s out there and I hope people will find it if they Google (or Bing) it out there. As usual there’s several ways to skin this cat but this sure looks good to me.

    I would think another way would be to reassign Ctrl V to do a PasteSpecial Values and then remove all Paste capability from every Menu or Ribbon😦 … this looks slicker to me.

    Good stuff.

    Dick

  7. Jayson says:

    I should disclose that I think I found that somewhere. I’ll have to see if I can find it again.

  8. Dick Moffat says:

    Jayson you are an honourable man. I was totally willing to give you credit for the code🙂 ….

    Thanx

    Dick

  9. Harlan Grove says:

    I face this from time to time in a model I maintain but didn’t create. Another alternative (one I don’t necessarily recommend) would be using the Workbook Activate event handler to check the CutCopyMode, and if it’s set, launch Word, paste into it, then copy from Word. This ensures that it’s just formatted text rather than spreadsheet cells in the clipboard when the user is back in Ready mode in Excel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s