Need Help Fixing Macros to set Edit Ranges (Getting Run-Time Error)

[evxret]

Hi all, after posting in various forums trying to find a solution for my problem, searching google for hours, I am coming here to see if anyone can help me with this situation I am stuck in at work.
If someone can fix this problem for me, you will end the heartache I've had for the last 5 days, so any help is appreciated very VERY much.

For the sake of me finding my solution faster, I created an example spreadsheet displaying exactly what I need from my macros.

Below, you will see pictured 2 buttons, both with macros attached.

The "Protect Sheet" Button will call a macro that protects the entire sheet, locking down all cells, making this spreadsheet completely un-editable in this state.

The "Unprotect Sheet" button, SHOULD call a macro that Protects the entire sheet as well, but also sets an edit range (in this case A1:A5). (There are also lines in this code related to entering a password; these lines work fine and can be ignored; I am only concerned with what comes after the IF statement located after the password loop.)

HERES MY ISSUE:
"Protect sheet" button works fine. Press button, entire sheet locks and password is set.

Once I press the "Unprotect" button, my password box loads up and allows me to type it in, once I type it in it unlocks, but I get a run-time error on the line of code setting the edit ranges.

Basically, I just need help revising this code to something that will work; I think I'm pretty close to getting the solution already; but I am also pretty amateur at VBA and could be completely off on everything.

Above is the sheet with the 2 buttons, demonstrating what I need done from each of the buttons in the text box to the right of them.

Option Explicit
Sub ProtectSheet123()
    ActiveSheet.Protect PassWord:="abc"
End Sub

Above is the code I used for the "Protect Sheet" button, figured this wouldn't be necessary to include since it works, but decided on it anyway in case it's interfering with the other button.

Option Explicit
Sub UnprotectSheet123()
Dim PassWord As String, i As Integer
  
  i = 0

Do
    i = i + 1
    If i > 3 Then
      MsgBox "Only 3 Password Tries Allowed. Application will now save & close."
        Application.DisplayAlerts = False
        ThisWorkbook.Saved = True
        Application.Visible = False
        Application.Quit
      Exit Sub
    End If
    
    PassWord = InputBox("Enter Password (Accessable by admin only)")
    
Loop Until PassWord = "abc"
    
    If PassWord = "abc" Then
   With Sheets("Testsheet")
  .Select
  .Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), PassWord:=""
End With
    End If
    
End Sub

Above is the code I used for the "Unprotect Sheet" button. This is the code that is failing, and after testing more than 10 iterations of the same few lines of code, all doing the same thing but written in different ways, I am stumped.

Here are the lines that are failing.

  .Protection.AllowEditRanges("EditableRange").Delete
  .Protection.AllowEditRanges.Add Title:="EditableRange", Range:=Range("A1:A5"), PassWord:=""

Once again, I appreciate any possible solutions I can test for this, even if it doesn't fully help my problem, if you can get me one step closer to figuring out what's wrong, I don't know how I will repay.

 

[BSALV]

if you limit the access with

Sheets("MySheet").scrollarea="A1:A5"

Isn't that enough
(PS. security isn't/wasn't the highest priority in excel, so for skilled people, any protection is easy to overrule)

 

[evxret]

if you limit the access with

Sheets("MySheet").scrollarea="A1:A5"

Isn't that enough
(PS. security isn't/wasn't the highest priority in excel, so for skilled people, any protection is easy to overrule)

Im not actually worried about people tampering with the code to try and unlock it; these numbers aren't significant enough for them to want to go any lengths to edit them after they're locked.
Inserted the code you recommended; this somewhat works. However, for my case, the cells I will need editable are going to be scattered around the spreadsheet, (A5,B10,A8,D15, etc). I just used range A1:A5 for the sake of it being an easy example. If someone can correct the code that creates the Edit Range, I can just adjust those cell values to match what I need on my sheet.

 

[BSALV]

Sub Test()

     With Sheets("EVXRET")
          .Unprotect "MyPassword"                               'sheet is protected with this password

          Set c = .Range("A5,B10,A8,D15")                       'your cells
          c.Interior.Color = RGB(0, 255, 0)                     'make them green to mark them

          On Error Resume Next
          .Protection.AllowEditRanges("MyRange").Delete         'delete in case it existed
          On Error GoTo 0

          .Protection.AllowEditRanges.Add Title:="MyRange", Range:=c, Password:="EVXRET"     'add with (another) password

          .Protect "MyPassword"                                 'protect the sheet

     End With

End Sub

 

[Micron]

That looks like the same code and problem as here

Creating a Macro that will Lock everything besides data-entry cells

Hi all, I had help creating a VB macro that is attached to buttons that will lock or unlock a workbook based on a macro-attached button press. I got the buttons to function the way they need to, the problem lies now that when I press "unprotect", the entire sheet is left vulnerable to formula &...

www.mrexcel.com

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.