Related:
- How to make 2 VBA codes to work correctly
- Combine 2 VBA codes - How-To - Programming
- Combining 2 VBA codes ✓ - Forum - Programming
- VBA Code 2 import many cells not in range from many workbooks ✓ - Forum - Excel
- Vba code to compare 2 excel sheets ✓ - Forum - Excel
- Vba code to search data in excel - How-To - Excel
1 reply
TrowaD
- Posts
- 2660
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 29, 2020
Hi Issa,
Try the following code:
Take care,
Trowa
Try the following code:
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
Take care,
Trowa
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
- Posts
-
2660
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 29, 2020
442I got the same error as you. Rewriting the line a different way solved the error for me.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range) Const PW As String = "HR2011" If Intersect(Target, Range("AS13:AS48")) Is Nothing Then GoTo SecondCode If Target.Value = "Compensation" Then ActiveSheet.Unprotect PW Range("AU" & Target.Row).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 SubKind 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
- Posts
-
2660
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 29, 2020
4421. 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:
Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Name = "Sheet2" Then Cancel = True MsgBox ("You are not allowed to print this sheet.") End If End SubThis 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