Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Execute Module on Drop down selection

I have a ActiveX Control Dropdown box.
I also have a module named "filtercode" that takes the value selected in that box (linked to another cell within the same sheet) that filters a dataset by a certain field, based on the value i select.

How or with what code do i need to execute this 'filtercode' upon selecting a selection from the drop down menu? So for example, if I select "John" from the drow down, i want the filtercode module to automatically run.
0
ctownsen80
Asked:
ctownsen80
  • 2
  • 2
1 Solution
 
SmittyProCommented:
You can use the ComboBox's Change event to call the code.  Or if you want to use Data Validation instead of a control you can use a Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("xxx")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            
End Sub

Open in new window


HTH

0
 
ctownsen80Author Commented:
Thahnks for your response @ SmittyPro.


I need a little more direction. The cell that will change is cell "BQ2". Upon change in cell BQ2, i want to execute module "filtercode". Can you adjust code you provided to how that would be embedded?
0
 
dlmilleCommented:
There are a couple other alternatives:

1 - create a dropdown control, with cell link - use an index from that link to determine what was selected, and link that dropdown to a macro
2 - create an active-x control, and write code behind that control when items are selected.

Both are in the attached spreadsheet.  Please advise if further assistance is needed.

Give it a try!  when you make a selection from either example, the hello subroutine I wrote will advise you with a messagebox what you selected - that's where you can take action based on what was selected

Dave
DropDownList-Action-r1.xls
0
 
SmittyProCommented:
For the change event, assuming you use Data Validation, you would set the target range:

Set rng = Target.Parent.Range("BQ2")

And at the bottom under the "Do your thing here" part you'd add:

Call filtercode
0
 
ctownsen80Author Commented:
Got it working! Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now