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

Question

Ask a question Report

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

issa - Mar 13, 2012 at 08:25 AM - Latest reply: Issa

- Mar 20, 2012 at 01:13 AM

Excel Ask a question Report

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

6 replies

Report Respond to TrowaD

TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Mar 15, 2012 at 10:30 AM

0

Thank you

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

issa - Mar 15, 2012 at 10:56 AM

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

TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Mar 15, 2012 at 12:20 PM

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

issa - Mar 16, 2012 at 02:52 AM

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

TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Mar 19, 2012 at 10:44 AM

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

Issa - Mar 20, 2012 at 01:13 AM

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

Answer 1

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

Your reply