Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fit text in cell of a protected worksheet
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fit text in cell of a protected worksheet
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 On Mon, 7 Jul 2008 10:35:01 -0700, Suzanne wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fit text in cell of a protected worksheet
Thanks so much, i had a feeling you'd be the one to respond : ) Thanks for
coming thru for me yet again. Cheers! -- Thank you -- Suzanne. "Gord Dibben" wrote: 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 On Mon, 7 Jul 2008 10:35:01 -0700, Suzanne wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fit text in cell of a protected worksheet
Thanks for the feedback.
Happy to assist. Gord On Tue, 8 Jul 2008 10:22:09 -0700, Suzanne wrote: Thanks so much, i had a feeling you'd be the one to respond : ) Thanks for coming thru for me yet again. Cheers! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use auto filter in a protected worksheet | Excel Discussion (Misc queries) | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
Help with using Auto-filter in a protected Worksheet. | Excel Discussion (Misc queries) | |||
Auto Filter with Protected Worksheet | Excel Worksheet Functions | |||
HOW CAN I AUTO FILL A CELL WITH TEXT FROM ANOTHER WORKSHEET TEXT . | Excel Worksheet Functions |