VBA code and protected cells

[manni]

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

 

[Reg]

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]

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]

Try this

1. Unprotect the *workbook*
2. Unprotect the *worksheet(s)*
3. Does the code now run properly/as expected?

If it does then the problem is caused by the macro trying to update a
something that is protected the solution is to a) change the macro so it
doesnt do any updates or b) change the macro to do the unprotect (ask for a
password, unprotect, do update, re-protect with same password) or c) run the
worksheet without protection or d) change the macro to do the unprotect and
hard code the password into the macro

I prefer b) but it takes the most work to do properly so you need to decide
if one of the alternates is better for your product


hth
Reg

 

[Gord Dibben]

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

 

[manni]

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]

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

 

[manni]

Hi Gord,

Thank you that's perfect! I didn't get any error messages. Buttt..... now
I have a new issue... After those cells to which the code applies have been
clicked on, I'm not allowed to go back to them. So if I entered information,
I can't go back and edit it. And if I clicked on it, didn't enter anything
and then went to a different cell and wanted to return to that box, it won't
let me.

Any thoughts on my latest issue???

Thank you again for all your help!

 

[manni]

I actually just noticed that once the cell has been clicked on it get's
"locked" even if it was unlocked previously.

 

[Gord Dibben]

More revisions...........get rid of all previous code and try this version.

Make sure you first unlock desired merged cells and set them for wraptext.

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
With ActiveSheet
.Protect Password:="justme", userinterfaceonly:=True
.EnableSelection = xlNoRestrictions
End With
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

Application.ScreenUpdating = True
End If
End With
End Sub


Gord