Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to enable font color on protected worksheet?
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 |
#2
|
|||
|
|||
Maybe you could provide a macro that would unprotect the worksheet, change the
color, then reprotect the worksheet. dlterry wrote: 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 -- Dave Peterson |
#3
|
|||
|
|||
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? "Dave Peterson" wrote: Maybe you could provide a macro that would unprotect the worksheet, change the color, then reprotect the worksheet. dlterry wrote: 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 -- Dave Peterson |
#4
|
|||
|
|||
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). dlterry wrote: 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? "Dave Peterson" wrote: Maybe you could provide a macro that would unprotect the worksheet, change the color, then reprotect the worksheet. dlterry wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
Is it possible to sort a protected worksheet? | Excel Discussion (Misc queries) | |||
Unprotecting Cells in a Protected Worksheet | Excel Discussion (Misc queries) | |||
Sorting protected worksheet | Excel Discussion (Misc queries) | |||
How can I activate cell shading in a protected worksheet? | Excel Discussion (Misc queries) |