1

The problem

I manage a weighted decision matrix in an Excel file for a team of 15 people. There are 10 columns in the file that contain formulas. The formulas calculate a total weighted score based on the options users select in other cells.

Sometimes a user needs to remove an entry. Common removal methods for my team include selecting an entire row and doing 1 of the following:

  • Pressing the delete key.
  • Right-clicking and selecting "Clear Contents".
  • Selecting "Clear Contents" from the Editing group on the Home tab.

All of these methods also clear the formulas that calculate the weighted scores.

The methods described above are intuitive for most of my team, so I’d like to find a way to let people use these methods without clearing the formulas.

The dream

Allow users to clear everything but the formulas when they use any of the methods described above to clear cells.

Things I've tried

  • Locking just the formula cells. This prevents users from clearing the formulas, but they can’t clear contents in the other cells when they select the whole row because the formula cells in that row are locked.

  • The macro below. The macro will clear constants and spare the formulas, but I can't get it to trigger automatically when a "clear contents" event happens. Expecting all the members of my team to run this macro instead of using their regular method for clearing contents is a fool's errand I fear.

    Sub ClearOnlyValues()
  Dim Cel As Range

  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  For Each Cel In Selection
    If InStr(Cel.Formula, "=") = 0 And Cel.Value <> "" Then
     Cel.ClearContents
  End If
  Next Cel

  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic

End Sub

  • Google. The only macros I could find all had to be run manually each time you want to clear contents and countless posts demo'ing the Go To Special method of clearing constants. See fool's errand above. If they can't clear cells intuitively, we'll descend into chaos. Cats and dogs living together. It'll be anarchy.

  • Advanced Excel users who are way smarter than me. They're all stumped.

If you're still with me, thanks for taking the time.

vacip
  • 5,246
  • 2
  • 26
  • 54
Nicks
  • 11
  • 1
  • 1
  • 3
  • 1
    Can you protect the sheet? That way the users could only clear contents from unlocked cells (select cells -> right-click and select Format Cells -> Protection -> unchecked "Locked" -> OK and then protect the sheet) – tigeravatar Mar 29 '16 at 22:37
  • Use `worksheet_change`? If cell is getting deleted, grab the formula, clear cell then put formula back. – findwindow Mar 29 '16 at 22:43
  • Possible, but all 3 of these methods would require hooking into the Windows event dispatching for Excel and monitoring the user inputs that would cause the cells to clear. A ton of work compared to the rubber hose method, but if you want to explore it [this post](http://stackoverflow.com/a/11154285/4088852) will get you started toward the relevant APIs. – Comintern Mar 29 '16 at 22:44
  • @findwindow - Unfortunately, `Worksheet_Change` fires *after* the content is already gone. AFAIK, the only Worksheet events that allow cancellation are `BeforeRightClick` and `BeforeDoubleClick`, and `BeforeDoubleClick` would fire *before* the context menu was displayed, so it wouldn't be a lot of help. – Comintern Mar 29 '16 at 22:47
  • @Comintern that's why there's `application.undo` ~_^ – findwindow Mar 29 '16 at 22:48
  • @findwindow - Also why there's the rubber hose. – Comintern Mar 29 '16 at 22:52
  • Use `Worksheet_Change` to recreate *all* of the formulas whenever there's a change which intersects with *any* of the cells containing a formula – barrowc Mar 29 '16 at 23:10
  • 1
    @barrowc - Easier said than done. How do you easily figure out what's missing if entire rows or columns get deleted? For example, if I delete column B:B, the contents that are in the Range that gets passed to `Worksheet_Change` are what used to be in C:C before the event handler gets it. – Comintern Mar 29 '16 at 23:16
  • 1
    @Comintern - fair point but the question seems to be specifically about clearing contents and not about deleting cells. The Protect Sheet options can be used to prevent deletion of cells while still allowing clearing of contents – barrowc Mar 29 '16 at 23:22
  • 2
    Raul, even if you get it to work, that will mean that a macro has to run every time the workshet changes, to check if cells were cleared. (On the Worksheet_change event.) This means your users will have **no undo**. Undo history will be constantly cleared. this is worse than anything you could do to your users. So, in my opinion, there is no good programmable solution. The solution is upgrading your users, not your table. Teach them a better way to clear only the used cells. – vacip Mar 29 '16 at 23:27
  • You could use a Table, place your calculations next to the table (make sure they are not part of the table), and teach your users to select table rows instead of full rows before deleting. – vacip Mar 29 '16 at 23:31
  • @tigeravatar - I think I've already tried what you are suggesting, but it didn't work out for my users. See the 1st item in the "Things I've tried" section. If I misunderstood your suggestion, please let me know. – Nicks Mar 31 '16 at 22:03
  • @Comintern - Thanks for the suggestion and the link. I'll definitely try the rubber hose. :) – Nicks Mar 31 '16 at 22:04
  • @vacip - Thanks for pointing out the no undo issue. It didn't occur to me. I'm going to give your table suggestion a try. – Nicks Mar 31 '16 at 22:06

1 Answers1

7

Try these constants in your Excel VBA code:

Sheet1.Range("B3:E20").SpecialCells(xlCellTypeConstants).ClearContents

No need to protect the cells. This will clear cell contents without erasing formulas .

valera5505
  • 3,397
  • 1
  • 13
  • 20
Salim Talal
  • 71
  • 1
  • 2