Create Multiple Data Validation Lists in One Sheet with VBA

Question

Good Afternoon. I am building a database with VBA. And now I have three data validation drop down menu in one sheet. Each menu has a list that contains item with which I can click on and call out macros. And the function of those macros are to print out some item on another worksheet. Here is my code for the data validation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
    Select Case Target.Value2
         Case "ABCP"
            Call Macro1
         Case "Accounting Policy"
            Call Macro2
         Case "Audit Committee"
            Call Macro3
         Case "Auto"
            Call Macro4
         Case "Auto Issuer Floorplan"
            Call Macro5
         Case "Auto Issuers"
            Call Macro6
         Case "Board of Director"
            Call Macro7
         Case "Bondholder Communication WG"
            Call Macro8
         Case "Canada"
            Call Macro9
         Case "Canadian Market"
            Call Macro10
         End Select
End If
End Sub

This is the code for the first data validation. I have another two data validation lists that need to have the same functions. However, I cannot assign the cell where the data validation is to another code that is of this format, or the current one will stop working. I have tried to change the Private_sub Worksheet names, but it won't do. How should I do this?

Thank you in advance!

Can you expand on what you're trying to do (perhaps with some psuedocode)? You can't use B3 in the other validations? — BruceWayne
– BruceWayne, Commented May 19, 2016 at 17:16
@BruceWayne. The situation is that, now I have three independent data validations, each with a list of items, which I could click on and call out a macro to run. Just like the code above, when I select an item in cell B3, the corresponding macro will be executed, and this one works perfectly. In the meantime, I have two other data validations that should have same function as this one on the same worksheet. I try to use format to achieve that, only with the targeted address cell changed to B4 or B5, it would not work, and it impairs the current one's function. — Joseph Lin
– Joseph Lin, Commented May 19, 2016 at 17:24
@Nathan_Sav, please check the comment I left or Bruce, it is B3 I asm sure. — Joseph Lin
– Joseph Lin, Commented May 19, 2016 at 17:26

Dirk Reichel · Accepted Answer · 2016-05-19 18:33:02Z

This is not an answer! But comments do not allow questions this complex, and it will be deleted as soon as clarification is given. (or changed to an answer)

~~Right now it is not clear what you want.~~ If the macros to be executed are the same then you can do it like in Nathan_Sav's comment:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$3" Or Target.Address = "$C$3" Then
    Select Case Target.Value2
    Case "ABCP"
      Call Macro1
    Case "Accounting Policy"
      Call Macro2
    ....

But if they call different macros with the same values then you can do it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$3" Or Target.Address = "$C$3" Then
    Select Case Target.Value2
    Case "ABCP"
      If Target.Address = "$B$3" Then Call Macro1 Else Call Macro101
    Case "Accounting Policy"
      If Target.Address = "$B$3" Then Call Macro2 Else Call Macro102
      ....

And if they are completely different then you can do it like that:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$3" Or Target.Address = "$C$3" Then
    Select Case Target.Value2
    Case "ABCP"
      Call Macro1
    Case "Accounting Policy"
      Call Macro2
    ....
    Case "Canadian Market"
      Call Macro10
    Case "ABCP-x"
      Call Macro101
    Case "Accounting Policy-x"
      Call Macro102
    ....
    Case "Canadian Market-x"
      Call Macro110
    End Select

Or split up the 2 cases like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$3" Then
    Select Case Target.Value2
    Case "ABCP"
      Call Macro1
    Case "Accounting Policy"
      Call Macro2
    ....
    Case "Canadian Market"
      Call Macro10
    End Select

  ElseIf Target.Address = "$C$3" Then

    Select Case Target.Value2
    Case "ABCP-x"
      Call Macro101
    Case "Accounting Policy-x"
      Call Macro102
    ....
    Case "Canadian Market-x"
      Call Macro110
    End Select

And if they use the same macros but with different values you can do it via Case "ABCP, "some different string".However, if all validations are needed to evaluate which macro to call, it will change another time.

And if you just need to know which cell was changed in the macro to call you need to hand them over like this: (just an example to show how it works)

Private Sub Worksheet_Change(ByVal Target As Range)
  Call TestMacro(Target)
End Sub

Sub TestMacro(rng as Range)
  Debug.Print rng.Address
End Sub

~~Still some clarification is needed. Please help us in helping you~~

Just to make it more difficult for you to delete ++, actually I think you covered all the bases. And this is as good as an answer as can be given with the information provided.
I have different macros for different data validation lists.I think the third and forth code are exactly what I need. Really appreciated!

Collectives™ on Stack Overflow

Create Multiple Data Validation Lists in One Sheet with VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related