Two Private Worksheet_Change

E

Enio Goncalves

New Member
Joined
Nov 4, 2014
Messages
5
Hello.
I'm new to VBA language. I have 2 Worksheet_Change routines that work fine by themselves,but i need to put them both in the same sheet.
I know that there can be only one per sheet, so I guess that the only way is to bind both routines into one, but I don't know how...

First one (timestamps cell in Column "E" when cell in column "H" is filled)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
Cells(Target.Row, 5).Value = Date + Time
Application.EnableEvents = True

End If
End Sub


Second one (locks intire row - except a few cells - when cell in column "C" is filled)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then '3=column C
If Target.Value = "" Then
Me.Unprotect Password:="xxx"
Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = False
'leave these locked:
Range("D" & Target.Row).Locked = True
Range("E" & Target.Row).Locked = True
Range("P" & Target.Row).Locked = True
Range("AA" & Target.Row).Locked = True
Range("AD" & Target.Row).Locked = True
Range("AX" & Target.Row).Locked = True
Me.Protect Password:="xxx", AllowFiltering:=True
Else
Me.Unprotect Password:="xxx"
Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = True
'dont block these:
Range("X" & Target.Row).Locked = False
Range("BA" & Target.Row).Locked = False
Range("BC" & Target.Row).Locked = False
Range("BE" & Target.Row).Locked = False
Range("BG" & Target.Row).Locked = False
Range("BI" & Target.Row).Locked = False
Range("BK" & Target.Row).Locked = False
Range("BP" & Target.Row).Locked = False
Me.Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End If
End Sub

I apreciate all the help you can give me
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Welcome to the Board!

Because they access different ranges, you should just be able to take the body from one and place it under the other (so that you have two IF...THEN blocks).
 
Upvote 0
Joe4 said:
Welcome to the Board!

Because they access different ranges, you should just be able to take the body from one and place it under the other (so that you have two IF...THEN blocks).
Click to expand...

Hi there
First of all, thank you for taking the time to try and help me
Tryed what you said, did the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
Cells(Target.Row, 5).Value = Date + Time
Application.EnableEvents = True

End If

If Target.Column = 3 Then '3=column C
If Target.Value = "" Then
Me.Unprotect Password:="xxx"
Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = False
'leave these locked:
Range("D" & Target.Row).Locked = True
Range("E" & Target.Row).Locked = True
Range("P" & Target.Row).Locked = True
Range("AA" & Target.Row).Locked = True
Range("AD" & Target.Row).Locked = True
Range("AX" & Target.Row).Locked = True
Me.Protect Password:="xxx", AllowFiltering:=True
Else
Me.Unprotect Password:="xxx"
Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = True
'dont block these:
Range("X" & Target.Row).Locked = False
Range("BA" & Target.Row).Locked = False
Range("BC" & Target.Row).Locked = False
Range("BE" & Target.Row).Locked = False
Range("BG" & Target.Row).Locked = False
Range("BI" & Target.Row).Locked = False
Range("BK" & Target.Row).Locked = False
Range("BP" & Target.Row).Locked = False
Me.Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End If
End Sub

But I got a compile error " Block if without End if"...
Don´t know what that is, as I said I´m new to VBA

Thanks in advance for your help
 
