XL 2003 VBA: Protection Does Not Allow Row Deletion

T

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
Hi,

I have the below macro assigned to a button on my worksheet:

Code: 
Option Explicit
Sub Add_Prerequisite()
Dim rng As Range
    
Application.DisplayAlerts = False
Application.ScreenUpdating = False
        
        Sheets("Format Control").Range("B14").Value = _
        Sheets("Cover Sheet").Range("B23").Value
    With Sheets("Environment Information")
        .Unprotect
        Set rng = .Columns("A").Find(What:="2", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
        Sheets("Format Control").Rows(14).Copy
        rng.Offset(1).EntireRow.Insert
        rng.Offset(1, 2).Select
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowInsertingRows:=True, AllowDeletingRows:=True
    End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

However, it should but doesn't allow the user to delete a row after it has inserted.

Suspecting a code error I recorded the following while selecting what the user can and can't do in the "Protect my sheet" dialog box to compare and tried it again:

Code: 
Option Explicit
Sub Macro1()

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True
End Sub

Adding this second code to my original (top) macro still will not let the user delete the inserted row even though I've allowed it in the protection properties.

Can anyone point me to where I might be going wrong at all please?

Thank you for your time and any help.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
For the user to be able to delete a row all cells in it must be unlocked. That's the message I got from Excel when I tried it.
 
Upvote 0
Andrew Poulsom said:
For the user to be able to delete a row all cells in it must be unlocked. That's the message I got from Excel when I tried it.
Click to expand...

It would seem "idiot" would be a good title for me on this occasion! In my Format Control Sheet it would seem I hadn't unlocked some cells to enable this to happen.

And commenting out this might have helped:

Code: 
Application.DisplayAlerts = False

Andrew, you've saved my sanity again and I'm hoping this code-blindness has now gone. Awesome VBA skills, I think you've replied to every one of my questions :)

Regards,

Mark.
 
Upvote 0
Hi,

Further to the above I added the following into the sheet code but the scenario I get is this:

When I add several lines to the same cell (using ALT+ENTER) and then tab to the next cell the cell I was typing in that has had it's row height "auto-fitted" by the following code becomes locked for some reason:

Code: 
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
 
Application.ScreenUpdating = False
 
ActiveSheet.Unprotect
 
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
         MrgeWdth = MrgeWdth + cc.ColumnWidth
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
End If
End With
 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowInsertingRows:=True, AllowDeletingRows:=True
        
Application.ScreenUpdating = True
 
End Sub

I added the ActiveSheet.Unprotect and .Protect parts in myself as the code would not run while the sheet was protected.

Can anyone tell me why please as this is preventing users from deleting the row should they need to.

Thank you for reading and for you time.
 
Upvote 0
I thought I had with this line:

Code: 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowInsertingRows:=True, AllowDeletingRows:=True

This might be an eureka moment but is it this part I need to remove:

Code: 
Contents:=True,

Thank you for replying.
 
Upvote 0
I tried the above and the checked the cells properties. The cell's 'Locked' box was green but not with a tick to I can only guess that it is partially locked. the above\below code did little but I suspected it might do that.

TechTank said:
I thought I had with this line:

Code: 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowInsertingRows:=True, AllowDeletingRows:=True

This might be an eureka moment but is it this part I need to remove:

Code: 
Contents:=True,

Thank you for replying.
Click to expand...

Ah the complexities of Excel and VBA :eeek:
 
Upvote 0
I have removed protection on the sheet and typed some information into the cell and used ALT+ENTER to add another line within the same cell. When I press TAB the code below activates and seems to lock (Not the green tick on the Locked box but a green square) just the one cell that I am typing into.

Code: 
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
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Locking of cells with protected sheet not working
Replies
5
Views
457
Eurekaonide
E
N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
212
Peter_SSs
Peter_SSs
L
  • Question Question
VBA - Run time error '9': subscript out of range
Replies
3
Views
235
RoryA
RoryA
G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
405
GK039
G
M
  • Question Question
Deleting unuse row before transfering into pdf
Replies
1
Views
285
NoSparks
N

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top