Dynamically assign items to a list box

[Fernando Ronci]

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: (e-mail address removed)

 

[Tom Ogilvy]

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

 

[Fernando Ronci]

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

 

[Chip Pearson]

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]

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]

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]

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