Upvote 0
In your second block, is this supposed to be a Nested IF statement?
Code: 
[COLOR=#0000ff]If [/COLOR][COLOR=#333333]Target.Column = 3 [/COLOR][COLOR=#0000ff]Then[/COLOR][COLOR=#333333] [/COLOR][COLOR=#008000]'3=column C[/COLOR]
[COLOR=#0000ff]If[/COLOR][COLOR=#333333] Target.Value = "" [/COLOR][COLOR=#0000ff]Then[/COLOR]
If so, then you need to "End If" statements at the end, or just replace the code above with this:
Code: 
[COLOR=#0000ff]If ([/COLOR][COLOR=#333333]Target.Column = 3) And ([/COLOR][COLOR=#333333]Target.Value = "") [/COLOR][COLOR=#0000ff]Then[/COLOR]
 
Upvote 0
Joe4 said:
In your second block, is this supposed to be a Nested IF statement?
Code: 
[COLOR=#0000ff]If [/COLOR][COLOR=#333333]Target.Column = 3 [/COLOR][COLOR=#0000ff]Then[/COLOR][COLOR=#008000]'3=column C[/COLOR]
[COLOR=#0000ff]If[/COLOR][COLOR=#333333] Target.Value = "" [/COLOR][COLOR=#0000ff]Then[/COLOR]
If so, then you need to "End If" statements at the end, or just replace the code above with this:
Code: 
[COLOR=#0000ff]If ([/COLOR][COLOR=#333333]Target.Column = 3) And ([/COLOR][COLOR=#333333]Target.Value = "") [/COLOR][COLOR=#0000ff]Then[/COLOR]
Click to expand...

Still no luck...
In the second block, the purpose is to lock the intire row if the cell in column "C" is filled, otherwise the row stays unlocked.
Don´t know if theres a better way to do it

If I replace the line with your code it will lock the row even with the cell in "C" empty (not good), and i also get a "run time error 1004".
 
Upvote 0
Was your second block of code working the way you wanted it to before you tried combining them together?

Maybe you just need to add the second "End If", i.e.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column = 8 Then
        Application.EnableEvents = False
        Cells(Target.Row, 5).Value = Date + Time
        Application.EnableEvents = True
    End If


    If Target.Column = 3 Then '3=column C
        If Target.Value = "" Then
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = False
            'leave these locked:
            Range("D" & Target.Row).Locked = True
            Range("E" & Target.Row).Locked = True
            Range("P" & Target.Row).Locked = True
            Range("AA" & Target.Row).Locked = True
            Range("AD" & Target.Row).Locked = True
            Range("AX" & Target.Row).Locked = True
            Me.Protect Password:="xxx", AllowFiltering:=True
        Else
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = True
            'dont block these:
            Range("X" & Target.Row).Locked = False
            Range("BA" & Target.Row).Locked = False
            Range("BC" & Target.Row).Locked = False
            Range("BE" & Target.Row).Locked = False
            Range("BG" & Target.Row).Locked = False
            Range("BI" & Target.Row).Locked = False
            Range("BK" & Target.Row).Locked = False
            Range("BP" & Target.Row).Locked = False
            Me.Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:=True _
                , AllowFiltering:=True
        End If
    End If
    
End Sub
 
Upvote 0
Joe4 said:
Was your second block of code working the way you wanted it to before you tried combining them together?

Maybe you just need to add the second "End If", i.e.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column = 8 Then
        Application.EnableEvents = False
        Cells(Target.Row, 5).Value = Date + Time
        Application.EnableEvents = True
    End If


    If Target.Column = 3 Then '3=column C
        If Target.Value = "" Then
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = False
            'leave these locked:
            Range("D" & Target.Row).Locked = True
            Range("E" & Target.Row).Locked = True
            Range("P" & Target.Row).Locked = True
            Range("AA" & Target.Row).Locked = True
            Range("AD" & Target.Row).Locked = True
            Range("AX" & Target.Row).Locked = True
            Me.Protect Password:="xxx", AllowFiltering:=True
        Else
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = True
            'dont block these:
            Range("X" & Target.Row).Locked = False
            Range("BA" & Target.Row).Locked = False
            Range("BC" & Target.Row).Locked = False
            Range("BE" & Target.Row).Locked = False
            Range("BG" & Target.Row).Locked = False
            Range("BI" & Target.Row).Locked = False
            Range("BK" & Target.Row).Locked = False
            Range("BP" & Target.Row).Locked = False
            Me.Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:=True _
                , AllowFiltering:=True
        End If
    End If
    
End Sub
Click to expand...

Hi Joe4. Thanks again for your time.
Both blocks work fine on their own.

Tried your sugestion (second "End if"), and although now I don´t get the "block if without end if" error, it doesn´t do anything (neither the timestamp or the line block)...
 
Upvote 0
Joe4 said:
Was your second block of code working the way you wanted it to before you tried combining them together?

Maybe you just need to add the second "End If", i.e.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column = 8 Then
        Application.EnableEvents = False
        Cells(Target.Row, 5).Value = Date + Time
        Application.EnableEvents = True
    End If


    If Target.Column = 3 Then '3=column C
        If Target.Value = "" Then
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = False
            'leave these locked:
            Range("D" & Target.Row).Locked = True
            Range("E" & Target.Row).Locked = True
            Range("P" & Target.Row).Locked = True
            Range("AA" & Target.Row).Locked = True
            Range("AD" & Target.Row).Locked = True
            Range("AX" & Target.Row).Locked = True
            Me.Protect Password:="xxx", AllowFiltering:=True
        Else
            Me.Unprotect Password:="xxx"
            Range(Target.Offset(0, 1), Target.Offset(0, 76)).Locked = True
            'dont block these:
            Range("X" & Target.Row).Locked = False
            Range("BA" & Target.Row).Locked = False
            Range("BC" & Target.Row).Locked = False
            Range("BE" & Target.Row).Locked = False
            Range("BG" & Target.Row).Locked = False
            Range("BI" & Target.Row).Locked = False
            Range("BK" & Target.Row).Locked = False
            Range("BP" & Target.Row).Locked = False
            Me.Protect Password:="xxx", DrawingObjects:=False, Contents:=True, Scenarios:=True _
                , AllowFiltering:=True
        End If
    End If
    
End Sub
Click to expand...

Hi again Joe4
Please Forget my last post, your new suggestion (second "End If") worked great!!!!
Everything up and running now, I can´t thank enough for your help, oh great VBA Master.

Thanks for your time and support!
 
Upvote 0
You're welcome!
Glad to help!:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
182
Peter_SSs
Peter_SSs
R
  • Solved
VBA moving rows
2
Replies
14
Views
869
Joe4
Joe4
K
  • Question Question
VBA with password-protected sheet
Replies
5
Views
629
Logit
Logit
J
  • Solved
Combining VBA Codes
Replies
7
Views
434
Joe4
Joe4
E
  • Question Question
Copy Cell as Hyperlink to another cell
Replies
1
Views
227
eli_m
E

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top