Protect using VBA including protection options like autofilter and inserting rows

ALPA

New Member
Joined
Mar 9, 2022
Messages
12
Office Version
  1. 365
Hello, I have a macro that works well in which the user selects an option from a drop down menu and it protects the row from any more changes by other users. However, in this process, the sheets gets unprotected and protected in the background. The problem is that when it gets re-protected, it looses the options i had previously chosen (autofiltering and inserting rows) - how can i add that to the script?

Private Sub worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("v5:v1000")) Is Nothing Then
If Target.Value = "Not seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = False
Me.Protect Password:="123"
ElseIf Target = "Seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"
ElseIf Target = "Imported to Signal" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect Password:="123"

End If
End If
End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Have you tried to record a macro while you protect the sheet with options?
 
Upvote 0
HI, thanks for the answer. No I haven't, I m not sure what sort of Macro I should record and in what part of the process... I was thinking more like adding the protection options part to the Protect expression after or before the password..
 
Upvote 0
You can record a macro whenever you like, do the process you wish, then look back at the macro to see what code it creates. I learned VBA in this way and i still use it from time to time for things that i have forgot.

If you record a macro while protecting the worksheet with options you get the below, i would imagine you can get what you are looking for from it:
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowFiltering:=True
End Sub
 
Upvote 0
Hey! I just copied what you got from the MAcro you run and pasted it in between my expressions, and it worked! It helped me figure the format i needed to use! Thanks so much!

Private Sub worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("v5:v1000")) Is Nothing Then
If Target.Value = "Not seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = False
Me.Protect AllowInsertingRows:=True, AllowFiltering:=True, Password:="123"
ElseIf Target = "Seen" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect AllowInsertingRows:=True, AllowFiltering:=True, Password:="123"
ElseIf Target = "Imported to Signal" Then
Me.Unprotect Password:="123"
Range("i" & Target.Row).Resize(, 13).Locked = True
Me.Protect AllowInsertingRows:=True, AllowFiltering:=True, Password:="123"

End If
End If
End Sub
 
Upvote 0
No problem - record macro's and look at the result will help you out most of the time.

The code from the macro recorder is not the most efficient though but you can google on how to clean it up.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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