How Should I Handle Large Amounts of Varying Text in Excel???

[Marcia]

I have been asked to make a template that will contain formulas that
must be protected because some of the users have few computer skills.
The template also contains several areas that the user must type
large amounts of explanatory text (which may vary from one sentence to
several paragraphs).

I do not know how to handle large amounts of text in Excel. If I
protect the form, it disables the user's ability to re-size the row
height in order to display all the text. Protecting the form also
disables their ability to insert additional rows (and an Excel cell
will only display 1,024 characters). I thought about using a text box
instead of a cell, but protecting the worksheet also disables their
ability to enter the text or re-size it.

If I tell them to unprotect the form, I **KNOW** they will not
remember to protect it back, and they may accidentally delete the
formulas in the process.

Any suggestions on how to handle this would be greatly appreciated!!!
I am unaware of any other options available to me.

Thanks,
Jessi

 

[Dave Peterson]

If you protect the worksheet in code, you can specify that your macro can do
things that users can't.

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

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook--so maybe auto_open/workbook_open would be a good
spot.)

Then in the worksheet's module, you could resize the rows each time they change
something:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Me.UsedRange.Rows.AutoFit
End Sub

The first sub goes in a general module. (I used auto_open.)

If you right click on the worksheet tab that should have this behavior, you can
select View Code and paste that second one in.

Inside the VBE, remember to protect your project:
Tools|VBAProject Properties|Protection tab.

All that said, worksheet protection isn't very secure. There's code posted each
week that'll break it.

(and if you toss in a few of those alt-enters, then you can see more than the
1024 characters limit per cell that's described in help.)

 

[Marcia]

Thanks!! I will definitely make use of this information.

Jessi

_________