How to enable font color on protected worksheet?

D

dlterry

How do I enable the changing of font color tool and still protect a worksheet
in Excel 2000? Protection is necessary for formulas in other areas of the
worksheet, but we need to be able to chnge the font color without
unprotecting the worksheet
 
D

Dave Peterson

Maybe you could provide a macro that would unprotect the worksheet, change the
color, then reprotect the worksheet.
 
D

dlterry

I tried make a macro forthis, but it won't work. We have multiple users and
the workbook is to assign order numbers from. I cannot give the password to
everyone. The macro makes you input the password. We can't share the
workbook either because if two people were trying to get the next order
number, they would be duplicated. The pain that can cause is greater as it
involves our corporate people in addition to ourselves. We are in two
different cities so our instant communications can be difficult.

I'm not very good on Excel so maybe there is a trick to creating a macro
that will furnish the password that I don't know about. (So others won't
know what it is) Are there any other suggestions?
 
D

Dave Peterson

I meant that the macro itself should supply the password.

But if you meant that your workbook is shared (via tools|Share workbook), then
my suggestion won't work. Nothing can change the worksheet protection (turn it
on or turn it off) in a shared workbook.

If the workbook isn't shared, how about trying this out on a test copy:

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this in.

Adjust this line to specify the range that should be able to be changed:

Set myRng = Me.Range("a:a,b3:g9,d8")


I put two lines that change colors--the top one is commented out (the leading
apostrophe). The second one is "real".

'Application.Dialogs(xlDialogPatterns).Show
Application.Dialogs(xlDialogActiveCellFont).Show

The commented line shows the dialog for Fill color. The "real" one pops the
dialog for Font.

And notice that the password is buried in the code (twice--once to unprotect and
once to protect). Change that password to what you want.

And to keep prying eyes from looking at your code, you'll want to protect the
project (the VBA portion).

Inside the VBE, (with your project selected),
click on Tools|VBAProject Properties|Protection tab

Give it a nice memorable password.



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

Dim myRng As Range
Set myRng = Me.Range("a:a,b3:g9,d8")

If Intersect(myRng, Target) Is Nothing Then Exit Sub

Cancel = True 'stop normal rightclick menu from showing

On Error GoTo errHandler:
Me.Unprotect Password:="hi"
'Application.Dialogs(xlDialogPatterns).Show
Application.Dialogs(xlDialogActiveCellFont).Show

errHandler:
Me.Protect Password:="hi"

End Sub

========
And you may not know it, but worksheet passwords (and workbook passwords) are
not very difficult to break. Protecting formulas from being overwritten by
mistake is a very good use of protection (as opposed to keeping things
private/secret).
 
Top