Hi,

Is there a way to automatically add items to a list box and then
programatically act according to the item selected by the user ?
This is the scenario:
I have a spreadsheet that keeps statistics of basketball games. Each time
the assistant to the coach needs to add the stats of a new game, he/she
clicks on a button (which is linked to a macro) and a new blank template is
appended to the end of the sheet. Then he/she fills the recently added
template and some calculations are done. Before exiting, the spreadsheet is
saved. I've been able to implement this by means of a couple of macros and a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows, it is more and more
time-consuming to locate the stats of any given game, requiring many
page-up's and page-down's and/or Edit | Search, so I thought that a feature
to automatically "jump" to the stats of the game of choice may come handy.
For this task, I thought of adding a list box (which should be linked to a
macro) whose items must be picked from -for example- the first cell of the
first row of every game, and then, according to the item or "game" selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that could -for example- run
when the .XLS file is opened and create the list box on the fly, however,
although I can programatically parse the whole sheet and read the content of
the cells that identify each game, I don't know how to feed the content of
these cells to the list box. I have no doubts it has to be possible to
implement this.

I'll highly appreciate if someone points me in the right direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

for rw = 1 to 2000 step 9

With Worksheets("Stats")
if isempty(.cells(rw,1)) then exit for
me.listbox1.AddItem .Cells(rw,1).Value & " " & .cells(rw,2).Value
End With

Next

--
Regards,
Tom Ogivly


"Fernando Ronci" wrote in message
...
Hi,

Is there a way to automatically add items to a list box and then
programatically act according to the item selected by the user ?
This is the scenario:
I have a spreadsheet that keeps statistics of basketball games. Each time
the assistant to the coach needs to add the stats of a new game, he/she
clicks on a button (which is linked to a macro) and a new blank template
is
appended to the end of the sheet. Then he/she fills the recently added
template and some calculations are done. Before exiting, the spreadsheet
is
saved. I've been able to implement this by means of a couple of macros and
a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows, it is more and more
time-consuming to locate the stats of any given game, requiring many
page-up's and page-down's and/or Edit | Search, so I thought that a
feature
to automatically "jump" to the stats of the game of choice may come handy.
For this task, I thought of adding a list box (which should be linked to a
macro) whose items must be picked from -for example- the first cell of the
first row of every game, and then, according to the item or "game"
selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that could -for example-
run
when the .XLS file is opened and create the list box on the fly, however,
although I can programatically parse the whole sheet and read the content
of
the cells that identify each game, I don't know how to feed the content of
these cells to the list box. I have no doubts it has to be possible to
implement this.

I'll highly appreciate if someone points me in the right direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

Thanks Tom, but I'm getting a compilation error with this:
me.listbox1.AddItem .Cells(rw,1).Value & " " & .cells(rw,2).Value
^^__ Use of keyword "Me" is not valid.

Do you know what can have gone wrong?
Thank you,
Fernando


"Tom Ogilvy" wrote in message
...
for rw = 1 to 2000 step 9

With Worksheets("Stats")
if isempty(.cells(rw,1)) then exit for
me.listbox1.AddItem .Cells(rw,1).Value & " " & .cells(rw,2).Value
End With

Next

--
Regards,
Tom Ogivly


"Fernando Ronci" wrote in message
...
Hi,

Is there a way to automatically add items to a list box and then
programatically act according to the item selected by the user ?
This is the scenario:
I have a spreadsheet that keeps statistics of basketball games. Each
time
the assistant to the coach needs to add the stats of a new game, he/she
clicks on a button (which is linked to a macro) and a new blank template
is
appended to the end of the sheet. Then he/she fills the recently added
template and some calculations are done. Before exiting, the spreadsheet
is
saved. I've been able to implement this by means of a couple of macros
and
a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows, it is more and
more
time-consuming to locate the stats of any given game, requiring many
page-up's and page-down's and/or Edit | Search, so I thought that a
feature
to automatically "jump" to the stats of the game of choice may come
handy.
For this task, I thought of adding a list box (which should be linked to
a
macro) whose items must be picked from -for example- the first cell of
the
first row of every game, and then, according to the item or "game"
selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that could -for example-
run
when the .XLS file is opened and create the list box on the fly,
however,
although I can programatically parse the whole sheet and read the
content
of
the cells that identify each game, I don't know how to feed the content
of
these cells to the list box. I have no doubts it has to be possible to
implement this.

