Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

**jankrulak** · 08-09-2018, 07:59 AM

Hi All,

I am trying to run multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one and it gives me an error.

Could anyone please help me what I did wrong? Please see a code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Column <> 4 Or Target.Row < 3 Then Exit Sub

Cells(Target.row,5).Value = Now
End Sub
If Target.Column = 6 Then

Dim lastRow As Long
lastRow = Cells(Rows.Count, 6).End(xlUp).Row
Range("A3:F" & lastRow).Sort key1:=Range("F3:F42" & lastRow), order1:=xlAscending, Header:=xlNo

End If

End Sub

Thank you in advance!

Best regards,

Jan

**pipoliveira** · 08-09-2018, 08:04 AM

Hi Jan,

You have 2 "End Sub" on your code for what I can see. You should have only one.

Change this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Or simply remove that "End Sub" as it terminates your code right there.

Let me know if that helped.

BR,
Filipe

**jankrulak** · 08-09-2018, 08:50 AM

Hi Filipe,

Thank you for your response.

Your advice solves the error message but the second part of the code does not do any effect to the excel. The second code is to change order by changing priority. I am not able to attach this file here due its size, so if you want to have a look please follow the link: dropbox.com/s/359hbe8btrhwame/Work%20List.xlsm?dl=0

Kind regards,

John

**Vraag en antwoord** · 08-09-2018, 09:32 AM

Remove the unnecessary row 2. The formula in column B contains a circular reference

Please Login or Register  to view this content.

**pipoliveira** · 08-09-2018, 09:41 AM

Hey John,

I've twisted a bit your code but it's working from my end.

It's a longer code but it's working when you change cells on row D.

Here's the code and let me know if that works:

Please Login or Register  to view this content.

Cheers,
Filipe

**jankrulak** · 08-09-2018, 10:11 AM

Hi Filipe,

Thank you for the updated code. The code works but there must be still some bug as it doesn't work properly. The priority does not change by changing priority 'F column', but by changing status 'D column'. I attached a screenshots for better explanation.

I tried to solve the issue in your code, but cannot find the bug. Any idea?

Many thanks for your help, much appreciated.

Best regards,

John

**pipoliveira** · 08-10-2018, 04:49 AM

Hi John,

Sorry for the late reply.

I do believe the issue is within the first line of code:

Please Login or Register  to view this content.

I do believe changing it would solve the issue but, on the other hand, I tried to run the rest of the code without it and I got an error and the Excel simply crashed.

I'll leave it for some "Excel Gurus" here to assist you on this part.

Really sorry for not giving you a solution on this matter.

Best regards,
Filipe

**Vraag en antwoord** · 08-10-2018, 06:19 AM

You missed #4?

**pipoliveira** · 08-10-2018, 06:23 AM

Sorry John,

Just one other thing.

This file is huge in size but for what I see, there is no explanation for it as it only contains 2 sheets with no major data on it.

I would recommend you to create another one from scratch and copy-paste the codes you have.

This file shouldn't have such a size of 15.6MB.

Check this old thread as it might help:
https://www.excelforum.com/excel-pro...d-is-huge.html

Best regards,
Filipe

**pipoliveira** · 08-10-2018, 07:33 AM

Hi Vraag,

I have added the code you've posted on #4 but at least on my side is not working or not doing what John is requesting.

Column "F" is not sorting automatically.

I've managed to clean file from 15.6MB size to 2.67MB but for some reason, it still does not allow me to attach it to this thread.

Best regards,
Filipe

**Vraag en antwoord** · 08-10-2018, 07:58 AM

Works fine here.Work List.xlsb

**pipoliveira** · 08-10-2018, 09:29 AM

Hi Vraag,

It is working indeed with the file you've attached.

Probably this will solve John's query for this thread.

Thanks a million,
Filipe

**jankrulak** · 08-10-2018, 04:07 PM

Dear Fillipe and Vraag,

Sorry for late response.

Thank you very, very much for for your help, much appreciated. Excel works exactly as I needed to.

Thanks again!

Best regards,

John

**pipoliveira** · 08-10-2018, 04:58 PM

Originally Posted by jankrulak

Dear Fillipe and Vraag,

Sorry for late response.

Thank you very, very much for for your help, much appreciated. Excel works exactly as I needed to.

Thanks again!

Best regards,

John

Glad to help but the credit has to go to Vraag as he/she did not only solved the issue but as well gave you a very small file in size.

He/she’s code dis not worked on your file because for some reason that you can only tell, that your file had empty row and column after the header.

Best of luck and, pnce again, gobe credit to Vaag by adding reputation by clicking the “*” sign.

Best of luck,
Filipe

**jankrulak** · 08-11-2018, 02:53 AM

Hi,

Sorry I am bothering you again.

Would you mind help me with the time stamp for the column 'B' base on changes with column 'A' in the macro (the same as for column 'E'if possible)? Current code is way advanced for me. Currently I have a formula in the column 'B', but I would like to rid of it to avoid accidentally deleting of the formula as I will be sharing this project list with my colleagues.

Thank you in advance and look forward to hearing from you.

Kind regards,

John

**Vraag en antwoord** · 08-11-2018, 07:05 AM

Please Login or Register  to view this content.

Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

LinkBack

Thread Tools

Rate This Thread

Display

Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Re: Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Private Sub Worksheet_Change(ByVal Target As Range)

[SOLVED] Combining multiple Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)

[SOLVED] Private Sub Worksheet_Change(ByVal Target As Range) End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Help

[SOLVED] Private Sub Worksheet_Change(ByVal Target As Range)

[SOLVED] Private Sub Worksheet_Change(ByVal Target As Range)

Bookmarks

Bookmarks

Posting Permissions