none
VBA Excel - How to run code based on choosing a specific value from a dropdown RRS feed

  • Question

  • Hey guys, 

    As the title suggests, I'm trying to activate code based on a certain selection being chosen from a dropdown.  So if I select what ends up being Cell1 do X, if Cell2 do Y; etc.  I'm just not sure about the syntax.  

    Thanks!

    Thursday, December 20, 2012 10:08 AM

All replies


  • I have assumed that your dropdown is on cell D4 - change it at the top of the code to the actual cell with the dropdown.  And it is unclear if you are selecting Cell1 and Cell2 or if they are the values you are selecting from the dropdown - I have assumed values as strings in a dropdown - change those in the code too.

    Copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. 

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    Set R = Range("D4")

    If Target.Address <> R.Address Then Exit Sub
    Application.EnableEvents = False
    On Error Resume Next
    If Target.Value = "Cell1" Then
    'code to do X
    End If
    If Target.Value = "Cell2" Then
    'code to do Y
    End If
    Application.EnableEvents = True

    End Sub

    Thursday, December 20, 2012 4:42 PM
  • I realize this post is from 2012, but I think it's what I'm looking for so I'm hoping that someone can assist me here.  I understand the basics of Bernie's code.  But I think I struggle with the areas around:  Sub, PrivateSub, module vs not module, etc. - i.e. where to put code, why it goes where it goes, what Sub and some of that language means.

    So in my Excel file I have the following:

    Cell D2 is named "SearchOption" - this is a dropdown list with 2 choices:  Contract & CCAN (i.e. user choices)

    Cell D3 is named "Contract"

    Cell D4 is named "CCAN"

    What I'm trying to do is to clear the cell contents of CCAN (cell D4) IF the user selects "Contract" from SearchOption (cell D2).  And vice versa, I want to clear the cell contents of Contract (D3) IF the user selects "CCAN" from SearchOption (cell D2).  I also want to clear both Contract (cell D3) AND CCAN (cell D4) if the user blanks out SearchOption (cell D2) - meaning if they delete the value in cell D2, then I want both of the values in cells D3 and D4 to clear out as well (that is if there's something in those cells).

    Below is what I have so far and as you can guess it isn't working:(.  My code is between the **** rows.

    Help please!

    *******************************************

    Sub ClearCellContents()
    '
    ' Keyboard Shortcut: Ctrl+q
    '
      
    Private Sub Worksheet_Change(ByVal Target As Range)
     Dim R As Range
     Set R = Range("SearchOption")

    If Target.Address <> R.Address Then Exit Sub
     Application.EnableEvents = False
     On Error Resume Next
     
     If Target.Value = "Contract" Then
        Range("CCAN").Select
        Selection.ClearContents
        Range("SearchOption").Select
     End If
     
     If Target.Value = "CCAN" Then
        Range("Contract").Select
        Selection.ClearContents
        Range("SearchOption").Select
     End If
     
     Application.EnableEvents = True

    End Sub

    *******************************************

    Wednesday, October 2, 2013 8:16 PM
  • Copy the code below, right-click the sheet tab of the worksheet with the named ranges, select "View Code" and paste the code into the window that appears. This will place the event code into the proper codemodule, which belongs to that sheet.

    Bernie

    Private Sub Worksheet_Change(ByVal Target As Range)
     Dim R As Range
     Set R = Range("SearchOption")

    If Target.Address <> R.Address Then Exit Sub
     Application.EnableEvents = False
     On Error Resume Next
     
     If Target.Value = "Contract" Then
        Range("CCAN").ClearContents
        Range("SearchOption").Select
     End If
     
     If Target.Value = "CCAN" Then
        Range("Contract").ClearContents
        Range("SearchOption").Select
     End If
     
     If Target.Value = "" Then
        Range("Contract").ClearContents
        Range("CCAN").ClearContents
        Range("SearchOption").Select
     End If

     Application.EnableEvents = True

    End Sub

    Wednesday, October 2, 2013 11:10 PM