Transfer Data from Userform to Worksheet Table

[How How]

Hi, I have a sports workbook to record data of up to 20 teams which works well except it takes too long to enter all the data, so to speed things up I created a USERFORM that selects the players into their playing positions…… Works ok

The issue I have is that once the players have been selected in 10 different textboxes named Tbx1 to Tbx10 I need some code that

1. Looks at the week number in a combobox (CbxWeek).
2. On a click of the Transfer command button it puts the players playing position in to the main table in the correct week column (W1 in the example).
3. It would warn if the W1 etc Cells were already populated.


Example

Name Team W1 W2 W3
A. Smith 5 Team A 3
A. Smith 6 Team A 5

[How How]

Better exapmle

Table eg.JPG

[mike7952]

can you upload a sample workbook?

[How How]

Mike, Thanks for replying & I have made an example sheet but for some reason the attach button isn't working..... So frustrating

Capture1.JPG

Capture2.JPG

Userform.xlsm

[How How]

Mike, it appears I was able to attach the file using insert image!!

[mike7952]

Are those all buttons next to the names? You can upload a work by clicking the Go Advanced and scrolling down to Manage Attachments and attaching your workbook

[How How]

Mike, Yes they are all buttons. The file is attached under the screenshots (Namemed "userform,xlsm")

[mike7952]

Okay I see it now, I will work on this while I watch these NFL Playoff games. So give me a little time

[mike7952]

Take a look at the attached workbook. I did away with the Textbox's and Buttons and added a Listbox. Added code to the Team Combobox Change Event to load names to your Listbox. When the Names are loaded to the Listbox, I'm adding the Row number to Column 2 of the Listbox. This used to write back the data to the sheet.

To find the column for the week, I'm using the Right(ComboxTeam.Value,1) to get what week. And Looking for "W" & Right(ComboxTeam.Value,1) in row 1.

[mike7952]

Duplicate post

[How How]

Mike, was having prods opening but got it now....... I've had a play with it & it is defo better than the way I had it set up...... The fact once you select a player it is removed from the 1st listbox...... That will stop accidental duplicates.

Thank you very much.......

I'm going to add a button to open another userform to add a player & team to the sheet my code so far is below

P.s in my real worksheet I have Col b = last name, col C = First name & col D = full name via a formula, Col F is the team...... Is this the best way?

Please Login or Register  to view this content.

[mike7952]

Cleaned up your code some. No need to select cells. I guess I'm lost to why you have another userform? Are you using my updated workbook or are you still using yours?

Please Login or Register  to view this content.

[How How]

Mike, If choosing the players & a "new" player has played, that player will not be in the list when choosing the ten players so I have added a button to add a player & his team to the main excel sheet via a 2nd userform triggered from your userform...... Lazy but quicker!! (Players can be added throughout the season & remember could be as much as 20 teams).

Thanks again...... I will try & learn from the codes you have supplied....... I spend hours/days doing this but I must enjoy it!!

I only have 1 concern in that the position numbers can be overwritten if the wrong week is accidently selected but I will try to put a check in your code prior to writing the positions.

Your help is very much appreciated

[mike7952]

You do something like this to check the range and a Message box to either clear the range and rewrite the cells or cancel.

Please Login or Register  to view this content.

[How How]

Thanks Mike, I've got it all working with my "real" workbook except the last bit of code that checks if positions have been already entered. I was hoping to find a way that when the add players button was pressed it would take the team & week number from the userform CbxTeam & CbxWeek combo boxes then do a count to see the team had numbers already entered in that week

IF Team B & Week 3 was selected in the userform then if ant numbers were found it would have exit without overwriting.

I did try your code & it worked for individual columns, could I use that for the above if I referenced the userform combo boxes?

[mike7952]

Replace your btnAddPlayers with the code below.

Please Login or Register  to view this content.

Add this code in Standard module where you have your SmileyFace1_Click

Please Login or Register  to view this content.

[How How]

Mike......... Absolutely perfect, I appreciate you have done more than the original query....Thank you very much