VBA code in excel to work for many sheets [Closed]

Report
-
Posts
7083
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 25, 2020
-
Dears who can help me.

I have excel file includes 30 sheets for different 30 employees.
I have the following VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)

Const PW As String = "issa2011"
Dim cRow As Integer
If Intersect(Target, Range("AQ12:AQ47")) Is Nothing Then Exit Sub

If Target.Value = "DTIME" Or Target.Value = "DTOME" Then
ActiveSheet.Unprotect PW
cRow = Target.Row
Range(Cells(cRow, "AR"), Cells(cRow, "AS")).Locked = True
ActiveSheet.Protect PW
Else

If Target.Value = "DTIME" And Target.Value <> "" Then
MsgBox "Please Note: there no need to fill time and thank you.", vbExclamation, Title:="Human Resource Office Warning"
End If
End If

If Target.Value <> "DTIME" Or Target.Value <> "DTOME" Then
ActiveSheet.Unprotect PW
cRow = Target.Row
Range(Cells(cRow, "AR"), Cells(cRow, "AS")).Locked = False
ActiveSheet.Protect PW
End If

End Sub

I need to put/write this code once only in ThisWorkbook to work for all 30 sheets otherwise I have to copy and paste it in each of 30 sheets.

Thank you in advance for any kind assistant.
Best regards,

1 reply

Posts
7083
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 25, 2020
477
Hi,

I think it is not necessary to write your code for each worksheet and I think that you should just try to write your code in "ThisWorkbook" like this :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const PW As String = "issa2011"
Dim cRow As Integer
If Intersect(Target, Range("AQ12:AQ47")) Is Nothing Then Exit Sub

If Target.Value = "DTIME" Or Target.Value = "DTOME" Then
ActiveSheet.Unprotect PW
cRow = Target.Row
Range(Cells(cRow, "AR"), Cells(cRow, "AS")).Locked = True
ActiveSheet.Protect PW
Else

If Target.Value = "DTIME" And Target.Value <> "" Then
MsgBox "Please Note: there no need to fill time and thank you.", vbExclamation, Title:="Human Resource Office Warning"
End If
End If

If Target.Value <> "DTIME" Or Target.Value <> "DTOME" Then
ActiveSheet.Unprotect PW
cRow = Target.Row
Range(Cells(cRow, "AR"), Cells(cRow, "AS")).Locked = False
ActiveSheet.Protect PW
End If

End Sub 


Regards

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!