Prevent editing/deleting of named cell ranges without worksheet protection, and...

[halesowenmum]

That's it really. I don't want to be having to use passwords to protect a sheet in any way so want to use just VBA. I have to apply this to N5:N5000 so that no one can edit, delete, cut, remove the formula that is in there. I did find great code for this once but it was years ago and I can't find it any more - it worked great except for one thing.

If I want to go back in and change that formula or add a row or copy the formula from that column to somewhere else, then

I​

need to be able to do so - so is there a way of 'switching off' the VBA and then 'turning it back on again'?! I recall any time I wanted to add a row or whatever, I had to go in, delete the vba, make the change, go back, paste it back in - I wondered if there was a slightly more refined way of doing that!!

 

[footoo]

Put this in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [N5:N5000]) Is Nothing Then
    If Me.Buttons("Button 1").Characters.Text = "Allow Changes" Then
        MsgBox "Changes to N5:N5000 not allowed."
        With Application
           .EnableEvents = False
           .Undo
           .EnableEvents = True
        End With
    End If
End If
End Sub

Add a button to the worksheet and assign this macro :

Sub Toggle_Allow()
Dim PW$
With ActiveSheet.Buttons("Button 1").Characters
    If .Text = "Allow Changes" Then
        PW = InputBox(prompt:="Enter password to continue")
        If PW = "Your PassWord" Then  .Text = "Do Not Allow Changes"
    Else
        .Text = "Allow Changes"
    End If
End With
End Sub

 

[halesowenmum]

Thank you. Added to module, then inserted button from developer and added in the macro vba.

Tried deleting in one of the cells, it still let me delete so 'clicked' the button and tried again. It still let me delete the contents of the cell with the formula in it.

Any ideas?? Should something happen when I click the button (that I can see or.... )

 

[footoo]

Sorry, should have given some set-up instruction :
After adding a button ("Button 1"), when you first click the button the text should change to "Allow Changes".
Click it again and the text should change to "Do Not Allow Changes"
Click it a third time (it should change to "Allow Changes") and it should be set up to do what you want.

 

[halesowenmum]

Am assuming then that the password would be asked for only when the button was clicked such that it enabled editing of the cells in question.

No, I'm just lost - tried to do what was suggested, it doesn't change if I click it, and it says there's an error - runtime error 1004 Unable to get the buttons property of the worksheet class and the errant code is [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]With ActiveSheet.Buttons("PMO only").Characters (I'd named the title of the button so it kept folks out and edited it in the VBA editor but no dice [/FONT]

 

[footoo]

Am assuming then that the password would be asked for only when the button was clicked such that it enabled editing of the cells in question.

Yes.

Use a button from the Form Controls and put the macro in a normal module.
Make sure the button name in the macro is correct.

 

[footoo]

Make sure the button name in the macro is correct.

Use the Default Name : https://www.contextures.com/excelformcontrolbuttons.html