Results 1 to 4 of 4

Thread: Clear area field if state is changed in dependant drop down

  1. #1
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365

    Clear area field if state is changed in dependant drop down



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

    Dependant Drop Down.xlsm

  2. #2
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    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.

  3. #3
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    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!

  4. #4
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    You're welcome TTTT.
    I'm glad that I was able to help.

    Cheerio,
    vcoolio.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •