VBA Code running in same workbook from data validation drop down box

C

cgibson92

New Member
Joined
May 15, 2015
Messages
8
I have 2 VBA codes that i would like both to run when making a selection from a data validation drop down box. Could you please help me incorporate both so they will work.

This code will let me select more than one item in a data validation drop down box.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String, newVal As String

On Error GoTo exitHandler
If Target.Count > 1 Or Target.Text = "" Then Exit Sub
If Intersect(Range("B36,B44"), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
newVal = Target.Text
Application.Undo
oldVal = Target.Text
Target.value = newVal
If oldVal = "" Then GoTo exitHandler

If oldVal = newVal Then
Target.value = ""
ElseIf InStr(1, oldVal, newVal) > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
Else
Target.value = Replace(oldVal, newVal & Chr(10), "")
End If
Else
Target.value = oldVal & Chr(10) & newVal
End If

exitHandler:
Application.EnableEvents = True
End Sub

And this code will open a worksheet when it is selected in the data validation drop down box.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Admin Fee" Then
Sheets("Admin Fee").Visible = True
Sheets("Admin Fee").Select
Sheets("Flat Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Business Development Bonus" Then
Sheets("Flat Fee").Visible = True
Sheets("Flat Fee").Select
Sheets("Admin Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Business Development Fee" Then
Sheets("Override").Visible = True
Sheets("Override").Select
Sheets("Admin Fee").Visible = False
Sheets("Flat Fee").Visible = False
Sheets("Market Share").Visible = False

ElseIf Target.Value = "Flat Fee" Then
Sheets("Flat Fee").Visible = True
Sheets("Flat Fee").Select
Sheets("Admin Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Global Business Development Bonus" Then
Sheets("Market Share").Visible = True
Sheets("Market Share").Select
Sheets("Admin Fee").Visible = False
Sheets("Flat Fee").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Maintenance Bonus" Then
Sheets("Market Share").Visible = True
Sheets("Market Share").Select
Sheets("Admin Fee").Visible = False
Sheets("Flat Fee").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Override" Then
Sheets("Override").Visible = True
Sheets("Override").Select
Sheets("Admin Fee").Visible = False
Sheets("Flat Fee").Visible = False
Sheets("Market Share").Visible = False

ElseIf Target.Value = "Partnership Fee" Then
Sheets("Flat Fee").Visible = True
Sheets("Flat Fee").Select
Sheets("Admin Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Transaction / Service Fee" Then
Sheets("Admin Fee").Visible = True
Sheets("Admin Fee").Select
Sheets("Flat Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

ElseIf Target.Value = "Select Incentive Type (s)" Then
Sheets("Admin Fee").Visible = False
Sheets("Flat Fee").Visible = False
Sheets("Market Share").Visible = False
Sheets("Override").Visible = False

End If
End Sub


​Thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi and welcome to the forum.

Try this (not tested).

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] rngDV  [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] oldVal [color=darkblue]As[/color] [color=darkblue]String[/color], newVal [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] exitHandler
    
    [color=darkblue]If[/color] Target.Count > 1 [color=darkblue]Or[/color] Target.Text = "" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("B36,B44"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
    
        Application.EnableEvents = [color=darkblue]False[/color]
        newVal = Target.Text
        Application.Undo
        oldVal = Target.Text
        Target.Value = newVal
        [color=darkblue]If[/color] oldVal = "" [color=darkblue]Then[/color] [color=darkblue]GoTo[/color] exitHandler
    
        [color=darkblue]If[/color] oldVal = newVal [color=darkblue]Then[/color]
            Target.Value = ""
        [color=darkblue]ElseIf[/color] InStr(1, oldVal, newVal) > 0 [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Right(oldVal, Len(newVal)) = newVal [color=darkblue]Then[/color]
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
            [color=darkblue]Else[/color]
                Target.Value = Replace(oldVal, newVal & Chr(10), "")
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Else[/color]
            Target.Value = oldVal & Chr(10) & newVal
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]Else[/color]
        
        [color=darkblue]If[/color] Target.Value = "Admin Fee" [color=darkblue]Then[/color]
            Sheets("Admin Fee").Visible = [color=darkblue]True[/color]
            Sheets("Admin Fee").Select
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Business Development Bonus" [color=darkblue]Then[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]True[/color]
            Sheets("Flat Fee").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Business Development Fee" [color=darkblue]Then[/color]
            Sheets("Override").Visible = [color=darkblue]True[/color]
            Sheets("Override").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Flat Fee" [color=darkblue]Then[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]True[/color]
            Sheets("Flat Fee").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Global Business Development Bonus" [color=darkblue]Then[/color]
            Sheets("Market Share").Visible = [color=darkblue]True[/color]
            Sheets("Market Share").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Maintenance Bonus" [color=darkblue]Then[/color]
            Sheets("Market Share").Visible = [color=darkblue]True[/color]
            Sheets("Market Share").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Override" [color=darkblue]Then[/color]
            Sheets("Override").Visible = [color=darkblue]True[/color]
            Sheets("Override").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Partnership Fee" [color=darkblue]Then[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]True[/color]
            Sheets("Flat Fee").Select
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Transaction / Service Fee" [color=darkblue]Then[/color]
            Sheets("Admin Fee").Visible = [color=darkblue]True[/color]
            Sheets("Admin Fee").Select
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]ElseIf[/color] Target.Value = "Select Incentive Type (s)" [color=darkblue]Then[/color]
            Sheets("Admin Fee").Visible = [color=darkblue]False[/color]
            Sheets("Flat Fee").Visible = [color=darkblue]False[/color]
            Sheets("Market Share").Visible = [color=darkblue]False[/color]
            Sheets("Override").Visible = [color=darkblue]False[/color]
    
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
exitHandler:
    Application.EnableEvents = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thanks for the reply AlphaFrog, i put the code in and it works as far as letting me select multiple items from the drop down box, but it does not open the tab from what i selected in the multiple item drop down box... hope that makes sense... :)

Thanks
Cindy
 
Upvote 0
Hi Cindy,

I think I misinterpreted what you are doing. I thought it was two separate dropdowns for the two procedures.

Give this a go.

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] rngDV  [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] oldVal [color=darkblue]As[/color] [color=darkblue]String[/color], newVal [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color], ws [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] exitHandler
    
    [color=darkblue]If[/color] Target.Count > 1 [color=darkblue]Or[/color] Target.Text = "" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("B36,B44"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
    
        Application.EnableEvents = [color=darkblue]False[/color]
        newVal = Target.Text
        Application.Undo
        oldVal = Target.Text
        Target.Value = newVal
        
        [color=darkblue]If[/color] oldVal <> "" [color=darkblue]Then[/color]
    
            [color=darkblue]If[/color] oldVal = newVal [color=darkblue]Then[/color]
                Target.Value = ""
            [color=darkblue]ElseIf[/color] InStr(1, oldVal, newVal) > 0 [color=darkblue]Then[/color]
                [color=darkblue]If[/color] Right(oldVal, Len(newVal)) = newVal [color=darkblue]Then[/color]
                    Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
                [color=darkblue]Else[/color]
                    Target.Value = Replace(oldVal, newVal & Chr(10), "")
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Else[/color]
                Target.Value = oldVal & Chr(10) & newVal
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    
        Application.ScreenUpdating = [color=darkblue]False[/color]
        
        [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Sheets(Array("Admin Fee", "Flat Fee", "Market Share", "Override"))
            ws.Visible = [color=darkblue]False[/color]
        [color=darkblue]Next[/color] ws
        
        [color=darkblue]For[/color] [color=darkblue]Each[/color] v [color=darkblue]In[/color] Array("Admin Fee", "Business Development Bonus", "Business Development Fee", _
                            "Flat Fee", "Global Business Development Bonus", "Maintenance Bonus", _
                            "Override", "Partnership Fee", "Transaction / Service Fee", _
                            "Select Incentive Type (s)")
                        
            [color=darkblue]If[/color] InStr(Target.Value, v) [color=darkblue]Then[/color]
                [color=darkblue]Select[/color] [color=darkblue]Case[/color] v
                    [color=darkblue]Case[/color] "Admin Fee", "Transaction / Service Fee"
                        Sheets("Admin Fee").Visible = [color=darkblue]True[/color]
                        [color=green]'Sheets("Admin Fee").Select[/color]
                        
                    [color=darkblue]Case[/color] "Business Development Bonus", "Flat Fee", "Partnership Fee"
                        Sheets("Flat Fee").Visible = [color=darkblue]True[/color]
                        [color=green]'Sheets("Flat Fee").Select[/color]
                        
                    [color=darkblue]Case[/color] "Business Development Fee", "Override"
                        Sheets("Override").Visible = [color=darkblue]True[/color]
                        [color=green]'Sheets("Override").Select[/color]
                        
                    [color=darkblue]Case[/color] "Global Business Development Bonus", "Maintenance Bonus"
                        Sheets("Market Share").Visible = [color=darkblue]True[/color]
                        [color=green]'Sheets("Market Share").Select[/color]
                        
                    [color=darkblue]Case[/color] "Select Incentive Type (s)"
                    
                    [color=darkblue]Case[/color] [color=darkblue]Else[/color]
                    
                [color=darkblue]End[/color] [color=darkblue]Select[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] v
        
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
exitHandler:
   Application.EnableEvents = [color=darkblue]True[/color]
   Application.ScreenUpdating = [color=darkblue]True[/color]
   [color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Worksheet_Change Error: " & Err.Number
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Data Validation Multi Select Code needs boosting
Replies
3
Views
251
rlv01
R
M
  • Question Question
Not running code
Replies
0
Views
191
MagicCarpet
M
M
  • Question Question
VBA Macro - hides rows based on a single cell which is limited by data validation
Replies
6
Views
357
My Aswer Is This
My Aswer Is This
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
468
MagicCarpet
M
N
  • Solved
VBA Question
Replies
3
Views
396
RoryA
RoryA

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top