Listbox.RowSource problem

[Tim Coddington]

Given a simple form with one listbox, I have loaded the code as follows:

Private Sub UserForm_Initialize()
ListBox1.RowSource = "A1:A26"
End Sub
Private Sub ListBox1_Click()
ListBox1.RowSource = ""
ListBox1.RowSource = "A9:A14"
End Sub

where A1 .. A26 are the letters of the alphabet.
Question 1:
Putting a break in Listbox1_Click() shows that when I click on the listbox,
ListBox1_Click() fires two times. Why is that so?
Question 2:
When I click on the listbox, why aren't the contents of the listbox changed
to reflect the new rowsource?

Thanks for any help!
-Tim

 

[Charles Harmon]

Tim,

If you put this code in a module

Private Sub UserForm_Initialize()
Userform1.ListBox1.RowSource = "A1:A26"
End Sub
and this in your code for userform
Private Sub ListBox1_Click()
Unload userform1
With Userform1
.ListBox1.RowSource = ""
.ListBox1.RowSource = "A9:A14"
..show
End With
End Sub
I beleive it should do what you want.

Charles

 

[Tim Coddington]

Are you saying that every time I need to reset .RowSource I need to save all
my other fields on that form, unload the form, adjust .RowSource, and then
reload all my other fields????

 

[Charles Harmon]

Tim,
I think when you Initialize the userform it retains the current information.
Then when you select an item in the listbox (even though you say to clear
the listbox) it still holds the image from the Initialization. By dismissing
the form then recall it now takes on the new properties. Maybe someone else
can explain it better than me.

Charles

 

[Tim Coddington]

Thanks much Mr. Harmon. I suppose I'm stuck with .additem type technology.

 

[Charles Harmon]

Tim,
It was suggested that you use Dbl_Click by mangesh_yadav on the Forum. That
should solve your problem.

 

[Tim Coddington]

I'm sorry, Mr Harmon, but I do not know about any forum. How do I get there
to read up on this artical by mangesh_yadav?

 

[Charles Harmon]

It's at www.excelforum.com. This is the forum that I was referring to.
It was suggested that you use the Dbl_Click.

Charles

 

[Tim Coddington]

Thanks much Mr Harmon!
-Tim

It's at www.excelforum.com. This is the forum that I was referring to.
It was suggested that you use the Dbl_Click.

Charles