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 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
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 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