Auto fit text in linked cells of a protected worksheet
 

Help !!!! new to codes

I have a spreasheet where I need a certain range of linked cells (within another spreasheet) to autofit I used the following code which I found on google and it worked, but as soon as I protect the spreadsheet and I have to protect the cells containing the links as the formulas asked to be locked it doesnt work I get an error code

Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
End Sub


I need the autofit to work in the following locked and protected cells

F11 / F12 / F13 / F14
F16 / F17 / F18 / F19 /F20
F22 / F23 / F24
F26

Can anyone help ?

Thanks

Lucy

Auto fit text in linked cells of a protected worksheet
 

Try this revision............adjust password to suit.

Unprotects the sheet, autofits the rows then re-protects the sheet.

Private Sub Worksheet_Calculate()
Const rngrows As String = "F11:F14, F16:F20, F22:F24"
On Error GoTo endit
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme"
Range(rngrows).EntireRow.AutoFit
endit:
ActiveSheet.Protect Password:="justme"
Application.EnableEvents = True
End Sub


Gord

On Tue, 22 May 2012 15:32:02 +0000, LS3594
wrote:


Help !!!! new to codes

I have a spreasheet where I need a certain range of linked cells (within
another spreasheet) to autofit I used the following code which I found
on google and it worked, but as soon as I protect the spreadsheet and I
have to protect the cells containing the links as the formulas asked to
be locked it doesnt work I get an error code

Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
End Sub


I need the autofit to work in the following locked and protected cells

F11 / F12 / F13 / F14
F16 / F17 / F18 / F19 /F20
F22 / F23 / F24
F26

Can anyone help ?

Thanks

Lucy