On Save Lock Entire Row in a Table

blankus

Board Regular
Joined
Jul 25, 2005
Messages
67
Hey everyone ;)

So I have a Sheet with a Table - called "TB_Request". This table contain a number of columns, some columns require user input, while other are pulling data from other sheet based on the user input.

Initially the sheet, hence the table is protect, however it will have the user input columns unlocked whilst having the rest locked. Once the user press save I would like to have a macro that is triggered to lock any user input cells and re protect the sheet.

I have scoured the internet trying to figure out how I can have this done but haven't managed. Would appreciate any help.

Below is the code I have manage to compile up to now - obviously not working :confused: and the code has been place in the "ThisWorkbook" module Current the table "TB_Request" is located in B4:N30. Column B, C & M are the user input columns whilst D:L & N are pulling data based on the user input. The table header of one of the user input column is [Employee Name] - Mentioning this since I see reference in the below code

VBA Code:
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel As Boolean)

Dim myPW As String
Dim mySheet As Worksheet
Dim myTable As ListObject

'On Error Resume Next
myPW = "12345"
Set mySheet = Application.ActiveSheet
Set myTable = ActiveSheet.ListObjects("TB_Request")

Application.ScreenUpdating = False
mySheet.Unprotect Password:=myPW

If Intersect(Target, Range("TB_Request,[Employee Name]")) Is Nothing Then Exit Sub

If Target.Value <> "" Then
Intersect(Range("B" & Target.Row, Target.Offset(0, -1)), Range(TB_Request & "[#All]")).Locked = True
Else
Intersect(Range("B" & Target.Row, Target.Offset(0, -1)), Range(TB_Request & "[#All]")).Locked = False
End If

mySheet.Protect Password:=myPW, Userinterfaceonly:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, Contents:=True
        mySheet.EnableOutlining = True
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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