Excel, Protection question 1

[ccepaulb]

I have a worksheet that is shared by multiple users. Since there were so many hands in the cookie jar, the formulas were getting messed up.
I formatted only the cells that contain formulas to be "locked" when protected and left the rest unlocked. When I go and apply protection to the whole sheet it works as far as entering data in the cells needed and locking the ones with formulas, the problem is that I can not format (e.g. highlight cells) anywhere on the sheet, even though the cell is unchecked for "locked" in format cells?
Is there a way around this?

Thanks, Paul

 

[VE]

When you protect the sheet, there is a list of things you can allow people to do. Click the box next to "select locked cells" and "select unlocked cells". That should fix it.

 

[ccepaulb]

I'm on Excel 2000
I do not see that option when I go to protect the sheet, all I see are options for "contents" "objects" & "scenarios"?

 

[xlbo]

I think those options are only available on XP

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ccepaulb]

I think I just figured out a way around it.
With the formulas already in place in the cells, I made up a phony validation and copied it to every cell that I want protected.
Now when ever you go and try to change the cell, you get an error message and it will not let the value or letters to be typed in the cell

 

[xlbo]

nice - just make sure you remember that when you need to go in and edit a formula ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Note that this easily bypassed by simply copying another cell from any sheet and pasting it over your cells. If you are looking to prevent unintended change then you are probably OK, but anyone who felt the urge could simply bypass this.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[ccepaulb]

Did not think of that, can you think of a way to allow formatting of a cell but not allow them to change the contents?

Thanks, Paul

 

[xlbo]

not without code

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ccepaulb]

Is the code really complicated?
Where could I research this option? I've looked in the help files, searched the web and have not found anything?

Thanks, Paul

 

[xlbo]

depends on the layout of the sheet really - you could use soemthing like this:

Public CurrForm As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then
Application.EnableEvents = False
If Target.Formula <> CurrForm Then
Target.Formula = CurrForm
MsgBox &quot;Don't change the damn formulae&quot;
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.HasFormula Then CurrForm = Target.Formula
End Sub

This picks up the formula in a cell whenever a different cell is selected

If that cell is then changed, it compares the previous formula to the current one and changes it back if it is different

To try this, enter some formulae into a new blank sheet, right click on the sheet tab and choose view code, then just paste the code above from &quot;Public&quot; to the 2nd &quot;End Sub&quot; into the empty module - then try changing the formulae - it still allows formatting as this doesn't change the formula

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ccepaulb]

Goeff,

This works good, but there is one issue.
It will not allow a different formula to be placed in the cell, but it allows a value to be inputed deleting the original formula.
Could I add to the code so that it will not allow a value to be placed in there as well?

Thanks, Paul

 

[xlbo]

slight amendment:

Private Sub Worksheet_Change(ByVal Target As Range)
If HasForm = True Then
Application.EnableEvents = False
If Target.Formula <> CurrForm Then
Target.Formula = CurrForm
MsgBox &quot;Don't change the damn formulae&quot;
End If
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.HasFormula Then
CurrForm = Target.Formula
HasForm = True
Else
HasForm = False
End If
End Sub

This will allow cells with values to be changed but not cells with formulae - won't even allow copy / pasting

Ken - whaddya reckon - anything I've missed ??


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ccepaulb]

Geoff,

I hate to be a nag but it is still not working right.
Did I do something wrong, I changed the two subs to reflect the code above, but did not take out the declaration &quot;Public CurrForm As String&quot;

Thanks for your help on this! very much appreciated

-Paul

 

[xlbo]

my bad - forgot to add the new public variable HasForm

change:
Public CurrForm As String
to:
Public CurrForm As String, HasForm as boolean

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ccepaulb]

Perfect...appears to work well

Thanks again for your help, this is really cool

-Paul

 

[xlbo]

I tested it and it should stop any changing of formulae - either by changing the formula itself, overtyping with a value or pasting something else in

As I said though, it will allow changes to value cells

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244