How To Set A ListBox Selection As A Sheet Array

Hi all,

in the following code, for testing purposes, I have success in writing the data from a userform to all 5 sheets as specified in MyArr...

Please Login or Register  to view this content.

What I would like to happen, however, is for the data to be written to one specific worksheet, that worksheet being one of the selections from a listbox (ListBox1 in this case). After trying some variations on ListBox.Selected, Listbox.ListCount, and ListBox.ListIndex, I'm no closer to getting it to work, and again, I think I'm just having problems with the syntax of it all.

ListBox1 Item 1 = Round 1
ListBox1 Item 2 = Round 2
etc

Anyone got some suggestions for me?

Cheers,

AJ

Please Login or Register  to view this content.

lstbox.value is the text displayed whicch in this case is a worksheet name .. References to worksheet cells now can take the form

ThisWorkbook.Worksheets(ShtName).Range("A10").value = "XYZ"

Thanks for taking a look, Andy, but now I'm even more confused than ever and have no idea as to how to use your example with the code I've currently got written.

My ListBox1 is populated using the RowSource Property and if I leave the code in red above as it is, it will write the data to all 5 sheets. What I wanted was for the section in red to be something like ...

Please Login or Register  to view this content.

so that it would only write to the one sheet that was selected from ListBox1 and this is where I was having trouble with getting the syntax right.

Does that help?

Chers,

AJ

Hello ScotyB,

Can you post a copy of your workbook for review?

No worries, Leith,

sorry if the code is a mish-mash but I'm learning as I go...

Basically, each of the Userform 'SUbmit Player Data" buttons should write to the next row of the PlayerData sheet (this bit works fine) AND write to the other sheet depending on what is selected in ListBox1 (so, if Round 01 is selected from ListBox1, it should also write to the Round 01 sheet)

Cheers,

AJ

Hello ScotyB,

Thanks for posting the workbook. You certainly have done a lot of work on this project. Are you using the option buttons in the ListBox for aesthetics? I only ask since the default setting of a ListBox is single selection.

Leith,

thanks for the compliment (I still have much to learn, though) and yes, the option buttons are for aesthetics only.

Cheers,

AJ

Andy,

I took another look at the code you offered and experimented a little - I finally understand now how that works. One question; it will display all the worksheet names in the listbox (including hidden ones) and I wondered if there is a way to modify that code so that it excludes any sheet that doesn't have the word 'Round' in it? Some of the sheets are named Round 1, Round 2, etc, and they're the only ones I'd like to appear in the listbox. Is that possible?

Cheers,

AJ

Boy, oh boy,

sometimes the solution is so obvious, it's embarrassing.

From Andy's snippet of code...

Please Login or Register  to view this content.

... all I had to do was change the order of the worksheets and then change X = 1 to X =7

So, thanks, Andy, for pointing me in the right direction.

Leith, I don't know if you're looking at a solution for me but as soon as I test this new bit with the rest of my code, I'll mark this as solved.

Thanks, guys,

AJ

Thx Scoty and good effort.

To avoid displaying the hidden worksheets modify the code to :-

Please Login or Register  to view this content.

Cool, thanks for that, Andy.

Cheers,

AJ