Protect worksheets with VBA Macro

[jalrs]

Hello guys,

In order to finish my project, i need to protect my worksheets so the end users can only edit the Ranges i want them to edit, so they don't mess around on the worksheet and everything runs smoothly.

I've done some research, came to some codes, but none did what i was expecting. So i look for any helpers here, since i been quite lucky over here.

So what is my goal here:

I want to protect columns from "A:L" and also column "N". Why?

Because my next macro separates all the department sheets into 10 separate workbooks and sends them to the each department. They should only be able to select values from a dropdown list associated to Column "M" where Column "N" gets auto filled according to the value selected on previous column. As you can see on the example sheet I attached, a returns b and so on. These values are generated from a table, where i applied vlookup.

The other missing columns, O P Q R and S are for free use of the end user. Additionally on the protected ones, they should be able to select, format, order and autofilter.

Any help is appreciated,

Best regards!

 

[jalrs]

Good morning all again,

Here's my demo (demo), i came to this code, after doing my research here and there, on my data sensitive version:

Sub protectSpecificSheets()
'makes all cells of the sheet locked
  Dim shtNmAr As Variant
  Dim sht As Variant, pw As String
 
  'mention sheets names to be protected
  shtNmAr = Array("Porto", "Sporting", "Benfica", "United", "City", "Liverpool", "Boavista", "Chelsea", "Arsenal", "Nacional", "TAB_FDB")
  pw = "abc"

  For Each sht In shtNmAr
    On Error Resume Next
    With Worksheets(sht)
      .Unprotect pw
      .Cells.Locked = True
      .Protect pw
    End With
  Next sht
End Sub

My problem is that i don't know how to specific a lock range. On "Template2.xlsm" i wanna lock all sheets except "TAB_FDB" within range "A:AW" + "AY".
As for "TAB_FDB" all worksheet should be locked
"Readme" and "Resumo" worksheets are irrelevant for the matter.

Thanks

 

[jalrs]

Any help on this?

Thanks

 

[jalrs]

This did the job of protecting cells with data in.

sub proteger()

dim wb1 as workbook
dim ws3 as worksheet

set ws3=wb1.Worksheets("Pendentes")

ws3.Protect Password:="blabla", _
        DrawingObjects:=False, _
        Contents:=True, _
        Scenarios:=False, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=False

end sub