Excel Attributes Spreadsheet Version 2 - Step Two (10/02/2009 06:16)

The first part of this blog can be found right here. The macros and all can be found on that page.

Step Two.

Come back to ML and go to Team -> Individual Training. Drag your mouse to select from just before "Pos" at the top to just beyond the "Total" value of the bottom player listed. The selection should look like the following:


Now hit Ctrl+c (or right click the mouse and select Copy) to copy the selected data and go back to your Excel spreadsheet. Hit Alt+F8 (or Tools -> Macro -> Macros...) and select the Setup_Spreadsheet macro from the list of macros and select Run. This will paste the data that you copied from ML into the worksheet and start the setup sequence. Sit back and watch all the sections being created and all the formulae being entered automatically.


The Button
You should notice the button that is created between Section 2 and three. This will eventually start the ImportPlayerData macro and make the process easier rather than always running the macro manually. The first step in setting the button up is to press Alt+F8 (or Tools -> Macro -> Macros...) and select the FormatButton macro from the list of macros and select Run. This will make it tidy and change the Caption on the button (you can change what it says by changing the ".Caption =" line in the macro). The next step of making the button operational is to link the ImportPlayerData macro to the press of the button. Go to "View --> Toolbars --> Control Toolbox" on the menu of Excel and select the first button (Design Mode) on the toolbar that appears. Double click the non functioning button and this will start the Visual Basic Editor again with the start code already there like this:

---

Private Sub CommandButton1_Click()

End Sub

---

Click in between the lines and add this code - " Call ImportPlayerData " - so it will end up looking like this:

---

Private Sub CommandButton1_Click()
    Call ImportPlayerData
End Sub

---

Go to File -> Close and Return to Microsoft Excel.

Click the first button on the Control Toolbox to turn off the Design Mode and that will return the normal functioning of the mouse in Excel.


Bring the Spreadsheet up to speed.

Your spreadsheet is now set-up and functional. To bring the spreadsheet up to date you will have to change the data in Section 1 to reflect the attributes of your players at the start of the season. Go to a list of your team in ML and bring up the pop-up window of your first attacker listed in the spreadsheet. You have to manually change the data in Section 1 by deducting the gains of the player this season. One of my players currently has the following and to the right is what should be entered into Section 1 under the player's row:

Attribute		Diff	Indicator		Season start attribute entered in Section 1
Quality	90	+2			88
Keeping	48	+2			46
Tackling	87	+3			84
Passing	94	+2			92
Shooting	87	+2			85
Heading	74	+4			70
Speed	88	+1			87
Stamina	92	+1			91
Perception	83	+1			82
Fitness	99

Once you have completed entering the season start data for a player in Section 1, you can compare the values in Section 4 (to the right of Section 1) to the values in the Diff column of the player's pop-up window and it should read in the example above:      2    2    3    2    2    4    1    1    1    
This should ensure you are entering the correct data.

Buying a player.

The first player's row in Section 2 must always be on row 40 as the formulae and macros work on exact placement of cells and any movement must be compensated with the assistance of deleting or inserting blank rows. If you are buying a player then you must insert a row in all sections. Start by adding a row in section 1 with consideration of the players position and the alphabetical order of players in that position (eg if you buy a GK named Noel Griffiths then you must insert a row in between the Goalkeepers named Adam Swan and Xavier Marshall). To insert a row just below row 18, you will click on the 19 of row 19 on the left to select the whole row and then go to Insert -> Row (or use the right click of the mouse). Inserting a row in Section 1 (and Section 4 in the same process)  will move section 2, 3 and 5 down a row. Therefore you must delete a row to keep these lower Sections in their correct positions. I would delete row 36 (the bottom formulae in Section 4 should be in row 37 after inserting a row) to counter the moving down of the other sections. You can leave all the data blank as long as you insert and delete the appropriate rows, then you import the current data from your Individual Training page into the spreadsheet and update the data in Sections 1, 4 and 5 from the imported data in Section 4. Section 1 requires the season start attributes entered (a teamless player can have his data copied directly from Section 4) and Sections 4 and 5 require the name and position of the player entered. You could also update the Pos column so that it counts properly down the column in each section. In section 4 you can just select the row of a player in the same position between columns U and AF and just drag the little square on the bottom right down or up so that all the colours and formulae are copied in two moves.


Selling or sacking a player.

When you sell or sack a player (or he/she retires) you must delete the rows that the player occurs on in each section. Select the rows from their numbers on the left so that the whole row is selected and right-click the mouse and select delete or go to Edit -> Delete. To keep the lower sections in their correct positions after deleting a row, you must insert a row in between each section. Select the row that the formulae are on in Section 4 and insert the row and insert a row below Sections 2 and 5 as well.


If the inserting of and deleting of rows is not done before importing new data with a different squad (ie a player bought, retired, sacked or sold) then everything will be messed up and the best solution is to close the workbook without saving it and trying to get the row insertions and deletions correct before importing the data again.


Future Improvements?
I will continue to look at improvements to the spreadsheet - I am currently looking at deleting and inserting players through an administration sheet that will also allow the changing of the colours that appear in Section 5 when an attribute increases. If you use the spreadsheet and can think of a possible improvement, do not hesitate to contact me through ML Mail ;)


If you took the time to set-up a spreadsheet using this blog, then I hope you find it useful



                       Bozo.