Tags Tags

Auto fit text in linked cells of a protected worksheet

May 22nd, 2012 - 11:32 am ET by LS3594 | Report spam
Help Create a new topic
Help !!!! new to codes

I have a spreasheet where I need a certain range of linked cells (withi
another spreasheet) to autofit I used the following code which I foun
on google and it worked, but as soon as I protect the spreadsheet and
have to protect the cells containing the links as the formulas asked t
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

Luc


LS3594
email Follow the discussionReplies 1 replyReplies Make a reply

Replies

#1 Gord Dibben
May 23rd, 2012 - 02:52 pm ET | Report spam
Try this revisionadjust 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

Similar topics

Make your own search :