Two Worksheet changes help please

[stewart1]

Good evening or morning to all.

Well at the moment I have about ten tabs on Firefox open and a raging headache!

I have just found out (shows how green I am) that I cannot have "two" worksheet_changes going on.

I have put "end ifs" and Lord knows what in to try and run these two events, but at best one works and the other doesn't or I get errors "End If without "Block If" and it's all going a bit wrong, and my wife keeps looking at me like I'm a bit weird because I am talking to myself saying "please work".

So, I have these two bits of code first one here locks the cell on the chosen columns after the user presses "yes" (columns L,M and N have a popcal called, user enters a date and the choice "yes" locks it).

Private Sub Worksheet_Change(ByVal  Target As Range)Dim Ans As VariantIf Application.Intersect(Target,  Range("L2:L10000, M2:M10000, N2:N10000")) Is Nothing Then Exit Sub
    Ans = MsgBox("Are you sure you want to enter " & Target.Value, vbQuestion + vbYesNo, "Confirm Entry")
    If Ans = vbYes Then Target.Locked = True
    
End Sub

This is great but I needed this code below to work as well:

Private Sub Worksheet_Change(ByVal Target As Range)Dim Rng As Range
    If Target.Count > 1 Then Exit Sub
Set Rng = Range("D1:D10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 4) = "" Then Target.Offset(, 4) = Date
    ActiveSheet.Protect ""

This was some code I found which allowed the date to be entered and the date cell locked after data was put into column D.

So can I get these to work alongside each other and if so what joins them please. Can I use this

Private Sub Worksheet_Change(ByVal Target  As Range)Dim Rng As Range
    If Target.Count > 1 Then Exit Sub
Set Rng = Range("D1:D10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 4) = "" Then Target.Offset(, 4) = Date
    ActiveSheet.Protect ""

a second time within the worksheet independantly of that one so I could have code for example entry in column B trigger date entry in column A

If it does make a difference I do have a workbook code for locking those columns but I would have thought that it wouldn't interfere with any others.

If anyone can help tell me what the "link" is I would be grateful.

i hope I have explained myself OK.

Thanks for looking.

Stewart

 

[Zack Barresse]

Hi there,

Not sure because I haven't tested it (not sure of the data structure) but maybe something like this...


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Target, Range("L2:L10000, M2:M10000, N2:N10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Ans = MsgBox("Are you sure you want to enter " & Target.value, vbQuestion + vbYesNo, "Confirm Entry")<br>        <SPAN style="color:#00007F">If</SPAN> Ans = vbYes <SPAN style="color:#00007F">Then</SPAN> Target.Locked = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">ElseIf</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("D1:D10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        ActiveSheet.Unprotect ""<br>        <SPAN style="color:#00007F">If</SPAN> Target.Offset(, 4) = "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 4) = VBA.Date<br>        ActiveSheet.Protect ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


The difficult part to discern out of your post is the exact conditions in which you want these to fire. If you wanted them both to work every time, you'd need separate If/Then statements, which would look like this ...


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Target, Range("L2:L10000, M2:M10000, N2:N10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Ans = MsgBox("Are you sure you want to enter " & Target.value, vbQuestion + vbYesNo, "Confirm Entry")<br>        <SPAN style="color:#00007F">If</SPAN> Ans = vbYes <SPAN style="color:#00007F">Then</SPAN> Target.Locked = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("D1:D10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        ActiveSheet.Unprotect ""<br>        <SPAN style="color:#00007F">If</SPAN> Target.Offset(, 4) = "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 4) = VBA.Date<br>        ActiveSheet.Protect ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


It's only slightly different. The first one will look at the first If statement, if it doesn't match it will look at the second one. But, if it looks at the first If and it does match, it will not look at the second ElseIf portion (hence two If/Then statements).

HTH

 

[stewart1]

Hi Zack,

Thanks for your reply,

My original columns still lock Ok, but the offset date from column D has no effect.

Would I need to provide futher info?

Thanks again though,


Stewart

 

[Zack Barresse]

Is that with only one cell selected? Because you had the code looking for more than one cell being selected. If you want to do it ONLY if one cell is selected, then change this line...

If Target.Count > 1 Then

... to this ...

If Target.Count = 1 Then

HTH

 

[stewart1]

Hi Zack,

Thanks for your help with this, I work shifts hence the delayed reply.

I tried the code but it still wouldn't fire up the date offset.

I am need to change my tactic with this but I still need to have more than one selection change.

As I wrote in my original post (albeit a bit garbled- sorry!) I have the offset column that enters a date and locks the cell when data is put into column "D"

I need to bin the popcal as it allows the user to put in any date.

Is there any way of keeping that code but modifying it so for example;

Private Sub Worksheet_Change(ByVal Target  As Range)
Dim Rng As Range
    If Target.Count > 1 Then Exit Sub
Set Rng = Range("D1:D10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 4) = "" Then Target.Offset(, 4) = Date
    ActiveSheet.Protect ""</pre>

would work independantly of this:

Private Sub Worksheet_Change(ByVal Target  As Range)
Dim Rng As Range
    If Target.Count > 1 Then Exit Sub
Set Rng = Range("K1:K10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
    ActiveSheet.Protect ""</pre>

I have tried using "End If" and "Then" to join them but all I get is a row of dates from my start column to the finish.

My last issue would be to also auto enter a date and lock a cell, say for example, column "M" range M1:M10000 when a user simply clicks onto that cell within the given range. This is because there simply isn't any data input to trigger an offset column.

Thanks for looking and all your help thus far,

Cheers,


Stewart

 

[stewart1]

I just thought, if it helps, would it be easier to upload my workbook?

Thanks,


Stewart

 

[stewart1]

Right!

I have had a go at trying to get this to run so I can have the two choices as previously mentioned but I am still getting the "End If without block If" error.

My code is below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
    If Target.Count > 1 Then Exit Sub
Set Rng = Range("D1:D10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 4) = "" Then Target.Offset(, 4) = Date
    ActiveSheet.Protect ""
    End If
   If Target.Count > 1 Then Exit Sub
Set Rng = Range("K1:K10000")
' Only look at that range
If Intersect(Target, Rng) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect ""
    If Target.Offset(, 1) = "" Then Target.Offset(, 1) = Date
    ActiveSheet.Protect ""
    End If
End Sub

Now if I can get this to run I will be laughing.

Zack, I did try and follow your reply and I tried to adapt it to suit.

As before any help appreciated.

Thanks,


Stewart

 

[Zack Barresse]

You've got 2 too many 'End If' statements. Remember, if you take care of an If/Then statement, you don't need a closing 'End If' statement.

Now for your code, there are some problems you'll run into. Like getting to your second portion. You're checking if the Target (cell being changed) is in the range of "D1:D10000", and if it's not you're exiting the routine. This basically means that if your cell being changed is in K1:K10000 then the second portion will never run. So you'll need to use an If/Then or an If/ElseIf statement to look at all options. Plus you're doubling up on the "If Target.Count > 1 Then Exit Sub", and if you have it once there is no need to have it again. Good job on combining them, Stewart, it is very close to what would work. Here is my recommendation...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> sWSPWD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = ""<br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Me.Range("D1:D10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Me.Unprotect sWSPWD<br>        <SPAN style="color:#00007F">If</SPAN> Target.Offset(, 4) = "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 4) = <SPAN style="color:#00007F">Date</SPAN><br>        Me.Protect sWSPWD<br>    <SPAN style="color:#00007F">ElseIf</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Me.Range("K1:K10000")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.Unprotect sWSPWD<br>        <SPAN style="color:#00007F">If</SPAN> Target.Offset(, 1) = "" <SPAN style="color:#00007F">Then</SPAN> Target.Offset(, 1) = <SPAN style="color:#00007F">Date</SPAN><br>        ActiveSheet.Protect sWSPWD<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Let us know how it works.

 

[stewart1]

Zack, I got it!

With your advice I have managed to put the two together and incorporate my date entry on a single cell click, which locks as well.

For that, I employed the same "ElseIf" way you told me to do. (within a Worksheet_SelectionChange)

Honestly, I have now really understood and learnt something! I can string together multiple changes!! I don't know if that's the correct terminology but what the heck!

Thank you for simplifying it and your help.

All the best!


Stewart