[SOLVED] Listbox.RowSource problem

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

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" <jacod@comcast.net> wrote in message
news:u_6dncb--pUuPEHcRVn-iw@comcast.com...
> 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
>
>

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" <maricharm@cableone.net> wrote in message
news:uLqfPz58EHA.2900@TK2MSFTNGP09.phx.gbl...
> 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" <jacod@comcast.net> wrote in message
> news:u_6dncb--pUuPEHcRVn-iw@comcast.com...
> > 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
> >
> >
>
>

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" <jacod@comcast.net> wrote in message
news:QYydnTnjoLT4VUHcRVn-hA@comcast.com...
> 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" <maricharm@cableone.net> wrote in message
> news:uLqfPz58EHA.2900@TK2MSFTNGP09.phx.gbl...
>> 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" <jacod@comcast.net> wrote in message
>> news:u_6dncb--pUuPEHcRVn-iw@comcast.com...
>> > 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
>> >
>> >
>>
>>
>
>

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

"Charles Harmon" <maricharm@cableone.net> wrote in message
news:%231EtrfD9EHA.3616@TK2MSFTNGP11.phx.gbl...
> 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" <jacod@comcast.net> wrote in message
> news:QYydnTnjoLT4VUHcRVn-hA@comcast.com...
> > 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" <maricharm@cableone.net> wrote in message
> > news:uLqfPz58EHA.2900@TK2MSFTNGP09.phx.gbl...
> >> 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" <jacod@comcast.net> wrote in message
> >> news:u_6dncb--pUuPEHcRVn-iw@comcast.com...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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

Charles,

if that was the case, it should have behaved in the same manner for a single-click event as well as a double-click event, whereas it doesn't. See my earlier post (#3).

- Mangesh

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

"Tim Coddington" <jacod@comcast.net> wrote in message
news:z5qdnYwNBOGCgkPcRVn-jA@comcast.com...
> Thanks much Mr. Harmon. I suppose I'm stuck with .additem type
> technology.
>
> "Charles Harmon" <maricharm@cableone.net> wrote in message
> news:%231EtrfD9EHA.3616@TK2MSFTNGP11.phx.gbl...
>> 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" <jacod@comcast.net> wrote in message
>> news:QYydnTnjoLT4VUHcRVn-hA@comcast.com...
>> > 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" <maricharm@cableone.net> wrote in message
>> > news:uLqfPz58EHA.2900@TK2MSFTNGP09.phx.gbl...
>> >> 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" <jacod@comcast.net> wrote in message
>> >> news:u_6dncb--pUuPEHcRVn-iw@comcast.com...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>