wrapping text on protected sheets

[Dennis O'Donnell]

I have a multi-sheet workbook with all sheets but the
first sheet competely protected. The entries on the
protected sheets come from data on the first sheet. All
sheets are formatted for the cells to wrap text but when
the information on sheet one transfers to the appropriate
cell location on one of the other sheets that cell does
not expand.

Does anyone have a solution for me?
I'm using Excel 2000.

Thanks for any help!

Dennis

[Dave Peterson]

If you protect the sheet in code (auto_open/workbook_open??), you can have your
macros do things that users can't--including autofit rows.

but it has to be reset each time you open the workbook--xl doesn't remember this
setting when you close the workbook.

So you could have something like this in your workbook_open code.

Option Explicit
Private Sub Workbook_Open()
With Worksheets("sheet2")
.Protect Password:="hi1", userinterfaceonly:=True
End With
With Worksheets("sheet3")
.Protect Password:="hi2", userinterfaceonly:=True
End With
End Sub

then to do the work, add this under the workbook_open event code.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If LCase(Sh.Name) <> "master" Then
Sh.UsedRange.Rows.AutoFit
End If
End Sub

This code goes behind the ThisWorkbook module.

If you wanted the "master" worksheet (change the name to match) included, just
drop that "if" statement.

And if you protected all the sheets in your workbook with the same password, you
could even loop through them:

it would look something like:
Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet
Dim pwd As String

pwd = "hi"

For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) <> "master" Then
With wks
.Protect Password:="hi1", userinterfaceonly:=True
End With
End If
Next wks

End Sub

Remember to protect your project--else your passwords will be viewable. In the
VBE, Tools|VBAProject properties|Protection Tab.

But be aware that worksheet protection is very secure. There's code here posted
weekly that will unprotect worksheets in moments/minutes.

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

======
And if this is a shared workbook (tools|Share workbook), all this goes out the
window. You can't change worksheet protection with a shared workbook.

--

Dave Peterson
ec3...@msn.com