Excel File Become Slower when using VBA code.

[Man_of_Sleep]

Hi,

I would to introduce myself, I am a data entry clerk with zero knowledge on VBA.
I am using with excel 2013, after discover this forum, many thing I get to improve my excel file.
Thanks to all members here.

My excel flow is like this:
1) User insert data in column B until Z by row, after finish, then using drop-down list at column AA select YES to locked the entire row.
Problem when I use the VBA (protect the entire row when select YES in AA):
1) User can't autofilter, I already spent 3 days browsing this forum to try to solved, but failed.
2) User can't use add a row with a check box reference to a specific cell in column A (this is done by macro).
3) User can't use the expand/hide grouping column.
4) user can't edit range in column G and H, after the entire row is protected.
5) is there any simplified VBA to make my excel run more faster?

Please teach me how to solve above problem.

Thank you very much in advance for your help.

Below is my code:

Sub addNewRow_r2()
    ' Do not insert a row before the first row.
    Const TopRow As Long = 1
    
    ' Get the active row number.
    Dim rowNum As Long
    rowNum = ActiveCell.Row
    
    If (rowNum > TopRow) Then

        Rows(rowNum).Insert       ' Insert a new row.
        
        ' === add a Check Box ===
        Dim oCB As CheckBox
        Dim c   As Range
        
        Set c = Cells(rowNum, 1)
        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.LinkedCell = .Address
            oCB.Caption = vbNullString
        End With
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cell In Range("AA10:AA110")

If cell.Value = "YES" Then

ActiveSheet.Unprotect "Password"

cell.EntireRow.Locked = True

ActiveSheet.Protect "Password"

End If

Next cell

End Sub

 

[Deek6t8]

Hi

I note that you protect the sheet to not allow your locked cells to be edited however by default when protecting the sheet Insert new row is not allowed, however if you check the option in the protect sheet dialogue you will then retain the ability to insert rows. It would look like this in the code.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True

Hope that helps

 

[Candyman8019]

Similarly, you can use

ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, allowfiltering:=true

to allow the autofilter.

Adding Allowformattingcells:=true MIGHT allow you to group as well, but I haven't tested it.

Worksheet.Protect method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Man_of_Sleep]

I am sorry, I already spent hours of trying to add the code.
But error.

Can you show where to copy & paste the code inside my current code?

 

[Man_of_Sleep]

sorry, only this code to allow the group/ungroup is not possible when in protected mode.

Allowformattingcells:=true

Is there any solution?