Conditional drop down list using vba

[Wishmaster89]

Hi Everyone

I have a conditional drop down list where a user can choose a course and a pathway which works fine.
The problem is one of the courses (IT) has an additional drop down list (column c), the problem is when the course in the first column is changed ( changed to Maths for example) column c is not clearing.

This is the VBA code i have that clears column B if column A changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A4:B18")) Is Nothing Then Target.Offset(columnoffset:=1).ClearContents
Application.EnableEvents = True
End Sub

drop down list offset example.xlsm
	A	B	C
2	Course	Pathway	If course is IT choose additional pathway
3			Pathway Selection
4	IT	Access database	VBA
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Course selection

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:C18	Expression	=ISBLANK(XEL4)	text	NO
A4:C18	Expression	=ISBLANK(A4)	text	NO
A4:C18	Expression	=ISBLANK(A4)	text	NO
A4:C4	Expression	=ISBLANK(A4)	text	NO
A4:C4	Expression	=ISBLANK	text	NO
A4:A18	Expression	=ISBLANK(XEJ4)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A4:A18	List	=CourseList!$B$1:$E$1
B4:B18	List	=OFFSET(CourseList!$B$1,1,MATCH(A4,CourseList!$B$1:$E$1,0)-1,5,)
C4	List	=IF(C4="",OFFSET(CourseList!$M$1,1,MATCH(A4,CourseList!$M$1:$M$1,0)-1,5,),INDIRECT("FakeRange"))
C5:C18	List	=IF(A5<>"IT","",OFFSET(CourseList!$M$1,1,MATCH(A5,CourseList!$M$1:$M$1,0)-1,5,))

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  If Not Intersect(Target, Range("A4:B18")) Is Nothing Then
    With Target.Offset(0, 1)
      If Target.Column = 1 Then .Resize(1, 2).ClearContents Else .ClearContents
    End With
  End If
  Application.EnableEvents = True
End Sub

 

[Wishmaster89]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  If Not Intersect(Target, Range("A4:B18")) Is Nothing Then
    With Target.Offset(0, 1)
      If Target.Column = 1 Then .Resize(1, 2).ClearContents Else .ClearContents
    End With
  End If
  Application.EnableEvents = True
End Sub

Thank you that does work but i forgot to add something in.

 

[Wishmaster89]

I am trying to adapt the code given above to this spreadsheet but for some reason if a value in column N is changed the value in column P does not delete.
(Note, only "Insurance Practitioner Level 3" in col O and any of the options in O will trigger a value needed in P.

offset example 2 for forum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5						Employer:
6						Planned Start Month:
7	Apprentice Details:								Employer Details:					Programme Details:
8	Apprentice Forename	Apprentice Surname	Start Date in Role (mm/yyyy)	Contract Type	Work Email	Contact Number	Apprentice works 30 hours +	Annual Leave Dates (in the next month)	Line Manager Name	Line Manager Email	Line Manager Contact Number	Employer office	Digital Account Cohort Reference	Apprenticeship Standard & Level	Professional Qualification	If Insurance Practitioner Level 3:	First Workbook/Material to Order (if applicable)	Further unit selection agreed? (Yes/No)	If yes, please state here
9																Pathway Selection
10														Financial Services Administrator Level 3	CII: RO1
11														Insurance Practitioner Level 3	Cert CILA: Certificate in Claims Handling	Claims Handler / Loss Adjuster
12														Insurance Practitioner Level 3	Cert CII	Assistant Underwriter
13														Insurance Practitioner Level 3	Cert CILA: Certificate in Claims Handling	Claims Handler / Loss Adjuster
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Apprentice(s) Details

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O10:P29	Expression	=ISBLANK(O10) = FALSE	text	NO
O10:P29	Expression	=ISBLANK(XFA10)	text	NO
O10:P29	Expression	=ISBLANK(XFA10)	text	NO
O10:P29	Expression	=ISBLANK(XFA9)	text	NO
N10:N29	Expression	=ISBLANK(N10) = FALSE	text	NO
N10:N29	Expression	=ISBLANK(XEZ10)	text	NO
N11	Expression	=ISBLANK(XEZ11)	text	NO
A10:B29	Expression	=ISBLANK(A10)	text	NO
I10:M11,I12:N29	Expression	=ISBLANK(XEU10)	text	NO
A10:G29	Expression	=ISBLANK(A10)	text	NO
A29:G29	Expression	=ISBLANK(A29)	text	NO
A29:G29	Expression	=ISBLANK(A29)	text	NO
A25:G28	Expression	=ISBLANK(A25)	text	NO
A25:G28	Expression	=ISBLANK(A25)	text	NO
A24:G24	Expression	=ISBLANK(A24)	text	NO
A24:G24	Expression	=ISBLANK(A24)	text	NO
T10:XFD29,A10:G23,I10:Q29	Expression	=ISBLANK(A10)	text	NO
T10:XFD29,A10:G23,I10:Q29	Expression	=ISBLANK(A10)	text	NO
T10:XFD10,A10:G10,I10:M10,N11,O10:Q10,O10:P29	Expression	=ISBLANK(A10)	text	NO
T10:XFD10,A10:G10,I10:M10,N11,O10:Q10,O10:P29	Expression	=ISBLANK	text	NO
N11	Expression	=ISBLANK(XEZ10)	text	NO
Q10:Q29	Expression	=ISBLANK(A10)	text	NO
O10:P29	Expression	=ISBLANK(XFB10)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D10:D29	List	=#REF!$A$2:$A$5
G10:G29	List	=#REF!$I$2:$I$3
N10:N29	List	=Lists2!$B$1:$X$1
O10:O29	List	=OFFSET(Lists2!$A$1,1,MATCH(N10,Lists2!$A$1:$W$1,0)-1,4,)
P10:P29	List	=OFFSET(Lists2!$AB$1,1,MATCH(O10,Lists2!$AB$1:$AD$1,0)-1,2,)

 

[Wishmaster89]

*bump*