Protected worksheet formatting

[Marcia]

Is there any way that I can have unlocked cells in a
protected worksheet (excel 97) expand (by row height) to
show all text contained in it? The cells are formatted to
wrap text, but if a user needs more space than I have
allotted, they cannot simply adjust the row height on the
protected sheet.

[Dave Peterson]

You could have a macro unprotect the worksheet, adjust the rowheight and then
protect the worksheet again.

But if you protect the sheet in code, your code can do more things than the user
can do.

In a general module:

Option Explicit
Sub auto_open()

With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With

End Sub

(that userinterfaceonly stuff is very important to you.)

Then right click on the worksheet tab that needs to have this behavior. Select
view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireRow.AutoFit
End Sub

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
ec3...@msn.com

[Roland De Bouvere]

If you use Office XP, you can allow users to adjust rowheights in a
protected sheet.

"Marcia" <mwil...@acosta.com> schreef in bericht
news:03f001c388fd$5dc6d100$a101...@phx.gbl...

[Marcia]

This does work - however my rows, particularly the cells
that my end users will be accessing, are merged across
several columns. This macro is only adjusting the row
height according to the content of single cells within the
rrow. So if the merged cell content is more than the
height of the single cell's content in a row, the row
height does not accomodate the text within the merged
cells. Any additional help you can provide is greatly
appreciated.

>.
>

[Dave Peterson]

Merged cells are a problem. But Jim Rech has posted some code that you could
incorporate that simulates the autofit.

http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

Put this in a general module:

Option Explicit
Sub AutoFitMergedCellRowHeight(myActiveCell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim OrigMergeArea As Range
Dim CurrCell As Range
Dim myActiveCellWidth As Single, PossNewRowHeight As Single
If myActiveCell.MergeCells Then
Set OrigMergeArea = myActiveCell.MergeArea
With myActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
myActiveCellWidth = myActiveCell.ColumnWidth
For Each CurrCell In OrigMergeArea
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = myActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Put this under the worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Target.MergeCells Then
Call AutoFitMergedCellRowHeight(Target)
End If

End Sub

The top procedure is a (very slightly) changed version of Jim Rech's routine.

--

Dave Peterson
ec3...@msn.com

[Marcia]

It is rather obvious to me that I need further education
on programming and using VBA. I've followed your
instructions, read through the recommended websites you
gave, and this code isn't working for me. Surely it has
to be something simple that I'm not doing correctly. The
macro for 'AutoFitMergedCellRowHeight' is not showing when
I press ALT+F8 to run it. I removed the worksheet
protection and the auto_open macro and just tried to run
the 'autofit' macro at this point. If I type
in 'AutoFitMergedCellRowHeight' in the macro name field,
then the 'Run' option is enabled, but when I select it,
Visual Basic returns a message "Wrong number of arguments
or invalid property assignment."
The first code you sent me did work, just didn't
accomodate the merged cells that I have. And I followed
the same instructions for installing and running, just not
sure what else needs to be done to make this work.

>.
>

[Dave Peterson]

Marcia, Marcia, Marcia. (I always wanted to say that!)

The autofitmergedcellrowheight macro isn't supposed to be run via alt-F8. It
gets called by something else. That's what the "myactivecell as range" is used
for:
Sub AutoFitMergedCellRowHeight(myActiveCell As Range)

And the thing that calls it is the worksheet_change event.

So if you have all the code in the right spots: Under worksheet_change and a
general module), then you could format a cell with wraptext enabled and merge
enabled (multiple columns--but only one row).

Now make a change to that cell.

(Try it with the worksheet being unprotected to see if you can get it going.
Then come back and add the protection (userinterfaceonly:=true) to see if that
works.

and make one more change once you get it going:

If Target.MergeCells Then
Target.Rows.AutoFit
Call AutoFitMergedCellRowHeight(Target)
End If

The way Jim's macro works is not to adjust the rowheight if the height is too
big. So this reduces it, then calls Jim's code.

(I did copy all the code into a test workbook. (Under thisworkbook, under
sheet1 and in a general module.

Then I formatted a couple of cells, unlocked that cell, and ran the protection
macro. Then did some changes. It seemed to work hunky-dorey for me.)

Marcia wrote:
>
> It is rather obvious to me that I need further education
> on programming and using VBA. I've followed your
> instructions, read through the recommended websites you
> gave, and this code isn't working for me. Surely it has
> to be something simple that I'm not doing correctly. The
> macro for 'AutoFitMergedCellRowHeight' is not showing when
> I press ALT+F8 to run it. I removed the worksheet
> protection and the auto_open macro and just tried to run
> the 'autofit' macro at this point. If I type
> in 'AutoFitMergedCellRowHeight' in the macro name field,
> then the 'Run' option is enabled, but when I select it,
> Visual Basic returns a message "Wrong number of arguments
> or invalid property assignment."
> The first code you sent me did work, just didn't
> accomodate the merged cells that I have. And I followed
> the same instructions for installing and running, just not
> sure what else needs to be done to make this work.
>

--

Dave Peterson
ec3...@msn.com