Drop down boxes in VBA

  • Follow


Hi

I have two different drop down boxes on two different sheets. I want to make 
a macro that will automatically change the second drop down box when someone 
manually updates the first drop down box. I am currently using the code below 
but it only works for the first person ("Adrienne Zwarts"). Can anyone help 
me?

Sub Adviser()

Dim Adviser(2) As String
Dim i As Integer
Dim var

Adviser(0) = "Adrienne Zwarts"
Adviser(1) = "Andrew Cuttriss"
Adviser(2) = "Anna Tapp"

Sheets("Cover Letter").Select
If Range("D22") = Adviser(i) Then
    Sheets("Nov09").Select
    Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)


End If

End Sub

P.S. I'm very new to this
0
Reply Utf 1/11/2010 11:15:01 PM

Modify this:

If Range("D22") = Adviser(i) Then
    Sheets("Nov09").Select
    Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)

End If

To this:

For i = 0 To 2
    If Range("D22") = Adviser(i) Then
         Sheets("Nov09").Select
         Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)
    End If
Next

But it will still only match one of the three names.  Why don't you explain 
what you are trying to do and maybe someone can give you a solution that 
will use all three names.






"Haden" <Haden@discussions.microsoft.com> wrote in message 
news:A28B2FB4-6AD0-4CA2-AB5E-62027A801E26@microsoft.com...
> Hi
>
> I have two different drop down boxes on two different sheets. I want to 
> make
> a macro that will automatically change the second drop down box when 
> someone
> manually updates the first drop down box. I am currently using the code 
> below
> but it only works for the first person ("Adrienne Zwarts"). Can anyone 
> help
> me?
>
> Sub Adviser()
>
> Dim Adviser(2) As String
> Dim i As Integer
> Dim var
>
> Adviser(0) = "Adrienne Zwarts"
> Adviser(1) = "Andrew Cuttriss"
> Adviser(2) = "Anna Tapp"
>
> Sheets("Cover Letter").Select
> If Range("D22") = Adviser(i) Then
>    Sheets("Nov09").Select
>    Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)
>
>
> End If
>
> End Sub
>
> P.S. I'm very new to this 


0
Reply JLGWhiz 1/11/2010 11:41:24 PM

1 Replies
1048 Views

(page loaded in 0.045 seconds)

6/3/2014 6:00:53 PM


Reply: