VBA code in excel to work for many sheets Closed

Question

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,

aquarelle · Answer

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

VBA code in excel to work for many sheets

1 response

Similar discussions

Subscribe To Our Newsletter!