Dump the first code I posted and replace with this version which I find does
not chuck up "wrong password" error.

Change "justme" in two places to whatever your password is.

Make sure that Wrap Text and Row Autofit are enabled before protecting.

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

With Target
If .MergeCells And .WrapText Then
ActiveSheet.Unprotect Password:="justme" 'edit to suit
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
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
ActiveSheet.Protect Password:="justme" 'edit to suit
Application.ScreenUpdating = True
End If
End With
End Sub


Gord

On Thu, 25 Mar 2010 12:48:01 -0700, manni
wrote:

Hi Gord,

I think I was, I had copied it from somewhere else...

Apparatly, this is just not my strong point! So, I put in that new code and
then protected the sheet again and another error message popped up. This
time it tells me "Run-time error '1004': The password you supplied is not
correct. Verify that the CAPS LOCK key is off and be sure to use the correct
capitalization."

The sheet protection has a password, but I dont know why it would refer to a
password when I type in an actual cell...

Am I doing something very out of the ordinary with what I want to do here?

Thank you for your patience and your continued help

"Gord Dibben" wrote:

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??

.