Auto fit text in cell of a protected worksheet

[Suzanne]

Good afternoon.

I have a form with protection and validation in several cells; some are left
unlocked/no validation to allow for free form text entries. My problem is
that when i protect the sheet, Excel doesn't auto fit the text in the
column/row and the user is not able to either. (I don't want to allow Format
row height / column width in Protection options, as I don't want any hidden
rows/columns unhidden, which these protection options allow)

Is there a way to allow the user to modify the row height and column with of
the unlocked text cells (as needed) while the sheet remains protected?

Or a way for Excel to do this automatically? I have wrap text selected in
the cell format options.

Thanks! -- Suzanne.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then 'adjust to suit
Me.Unprotect Password:="justme"
n = Target.Row
With Me.Range("A" & n)
If .Value <> "" Then
.Cells.WrapText = True
.AutoFit = True
End If
End With
End If
enditall:
Application.EnableEvents = True
Me.Protect Password:="justme"
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module. Alt + q to return to the Excel
window.


Gord Dibben MS Excel MVP

 

[Suzanne]

Thanks so much, i had a feeling you'd be the one to respond : ) Thanks for
coming thru for me yet again. Cheers!

 

[Gord Dibben]

Thanks for the feedback.

Happy to assist.


Gord