I'll highly appreciate if someone points me in the right direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

Fernando,

If your code is in the userform's code module, the Me keyword
should work fine. If the code is elsewhere, change 'Me' to the
name of the userform.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Fernando Ronci" wrote in message
...
Thanks Tom, but I'm getting a compilation error with this:
me.listbox1.AddItem .Cells(rw,1).Value & " " &
..cells(rw,2).Value
^^__ Use of keyword "Me" is not valid.

Do you know what can have gone wrong?
Thank you,
Fernando


"Tom Ogilvy" wrote in message
...
for rw = 1 to 2000 step 9

With Worksheets("Stats")
if isempty(.cells(rw,1)) then exit for
me.listbox1.AddItem .Cells(rw,1).Value & " " &
..cells(rw,2).Value
End With

Next

--
Regards,
Tom Ogivly


"Fernando Ronci" wrote in message
...
Hi,

Is there a way to automatically add items to a list box and
then
programatically act according to the item selected by the
user ?
This is the scenario:
I have a spreadsheet that keeps statistics of basketball
games. Each
time
the assistant to the coach needs to add the stats of a new
game, he/she
clicks on a button (which is linked to a macro) and a new
blank template
is
appended to the end of the sheet. Then he/she fills the
recently added
template and some calculations are done. Before exiting,
the spreadsheet
is
saved. I've been able to implement this by means of a
couple of macros
and
a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows, it is
more and
more
time-consuming to locate the stats of any given game,
requiring many
page-up's and page-down's and/or Edit | Search, so I
thought that a
feature
to automatically "jump" to the stats of the game of choice
may come
handy.
For this task, I thought of adding a list box (which should
be linked to
a
macro) whose items must be picked from -for example- the
first cell of
the
first row of every game, and then, according to the item or
"game"
selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that
could -for example-
run
when the .XLS file is opened and create the list box on the
fly,
however,
although I can programatically parse the whole sheet and
read the
content
of
the cells that identify each game, I don't know how to feed
the content
of
these cells to the list box. I have no doubts it has to be
possible to
implement this.

I'll highly appreciate if someone points me in the right
direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

Chip,

My code is outside of the userform's code module.
How do I find out (or set) the name of the "listbox" which I want to refer
from the code ?
Sorry, but I'll start learning OOP these days.

Thank you,
Fernando

"Chip Pearson" wrote in message
...
Fernando,

If your code is in the userform's code module, the Me keyword
should work fine. If the code is elsewhere, change 'Me' to the
name of the userform.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Fernando Ronci" wrote in message
...
Thanks Tom, but I'm getting a compilation error with this:
me.listbox1.AddItem .Cells(rw,1).Value & " " &
.cells(rw,2).Value
^^__ Use of keyword "Me" is not valid.

Do you know what can have gone wrong?
Thank you,
Fernando


"Tom Ogilvy" wrote in message
...
for rw = 1 to 2000 step 9

With Worksheets("Stats")
if isempty(.cells(rw,1)) then exit for
me.listbox1.AddItem .Cells(rw,1).Value & " " &
.cells(rw,2).Value
End With

Next

--
Regards,
Tom Ogivly


"Fernando Ronci" wrote in message
...
Hi,

