macro2 autofit rowheight allows protection 2b removed without pass

[nanook]

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

 

[Gord Dibben]

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

 

[nanook]

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" !!

 

[Gord Dibben]

Thanks for the feeback.

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


Gord

 

[nanook]

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.

 

[nanook]

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?

 

[Gord Dibben]

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

 

[nanook]

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!

 

[Gord Dibben]

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

 

[Shane Devenshire]

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

 

[nanook]

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