VBA code to have dependant cell on a different row to the input cell

[Ancien1]

Hi,
I have a worksheet where a validation list choice in say sell A1 changes the validation list choices in B1 and if the validation list choice in A1 is changed the content of cell B1 is cleared allowing a different list of choices to be used.

The VBA code for this is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

However I want the dependant cell to be on a different row, say B2 instead of B1. Is this possible and of so how?

 

[Snakehips]

@Ancien1 Welcome.
With 'If Target.Column = 2' your above code is wanting to ensure that the triggering change is in column B

Since you mention A1, if you want it to be in column A then you must change to check that Target.Column = 1
If you want only to trigger when eg. B1 changes then you may wish to have a more specific test if the second test of Validation type is not sufficient to identify B1 ?

If Target.Address = "$B$1" Then

Currently, the 'Target.Offset(0,1).ClearContents is clearing row offset 0 (same row, ) and column offset 1 (C)
To get cell below rather than cell to the right you need ro offset 1 and column offset 0.

So code for Target B1 and dependant cell B2 would be

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(1, 0).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Hope that helps

 

[Ancien1]

Sorry, I'll try to be clearer. I have 2 tables/lists. One table has the choices ON or OFF. The 2nd has a long list of choices. As the code currently works, if I choose OFF in A1 there are no choices available in B1 (correct). If I choose ON in A1 I can choose from the long list of choices (correct). IF I then go back to A1 and change the choice to OFF then B1 is automatically cleared i.e. the contents disappear without user intervention (correct). What I would to achieve is to use cell B3 instead B1.

 

[Snakehips]

Forgive me but I'm still struggling to see how your current code works correctly for the A1 B1 scenario because A1 is your Target cell and the code does nothing if Target column is not 2 ?
However, if it is, then the offset for B3 from A1 is (2,1) rather than (0,1) if you wish to try it.

If I was trying to illustrate what I understand you are describing then it wouls be something like below.

Book1
	A	B	C	D	E	F	G
1	On					one
2						Two
3		Three				Three
4						Four
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1	List	On,Off
B3	List	=IF($A$1="On",$F$1:$F$4,$G$1)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Target.Address = "$A$1" Then Exit Sub   'Only proceed if Target is A1
If Target = "Off" Then Exit Sub  'Only proceed if Target has changed to 'On'
Application.EnableEvents = False
'Clear B3 if A1 has changed to 'On'
Target.Offset(2, 1).ClearContents

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Apologies if I am just not understanding.

 

[Ancien1]

Thanks you have been very helpful.
Apologies, I did state things incorrectly that my first piece of code was working for A and B
But I have got it working using you earlier piece of code and am using it using I8 and I10.
I now need to show you what I am looking to achieve using this code hopefully with nested statements within it.

EQ using I8 and I10 but still with complete MIDI list

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 0).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

The attached jpg show the worksheet area.

What I am trying to achieve is for I8, Q8, V8, I15 and Q15 to all use the same ONOFF and for
I10, K10, M10 and O10 to be driven by I8,
Q10 and T10 to be driven by Q8,
V10, X10 and Z10 to be driven by V8,
I17, K17, M17 and O17 to be driven by I15 and
Q17, T17, V17, X17 and Z17 to be driven by Q15.

phew!

Is this possible?

Sorry I can't get xl2bb to work

 

[Ancien1]

i've tried using a common ONOFF and that didn't work so I have tried using an ONOFF for each section and that doesn't work either.
This is part of my code for ONOFF for each EQ section has EQ type at I8 as ONOFF and LFGAIN choice is at I10, LFREQ choice is at K10, HFGAIN choice is at M10 and HFFREQ choice is at O10.
.
Private Sub Choose_EQ_LF_Gain(ByVal Target As Range)
On Error Resume Next
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 0).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Private Sub Choose_EQ_LF_Freq(ByVal Target As Range)
On Error Resume Next
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 2).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

Private Sub Choose_EQ_HF_Gain(ByVal Target As Range)
On Error Resume Next
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 4).ClearContents
End If
End If

exitHandler:

Application.EnableEvents = True
Exit Sub
End Sub
Private Sub Choose_EQ_HF_Freq(ByVal Target As Range)
On Error Resume Next
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 6).ClearContents
End If
End If

exitHandler:

Application.EnableEvents = True
Exit Sub
End Sub
Private Sub Choose_COMP_Threshold(ByVal Target As Range)
On Error Resume Next
If Target.Column = 17 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(2, 0).ClearContents
End If
End If

 

[Snakehips]

Good Morning. Good timing
I was just struggling to start trying to ask questions that might clarify EXACTLY what you want. Do remember, that what is totally obvious to you is not always to an outsider.
Initially, let me re-read the above and see if it clarifies it any.
Your previous use of the change event etc has perhaps been misleading. Or maybe, I'm just a bit thick!

I have no doubt that we will get it sorted.

 

[Snakehips]

Would you prefer a single On / Off if possible ?

 

[Ancien1]

No, each section - EQ, COMP, REVERB, FX 1 and FX 2 needs its own ONOFF. In real life, if say EQ is turned off then LFGAIN, LFREQ, HFGAIN and HFFREQ completely disappear but that isn't necessary for this worksheet,

 

[Snakehips]

So, in simple terms then, if entry is made in I8 and it is 'Off' then, you want I10, K10, M10, O10 to be cleared?
Similarly, with other sections eg Q15, entry of 'Off' to trigger clearance of Q17, T17, V17, X17, Z17 ?