Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

VBA code and protected cells

189 views
Skip to first unread message

manni

unread,
Mar 24, 2010, 10:13:01 AM3/24/10
to
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

unread,
Mar 24, 2010, 12:09:02 PM3/24/10
to
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

unread,
Mar 24, 2010, 1:11:02 PM3/24/10
to
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

unread,
Mar 25, 2010, 10:02:04 AM3/25/10
to
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

unread,
Mar 25, 2010, 10:21:05 AM3/25/10
to
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 <ma...@discussions.microsoft.com>
wrote:

manni

unread,
Mar 25, 2010, 3:48:01 PM3/25/10
to
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:

> .
>

Gord Dibben

unread,
Mar 25, 2010, 5:15:44 PM3/25/10
to
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 <ma...@discussions.microsoft.com>
wrote:

manni

unread,
Mar 30, 2010, 10:09:02 AM3/30/10
to
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!

"Gord Dibben" wrote:

> .
>

manni

unread,
Mar 30, 2010, 10:18:01 AM3/30/10
to
I actually just noticed that once the cell has been clicked on it get's
"locked" even if it was unlocked previously.

Gord Dibben

unread,
Mar 30, 2010, 11:30:39 AM3/30/10
to
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

On Tue, 30 Mar 2010 07:09:02 -0700, manni <ma...@discussions.microsoft.com>

0 new messages