Combining two subs in one sheet - VBA

[Paula03]

Hello everybody,

I'm new here and also fairly new to Excel VBA.

My problem is that I have two subs 'combined' in one sheet, but only one works.

The first part of the code enables me to select more than one worker in column G from a drop down list and is working just fine atm.

The goal of the second part of code, is that if a cell in column J becomes TRUE (WAHR) that the cell in column G is cleared.
But I just can't seem to make this bit work.

I have found a lot of similar threads across different forums, but none of them had an answer to my specific problem.
So any help is appreciated.


The part of the table that the code applies to:

The code: (pls ignore my comments in green. they're in German and just for my orientation)

 

[Joe4]

Welcome to the Board!

Typically, your error handling happen at the very bottom, before the "End Sub" line. So I think you may want to flip your second block of code and your error handling.

But that may not actually be your problem. The important question is, how exactly is column J being updated?
If it is a formula, it will not work. "Worksheet_Change" event procedures only work when cells are manually updated.
It does not fire when the value changes due to a formula!

 

[Paula03]

Welcome to the Board!

Typically, your error handling happen at the very bottom, before the "End Sub" line. So I think you may want to flip your second block of code and your error handling.

But that may not actually be your problem. The important question is, how exactly is column J being updated?
If it is a formula, it will not work. "Worksheet_Change" event procedures only work when cells are manually updated.
It does not fire when the value changes due to a formula!

Hello Joe4,

indeed column J is filled automatically if you enter a time in column I.

I just tried it without the formula and filled J manually and the code worked like a charm.

You just solved weeks of headaches and trial and error with two sentences.

Thanks you so much! :D

 

[Joe4]

You are welcome. Glad I was able to help!

Note: Depending on what the formula is in column I, you want be able to keep formulas in column J.
If an update to column I updates the value in the SAME row of column J, and column I is being manually entered into, just change your code to watch for changes to column I instead of column J.