Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to make 2 VBA codes to work correctly [Solved/Closed]

Report
-
 Issa -
Hello,

How can I correct the following 2 codes (which written in the same sheet) in order to work correctly, other wise it gives me Error (debug)???
I would appreciate a lot any assistance.

1.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRow As Integer
If Intersect(Target, Range("R11:R20")) Is Nothing Then Exit Sub
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
End Sub

2.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRow As Integer
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

Thank you in advance.

Issa

1 reply

Answer 1 / 1
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
Hi Issa,

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
Report

Hi dear 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
Report
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
Dear Issa,

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

Kind regards,
Trowa
Report

Dear 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
Report
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
Dear 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: 
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 Sub
This code needs to be pasted under ThisWorkbook.

Always happy to assist,
Trowa
Report

Dear 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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!