Clear area field if state is changed in dependant drop down

T

TTTT

New member
Joined
Jul 18, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
365
Hi all,
I'm keen to get some help in VBA that will clear the selection in the area column (B) if the state in column (A) is changed...

eg:
State selected: QLD, Area selected is Brisbane, however if someone changes the state to NSW, Brisbane selected in Area column should auto remove (as Brisbane is not an area in NSW), I was able to find one in a thread in Google but it clears all cells in the column instead of that one cell

Any help would be greatly appreciated - Thank you!

View attachment Dependant Drop Down.xlsm
 
Hello TTTT,

Try the following code placed in the worksheet module:-


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Target.Offset(, 1).ClearContents

End Sub

To implement the code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

Each time that a new state name is selected, and you then click away or press enter or down arrow, the contents of the adjacent cell in Column B will be cleared.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
 
Hi vcoolio!
That is awesome thank you! the other coding was similar I just couldnt figure out how to adjust it thank you so much it works amazingly!
 
You're welcome TTTT.
I'm glad that I was able to help.

Cheerio,
vcoolio.
 
You must log in or register to reply here.
Back
Top