Private Sub Worksheet_Change(ByVal Target As Range) Dim cRow As Integer If Intersect(Target, Range("R11:R20")) Is Nothing Then GoTo SecondCode If Target.Value = 3 Or Target.Value = 4 Then ActiveSheet.Unprotect cRow = Target.Row Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = True ActiveSheet.Protect End If If Target.Value = 1 Or Target.Value = 2 Then ActiveSheet.Unprotect cRow = Target.Row Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False ActiveSheet.Protect End If SecondCode: If Intersect(Target, Range("AS12:AS47")) Is Nothing Then Exit Sub If Target.Value = "DTIME" Or Target.Value = "DTOME" Then MsgBox "Please Note: Duty travel compensation is given for travelling during Weekends & Public Holidays ONLY!", vbExclamation, Title:="Human Resource Office Warning" End If If Target.Value = "WOMA" Then MsgBox "Please Note: The Maximum compensation for working outside mission area is (10) hours per day.", vbExclamation, Title:="Human Resource Office Warning" End If End Sub
DON'T MISS
Thanks a lot for your reply, but unfortunatly it worked simi correct, I mean it worked for the second code only but for the first one I received this message: "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"
Please note also that the first code that I sent first was with different cell ranges and actions but here down is the final one that I need. I do the same as you did.
Private Sub Worksheet_Change(ByVal Target As Range)
Const PW As String = "HR2011"
Dim cRow As Integer
If Intersect(Target, Range("AS13:AS48")) Is Nothing Then GoTo SecondCode
If Target.Value = "Compensation" Then
ActiveSheet.Unprotect PW
cRow = Target.Row
Range(Cells(cRow, "AU")).Locked = True
ActiveSheet.Protect PW
End If
SecondCode:
If Intersect(Target, Range("AU13:AU48")) Is Nothing Then Exit Sub
If Target.Value = "DTIME" Or Target.Value = "DTOME" Then
MsgBox "Please Note: Duty travel compensation is given for travelling during Weekends & Public Holidays ONLY!", vbExclamation, Title:="Human Resource Office Warning"
End If
If Target.Value = "WOMA" Then
MsgBox "Please Note: The Maximum compensation for working outside mission area is (10) hours per day.", vbExclamation, Title:="Human Resource Office Warning"
End If
End Sub
Therefor the debug marked by yellow on this code only (Range(Cells(cRow, "AU")).Locked = True) maybe this will be helpful for you to figure out the problem. Even I'm sure no need to explain a lot for you because you are expert with that but just to speed it up to solve it more quickly. I'm in urgent need for it.
Thanks again for all your kind help.
BR,
Issa
I got the same error as you. Rewriting the line a different way solved the error for me.
Here is the code:
Kind regards,
Trowa
Yes it's solved, fantastic :) thank you very much.
I have another 2 questions if you please but not so urgent.
1. If I want to write this code now in the Workbook instead of writing it in every sheet, what is the change could be?
I tried to change only this one but didn't succeed "Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)" instead of "Private Sub Worksheet_Change(ByVal Target As Range)"
2. This question is for something else now, I don't know if it's possible or not.
I have 2 sheets in excel workbook (sheet1, sheet2), I want VBA code (maybe module) in order to forbid the user to print sheet2 ONLY, I mean if the user tried to select (Ctrl+P) to print, it shouldn't be active or if he tried to select (File-print) should be deactivated.
Is it possible to deactivate the print option ONLY for the second sheet?
Thanks a lot for every thing, really appreciated.
Best regards,
Issa
1. What do you want to achieve by finding a new spot for the code? I.e. when do you want the code to be activated? If you want the code to work in other sheets as well; simply copy them to the other sheets.
2. Keep in mind that a user can always turn off macro's. That being said, the following code will prevent Sheet2 from being printed as well as show a message:
This code needs to be pasted under ThisWorkbook.
Always happy to assist,
Trowa
Thanks a lot, yes it solved.
For Macro's yes you are right, it should be always active (low level of security) to keep working.
All the best,
Issa