Is there a way to automatically add items to a list box and
then
programatically act according to the item selected by the
user ?
This is the scenario:
I have a spreadsheet that keeps statistics of basketball
games. Each
time
the assistant to the coach needs to add the stats of a new
game, he/she
clicks on a button (which is linked to a macro) and a new
blank template
is
appended to the end of the sheet. Then he/she fills the
recently added
template and some calculations are done. Before exiting,
the spreadsheet
is
saved. I've been able to implement this by means of a
couple of macros
and
a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows, it is
more and
more
time-consuming to locate the stats of any given game,
requiring many
page-up's and page-down's and/or Edit | Search, so I
thought that a
feature
to automatically "jump" to the stats of the game of choice
may come
handy.
For this task, I thought of adding a list box (which should
be linked to
a
macro) whose items must be picked from -for example- the
first cell of
the
first row of every game, and then, according to the item or
"game"
selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that
could -for example-
run
when the .XLS file is opened and create the list box on the
fly,
however,
although I can programatically parse the whole sheet and
read the
content
of
the cells that identify each game, I don't know how to feed
the content
of
these cells to the list box. I have no doubts it has to be
possible to
implement this.

I'll highly appreciate if someone points me in the right
direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

Fernando,

With the UserForm open in VBA, select the listbox in question,
press F4 to display the Properties window, and look at the Name
property.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Fernando Ronci" wrote in message
...
Chip,

My code is outside of the userform's code module.
How do I find out (or set) the name of the "listbox" which I
want to refer
from the code ?
Sorry, but I'll start learning OOP these days.

Thank you,
Fernando

"Chip Pearson" wrote in message
...
Fernando,

If your code is in the userform's code module, the Me keyword
should work fine. If the code is elsewhere, change 'Me' to
the
name of the userform.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Fernando Ronci" wrote in message
...
Thanks Tom, but I'm getting a compilation error with this:
me.listbox1.AddItem .Cells(rw,1).Value & " " &
.cells(rw,2).Value
^^__ Use of keyword "Me" is not valid.

Do you know what can have gone wrong?
Thank you,
Fernando


"Tom Ogilvy" wrote in message
...
for rw = 1 to 2000 step 9

With Worksheets("Stats")
if isempty(.cells(rw,1)) then exit for
me.listbox1.AddItem .Cells(rw,1).Value & " " &
.cells(rw,2).Value
End With

Next

--
Regards,
Tom Ogivly


"Fernando Ronci" wrote in
message
...
Hi,

Is there a way to automatically add items to a list box
and
then
programatically act according to the item selected by
the
user ?
This is the scenario:
I have a spreadsheet that keeps statistics of
basketball
games. Each
time
the assistant to the coach needs to add the stats of a
new
game, he/she
clicks on a button (which is linked to a macro) and a
new
blank template
is
appended to the end of the sheet. Then he/she fills the
recently added
template and some calculations are done. Before
exiting,
the spreadsheet
is
saved. I've been able to implement this by means of a
couple of macros
and
a
bit of user-interface design. So far, so good.
The problem is that as long as the spreadsheet grows,
it is
more and
more
time-consuming to locate the stats of any given game,
requiring many
page-up's and page-down's and/or Edit | Search, so I
thought that a
feature
to automatically "jump" to the stats of the game of
choice
may come
handy.
For this task, I thought of adding a list box (which
should
be linked to
a
macro) whose items must be picked from -for example-
the
first cell of
the
first row of every game, and then, according to the
item or
"game"
selected
by the user, jump to that stat. That simple.
I thought of creating an autoexecutable macro that
could -for example-
run
when the .XLS file is opened and create the list box on
the
fly,
however,
although I can programatically parse the whole sheet
and
read the
content
of
the cells that identify each game, I don't know how to
feed
the content
of
these cells to the list box. I have no doubts it has to
be
possible to
implement this.

I'll highly appreciate if someone points me in the
right
direction to
achieve my goal.
Thank you,

Fernando Ronci
E-mail:

Thank you Chip,
I'm getting closer.
I was able to run this simple test macro:
Private Sub ListBox1_Click()

For rw = 1 To 5
With Worksheets("Hoja1")
Me.ListBox1.AddItem.Cells(rw, 1).Value
End With
Next

End Sub

However, when I run the macro (F5) the listbox is empty, I mean, the test
data at A1..E1 aren't shown within the listbox.
What am I missing ?
Thank you,
Fernando

"Chip Pearson" wrote in message
...
Fernando,

With the UserForm open in VBA, select the listbox in question,
press F4 to display the Properties window, and look at the Name
property.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com