UnLocked Cells No Color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have a spread sheet which is passed around frequently. Therefore I have locked a number of cells to protect its information. However, its necesary that other change the color of the unlocked cells.

The problem is that once the sheet is protected the unlocked (and therefore changeable) do not have any color icon and so I cant change them. I cant figure out a way to have locked cells and still allow color changes on the unlocked without giving the password away (which of course defeats the purpose)

Cheers!
 
This capability was added in xl2002.

But if you're running an earlier version, maybe you could provide a macro to the
user that unprotects the sheet, does the formatting, and reprotects.

You may want to use the rightclick to let the users run the macro.

If you like this idea, you could rightclick on the worksheet tab, select view
code and paste this in:

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub
If Target.Locked = False _
And Me.ProtectContents Then
Me.Unprotect Password:="hi"
Application.Dialogs(xlDialogPatterns).Show
Me.Protect Password:="hi"
Cancel = True 'don't show usual rightclick menu
End If

End Sub


Don't forget to lock the VBA Project, too. Else you'll have inquisitive types
looking at your code and seeing the password.

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top