ListBox Control!?! HELP! Strange problem....

[AmyJade]

Hi There,

I have a very strange problem. Whenever I select an item from a drop down listbox in Excel, the selection disappears on the list but it does fill in the predefined range of cells. It seems to delete the item's name from the listbox.

Any thoughts?

AmyJade

 

[SkipVought]

AmyJade,

What is the code in the Listbox_Click or Listbox_Change event?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AmyJade]

My code is written is a general module as follows:

Sub FundModelSelection()

Sheets("Returns").Select
Range("M4").Select

If Range("M4") = 1 Then
Call Fund1
End If

If Range("M4") = 2 Then
Call Fund2
End If

If Range("M4") = 3 Then
Call Fund3
End If

If Range("M4") = 4 Then
Call Fund4
End If

If Range("M4") = 5 Then
Call Fund5
End If

If Range("M4") = 6 Then
Call Fund6
End If

If Range("M4") = 7 Then
Call Fund7
End If

If Range("M4") = 8 Then
Call Fund8
End If

If Range("M4") = 9 Then
Call Fund9
End If

If Range("M4") = 10 Then
Call Fund10
End If

End Sub

Sub Fund1()
Range("B3:B65").Select
Sheets("Summary").Select

Range("B3:B65").Select
Selection.Copy
Sheets("Returns").Select

Range("B3").Select
ActiveSheet.Paste
End Sub


Sub Fund2()

Range("C3:C65").Select
Sheets("Summary").Select

Range("D365").Select
Selection.Copy
Sheets("Returns").Select

Range("C3").Select
ActiveSheet.Paste

End Sub

Sub Fund3()

Range("D365").Select
Sheets("Summary").Select

Range("F3:F65").Select
Selection.Copy
Sheets("Returns").Select

Range("D3").Select
ActiveSheet.Paste

End Sub

Sub Fund4()
Range("E3:E65").Select
Sheets("Summary").Select

Range("G3:G65").Select
Selection.Copy
Sheets("Returns").Select

Range("E3").Select
ActiveSheet.Paste
End Sub

Sub Fund5()

Range("F3:F65").Select
Sheets("Summary").Select

Range("H3:H65").Select
Selection.Copy
Sheets("Returns").Select

Range("F3").Select
ActiveSheet.Paste
End Sub

Sub Fund6()
Range("G3:G65").Select
Sheets("Summary").Select

Range("I3:I65").Select
Selection.Copy
Sheets("Returns").Select

Range("G3").Select
ActiveSheet.Paste

End Sub

Sub Fund7()

Range("H3:H65").Select
Sheets("Summary").Select

Range("J3:J65").Select
Selection.Copy
Sheets("Returns").Select

Range("H3").Select
ActiveSheet.Paste
End Sub

Sub Fund8()

Range("I3:I65").Select
Sheets("Summary").Select

Range("K3:K65").Select
Selection.Copy
Sheets("Returns").Select

Range("I3").Select
ActiveSheet.Paste

End Sub

Sub Fund9()

Range("J3:J65").Select
Sheets("Summary").Select

Range("L3:L65").Select
Selection.Copy
Sheets("Returns").Select

Range("J3").Select
ActiveSheet.Paste
End Sub

Sub Fund10()

Range("K3:K65").Select
Sheets("Summary").Select

Range("M3:M65").Select
Selection.Copy
Sheets("Returns").Select

Range("K3").Select
ActiveSheet.Paste
End Sub


So the coding is uniform for each listbox item as you can see. I just don't understand why each item I select disappears....

 

[SkipVought]

Where is the ListFillRange for each Listbox?

Just take ONE listbox. I need to know what happens when a selection is made. Need to know everything that the listbox is programatically associated with.

BTW, your code can be GREATLY simplified -- I will post.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Sub FundModelSelection()
    With Sheets("Returns")
        Select Case Range("M4")
            Case 1
                Sheets("Summary").Range("B3:B65").Copy _
                    Destination:=.Range("B3")
            Case 2: Fund2
                Sheets("Summary").Range("D3:D65").Copy _
                    Destination:=.Range("C3")
            Case 3: Fund3
                Sheets("Summary").Range("F3:F65").Copy _
                    Destination:=.Range("D3")
            Case 4: Fund4
                Sheets("Summary").Range("G3:G65").Copy _
                    Destination:=.Range("E3")
            Case 5: Fund5
                Sheets("Summary").Range("H3:H65").Copy _
                    Destination:=.Range("F3")
            Case 6: Fund6
                Sheets("Summary").Range("I3:I65").Copy _
                    Destination:=.Range("G3")
            Case 7: Fund7
                Sheets("Summary").Range("J3:J65").Copy _
                    Destination:=.Range("H3")
            Case 8: Fund8
                Sheets("Summary").Range("K3:K65").Copy _
                  Destination:=.Range("I3")
            Case 9: Fund9
                Sheets("Summary").Range("L3:L65").Copy _
                  Destination:=.Range("J3")
            Case 10: Fund10
                Sheets("Summary").Range("M3:M65").Copy _
                    Destination:=.Range("K3")
        End Select
    End With
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884