Excel VBA Protected Sheet, after insert line give Error 13

amf22333

New Member
Joined
Jan 23, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
I need a material record sheet which i created in Excel table, and summarize with Pivot Table (for Pivot Table refreshing after entry i use VBA Code), in Sheet i make a column which is "Approved" after approving i want that specific line will become locked (i use VBA code which work properly) but issue is that for Insert Line when i creat a code (which is also working fine)

when i insert line its show Run Time Erro-13 and if i remove "refreshing code" then other thing work,

Code is below

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.RefreshAll

If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
ActiveSheet.unprotect "1234"
Select Case Target.Value
Case "Approved"
Range("A" & Target.Row & ":F" & Target.Row).Locked = True
Case Else
Range("A" & Target.Row & ":F" & Target.Row).Locked = False
End Select
ActiveSheet.protect "1234"


End Sub
 

Attachments

  • Debugg.png
    Debugg.png
    29.7 KB · Views: 13
  • Run Time Error.png
    Run Time Error.png
    40 KB · Views: 13

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,093
Office Version
  1. 365
Platform
  1. Windows
Try changing your If Interect line to this:-
VBA Code:
If Intersect(Target, Range("G:G")) Is Nothing Or Target.Count > 1 Then Exit Sub

When you are inserting a line it is selecting the whole row as the target and Target.Value = "Approved" is erroring out because Target is not a single cell. You can see that if when it error out and you enter debug mode you type into the immediate window,
"? Target.address" (without the quotes)
 
Solution

amf22333

New Member
Joined
Jan 23, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Bundle of Thanks Dear Alex, with your code everything is working now
 

Forum statistics

Threads
1,176,119
Messages
5,901,483
Members
434,896
Latest member
Derquila

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
Top