Formatting unlocked cells in a protected worksheet.

[Guest]

How can I format the text in an unlocked cell in a protected Excel2000 worksheet? For example, I want to underline (or make bold) a portion of the text in an unlocked cell. Even though the cell is unlocked, Excel2000 will not let me format the text in the unlocked cell as long as the worksheet is proteted. Is there a way to allow my users to format text in an unlocked cell in a protected Excel2000 worksheet? Paul S. Natanson (e-mail address removed) 11-14-03 Home phone 1-908-630-0406 (9 AM - 9 PM Eastern USA time).

 

[Dave Peterson]

You could provide a macro to the user that:

unprotects the worksheet
does the formatting
protects the worksheet

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(xlDialogFormatFont).Show
'or for more options
'Application.CommandBars("Format").Controls("cells...").Execute
Me.Protect Password:="hi"
Cancel = True 'don't show usual rightclick menu
End If

End Sub

I wasn't sure if you wanted a basic formatting dialog to show up or the the
whole Format|Cells dialog to appear. Uncomment the one you want.

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

 

[Dave Peterson]

If you decide to show the Format|cells dialog, be aware that the user could
protect that cell.

 

[Dave Peterson]

And if you need this on a shared workbook, you're out of luck.

You can't change the worksheet protection in a shared workbook.