VBA, Multiple Worksheet changes

[Flyingduck]

Hello all,

I've been working on the vba code for a work project.
I've done this multiple times before for similar projects but this one is slightly different.

Previously there was one group of entries, with 4 specific variables that had to be entered in order for the program to be ran.
This has worked just fine for me, but now I have two group of entries with 4 variables each.
If all 4 variables are entered for either group I need the program to run without making a change to the other group of variables if at all possible.
Is there anyway I can achieve this?
I pasted the code I have below, at this point in time it's not working.
I'm wondering if there is any sort of method of getting it to do what I need?

Best regards,

FlyingDuck

Sub WorkSheet_Change(ByVal Target As Range)


Dim rng As Range
Dim r As Range


Dim bcellsfilled As Boolean




If Sheets("T Generator").Range("C10").Value <> 0 And Sheets("T Generator").Range("C11").Value <> 0 And Sheets("T Generator").Range("C12").Value <> 0 And Sheets("T Generator").Range("C13").Value <> ""Then
Set rng = Sheets("T Generator").Range("C10, C11, C12, C13")
End If


'If Sheets("T Generator").Range("C61").Value <> 0 And Sheets("T Generator").Range("C62").Value <> 0 And Sheets("T Generator").Range("C63").Value <> 0 And Sheets("T Generator").Range("C64").Value <> 0 Then
'Set rng = Sheets("T Generator").Range("C61, C62, C63, C64")
'End If


Application.ScreenUpdating = False


If Not Intersect(rng, Target) Is Nothing Then


bcellsfilled = True


For Each r In rng


If r.Value = "" Then
bcellsfilled = False
End If
Next r


If bcellsfilled = True Then
Call Macro1
Sheets("T Generator").Range("c13").ClearContents
End If


'If bcellsfilled = True And rng = Sheets("T Generator").Range("C61, C62, C63, C64") Then
'Call Macro2
'Sheets("T Generator").Range("C64").ClearContents
'End If

If Sheets("T Generator").Range("C10").Value <> "" And Sheets("T Generator").Range("C11").Value <> "" And Sheets("T Generator").Range("C12").Value <> "" And rng = Sheets("T Generator").Range("C10, C11, C12, C13") Then
Call Plasticdata
End If



'If Sheets("T Generator").Range("C61").Value <> "" And Sheets("T Generator").Range("C62").Value <> "" And Sheets("T Generator").Range("C63").Value <> "" And rng = Sheets("T Generator").Range("C61, C62, C63, C64") Then
'Call Plasticdata2
'End If






End If


End Sub

 

[StephenCrump]

Welcome to the Forum!

I'm not sure what you mean by this:

... now I have two group of entries with 4 variables each.
If all 4 variables are entered for either group I need the program to run without making a change to the other group of variables if at all possible.

Are you perhaps referring to your code clearing C13 and C64?

The gist of your code seems to be that you want something (Macro1? Macro2? Plasticdata?) to run only if all cells in the two ranges Sheets("T Generator").Range("C10:C13") and .Range("C61:C64")are non-zero and non-blank? Is that correct?

Are you sure you want the code triggered by a Worksheet_Change event? If the user has a set of non-zero input cells and wants to enter a new set, your code will trigger every time the user changes any one of these values, i.e. rather than relying on the user telling you when the set is complete?

 

[Flyingduck]

Hi Stephen, thank you for the war welcome!
My apologies, I'm sure I could have made that clearer.

Nope, so that code was there so that if the program had already ran it would clear a cell from that group so it didn't not run it again, unfortunately though this logic didn't work for what I had intended.
Your mostly correct, So we have two sets of inputs C10:C13 and C61:C64 that are separate but have identical drop downs for one another.
So if C10:C13 are all non-zero and non-blank then the program runs macro1 which filters a drop down list.
Once this drop down list is filtered Plasticdata runs which goes through the list and copies any non-hidden lines of data and pastes them into a separate sheet.
This data then appears in a drop-down that the user can select from.
That's just for macro1 though, we need macro2 to do the exact same thing, without triggering macro 1 or visa-versa, once the inputs in C61:C64 are all entered or chosen.
To sum up, we need macro1 and macro2 to run based off separate groups of data, without running on top of one another.

Unfortunately yes, I told my boss that we could use a button to solve this problem, but she wants as little operator interface as possible(the group using this doesn't know much about excel outside of basic entry).
This was the method decided upon as it seemed to have the least amount of operator input. Is there another method you know of that would work besides a Worksheet_Change event, or a button?

Thank you so much for your help!

FlyingDuck

 

[StephenCrump]

Perhaps something along these lines:

Sub WorkSheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Set rng = Worksheets("T Generator").Range("C10:C13")
    If Application.CountBlank(rng) = 0 And Not Intersect(Target, rng) Is Nothing Then
        'Do stuff with C10:C13
        '...
    Else
        Set rng = Worksheets("T Generator").Range("C61:C64")
        If Application.CountBlank(rng) = 0 And Not Intersect(Target, rng) Is Nothing Then
            'Do stuff with C61:C64
            '...
        End If
    End If

End Sub

Are you sure you want the code triggered by a Worksheet_Change event? If the user has a set of non-zero input cells and wants to enter a new set, your code will trigger every time the user changes any one of these values, i.e. rather than relying on the user telling you when the set is complete?

It sounds like this won't be a problem ... if all you are doing is copying filtered lines to create a data validation list, there is no great harm in doing this every time the user changes a cell and the range is fully populated. Presumably the user won't use the data validation list until they are happy with all four input cells.

If you decide to make changes to the input ranges, e.g. setting back to blank, you'll need to wrap the code as follows so that this doesn't trigger another iteration of Worksheet_Change

Application.EnableEvents = False
'Make changes to worksheet
Application.EnableEvents = True

But I suggest that having cells automatically going blank might confuse or annoy the user?