macro2 autofit rowheight allows protection 2b removed without pass

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nanook - 21 Jan 2008 15:50 GMT

I'm using Excel 2003 and am creating a template for others to use. However,
in order to restrict users from changing too much I have protected the sheet
and only unlocked some cells.
I then had a problem with the autofit not working where cells were merged
(and text wrapped) and the sheet password protected, so used this code
(Thanks Greg Wilson):

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 ' "password"
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 ' "password"
End If
End With
End Sub

However, I've now come across an issue - if someone writes text in a cell
(which is long enough to wrap and the height to be adjusted) and then clicks
either tab or enter to move to another cell, a pop-up appears asking for a
password in order to unprotect sheet. If the user then presses escape,
without entering a password, and then tools-protection-unprotect sheet it
doesn't ask for the password and just
unprotects!!
This means the document is not securely protected - what have I done wrong?
Thanks

Reply to this Message

Gord Dibben - 21 Jan 2008 17:11 GMT

You have provided no password in your code.

Try this revision............................

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"
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"
Application.ScreenUpdating = True
End If
End With
End Sub

Gord Dibben MS Excel MVP

>I'm using Excel 2003 and am creating a template for others to use. However,
>in order to restrict users from changing too much I have protected the sheet
[quoted text clipped - 46 lines]
>This means the document is not securely protected - what have I done wrong?
>Thanks

Reply to this Message

nanook - 21 Jan 2008 17:39 GMT

Thanks, that's great.
I've also just realised that the previous code I had would have worked had I
not left the apostrophe in before "password" !!

> You have provided no password in your code.
>
[quoted text clipped - 82 lines]
> >This means the document is not securely protected - what have I done wrong?
> >Thanks

Reply to this Message

Gord Dibben - 21 Jan 2008 19:17 GMT

Thanks for the feeback.

Did you try the previous code after removing just the apostrophe?

Gord

>Thanks, that's great.
>I've also just realised that the previous code I had would have worked had I
[quoted text clipped - 86 lines]
>> >This means the document is not securely protected - what have I done wrong?
>> >Thanks

Reply to this Message

nanook - 23 Jan 2008 01:05 GMT

Yes, I tried it and they both seem to work the same, except I've now come
across a different issue....

When I protected the worksheet I checked some boxes to "Allow users of this
worksheet to:", including "format rows", thus allowing them to hide rows if
they want. However, after I edit text in a merged cell it appears that the
protection is re-set to the standard settings of just "Select locked cells"
and "Select unlocked cells". How can I make it retain my settings?

Thanks so much for your help.

> Thanks for the feeback.
>
[quoted text clipped - 92 lines]
> >> >This means the document is not securely protected - what have I done wrong?
> >> >Thanks

Reply to this Message

Gord Dibben - 23 Jan 2008 17:10 GMT

nanook

You have to code those settings in when you re-protect the sheet.

ActiveSheet.Protect Password:="justme" becomes this.....................

With ActiveSheet
.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:= _
True
.EnableSelection = xlNoRestrictions
End With

To get similar code to suit, record a macro whilst protecting the sheet and
marking the checkboxes you want.

Gord

>Yes, I tried it and they both seem to work the same, except I've now come
>across a different issue....
[quoted text clipped - 103 lines]
>> >> >This means the document is not securely protected - what have I done wrong?
>> >> >Thanks

Reply to this Message

nanook - 12 Sep 2008 17:11 GMT

Hi again,
This works perfectly for the example settings that you chose; however, I
would like to put the protection back on with different settings:
-Select Locked Cells
-Select Unlocked Cells
-Format Rows
-Use AutoFilter
Could you tell me what the code would be for these as I tried to guess and
got it wrong! (Specifically: Use AutoFilter)
Also, is there somewhere I can get a full list of the code for all settings,
so I don't have to ask every time I want to use different settings?
Thanks!

> nanook
>
[quoted text clipped - 125 lines]
> >> >> >This means the document is not securely protected - what have I done wrong?
> >> >> >Thanks

Reply to this Message

Square Peg - 12 Sep 2008 17:58 GMT

>Hi again,
>This works perfectly for the example settings that you chose; however, I
[quoted text clipped - 8 lines]
>so I don't have to ask every time I want to use different settings?
>Thanks!

You guys have done a lot of work. It sure looks to like like something
like this ought to be built into Excel.

Reply to this Message

Shane Devenshire - 13 Sep 2008 07:01 GMT

Hi,

In effect this is built into Excel. Choose Tools, Macros, Record New Macro.
After you name the macro (no spaces) and hit OK. Do all the steps you want
to program, but only those. Then choose the command Tools, Macros, Stop
Recorder. Look at the code Excel has created and modify as necessary.

Recording is the best way to start learning VBA, be happy you are in Excel,
there is no recording feature in Access to help you along.

Cheers,
Shane Devenshire

>>Hi again,
>>This works perfectly for the example settings that you chose; however, I
[quoted text clipped - 12 lines]
> You guys have done a lot of work. It sure looks to like like something
> like this ought to be built into Excel.

Reply to this Message

Square Peg - 13 Sep 2008 15:59 GMT

>Hi,
>
[quoted text clipped - 5 lines]
>Recording is the best way to start learning VBA, be happy you are in Excel,
>there is no recording feature in Access to help you along.

I meant that better tools for handling locks at the cell level should
be built into Excel so that macros would not be needed. It should be
possible to temporarily unlock a range without removing the protection
for the entire sheet. This would make development much easier and less
error-prone.

Reply to this Message

nanook - 15 Sep 2008 10:27 GMT

Thanks Shane (and Gord),
Your pointers really help, and it's getting me further along with
understanding VBA. In fact I should be able to figure a lot more things out
this way!
Cheers

> Hi,
>
[quoted text clipped - 25 lines]
> > You guys have done a lot of work. It sure looks to like like something
> > like this ought to be built into Excel.

Reply to this Message

Gord Dibben - 12 Sep 2008 21:07 GMT

Why guess?

Use the macro recorder to get all the code you want.

For your specific request............

With ActiveSheet
.Protect Password:="justme", AllowFormattingRows:=True, _
AllowFiltering:=True
.EnableSelection = xlNoRestrictions
End With

Gord

>Hi again,
>This works perfectly for the example settings that you chose; however, I
[quoted text clipped - 138 lines]
>> >> >> >This means the document is not securely protected - what have I done wrong?
>> >> >> >Thanks

Reply to this Message

nanook - 23 Jan 2008 01:52 GMT

Yes, and both versions of the code seemed to work, but now I have another
problem:
When I protect the sheet I tick some additional boxes in the "Allow all
users of this worksheet to:" list, including "Format rows", which then allows
users to hide rows if they like. However, it appears that in allowing the
autofit of the row height, this list is reset to just have the first 2 boxes
checked (select locked & unlocked cells), so then rows cannot be hidden by
all users any more! Is there a way of making it check which boxes were ticked
and retaining the settings as set when protection was put in place?

> Thanks for the feeback.
>
[quoted text clipped - 92 lines]
> >> >This means the document is not securely protected - what have I done wrong?
> >> >Thanks

Reply to this Message