From the sounds of it you are using event code by Greg Wilson.

Here is revised code to allow for protected sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim ProtectStatus As Boolean
With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ""
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ""
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 24 Mar 2010 10:11:02 -0700, manni
wrote:

What kind of details do you need? Sorry, I'm new to all this.

I tried deleting the original code, unprotected the sheet, put in the code,
then i unlocked the cells and then i re-protected the sheet again. I clearly
didn't do something right because it got the same message "Run-time error
'1004': Unable to set the MergeCells property of the Range class"

I have no idea what that means...

It asks me if I want to debug and then the view code pops up with
"ma.MergeCells = False" highlighted in yellow.

Thoughts??

"Reg" wrote:

Without having some more detail this is a shot in the dark - but VBA will
error if your code is trying to make changes to a protected sheet, you need
to unprotect, run the code, re-protect it.

hth
RegMigrant

"manni" wrote:

Hello -

I created a form template in excel and I unlocked various cells and then
protected the sheet so that users who completed the form would only be able
to make changes to specific areas.

I decided later that in "free answer" sections I wanted the merged cells to
expand to fit the data the person enters. So, I wrote a VBA code based on a
search that I'd performed on here.

Individually both of the above worked. But after writing the VBA code and
then protecting the sheet, I no longer had access to click on the free
response area (the area with the code). So, I went back in and "unlocked"
those cells and protected the sheet again. Unfortunately, I seem to have
done something wrong, because I get an error message when data is entered in
this section, the cell won't expand and it talks about "debugging" and brings
up the "view code" area.

Could someone please help me??