|
|
|
![]() |
|
|||||||
|
||||||||
![]() |
| Thread Tools | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
1510's Scouting
I created an Excel database to automatically create sheets for a variable number of teams. It uses the built in VB Editor, and creates a new Workbook each time.
Tell me your thoughts, I'm going to experiment some more to make this transferrable over many years. Read about/get it here. |
|
#2
|
|||||
|
|||||
|
Re: 1510's Scouting
When I try to open the workbook, I get an error that states there is unreadable content and If I want to, I can click "Yes" to recover the contents of the workbook. When I do, I see a list of team numbers in the leftmost column of the first sheet, and everything else is blank. In the VBA editor, there are no macros.
I'm using Excel 2007. Is it something to do with that, or is there another setting I need to activate? |
|
#3
|
|||
|
|||
|
Re: 1510's Scouting
I don't know, I'm using excel 2003 so I don't know if that could be it. The list of team numbers on the left is just the list of teams going to the Portland, OR regional and is there to show where to put the team numbers, or as an example. I'll reupload the file and see if that will fix it. I'll also attach a text document with the actual VBA code so you could copy-paste if all else fails (or just to look at).
|
|
#4
|
|||
|
|||
|
Re: 1510's Scouting
There have been a few updates to the Workbook, they are all still on the same paper. Let me know if you want a copy of the new VBA code, or if there are any new problems. As far as I can tell, this should be the final version. Check the Readme for information on the updates.
|
|
#5
|
||||
|
||||
|
Re: 1510's Scouting
Nice program. I've got a few recommendations.
Your readme doc could use a little more explanation on how to use the system. For example, what values do your scouters put in their sheets? At the beginning you ask for the number of teams. You can get this automatically with code similar to: With Worksheets("Team List") Set r = .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown)) End With For Each cell In r TeamNumber = cell.Value ' do something like create a worksheet with it Next It look like you are programmatically creating the team sheets. While this is a cool excercise in programming, I prefer to create a template sheet, then copy it. This allows me to use the built in WYSIWYG editor provided by Excel. The complete logic I'm using to create sheets from a list of team #'s is: Sub Create_Teams() ' Deletes existing team sheets, then creates one sheet per team represented in a ' list of teams. ' ' Assumes teams are in worksheet "Team List", ' and a team template sheet "Team Template" exists Dim r As Range, cell As Range ' Confirm this is what they want to do, sheets will be blown away If (MsgBox("Executing this routine will destroy the contents of any existing team sheets. Continue?", vbYesNo, "Warning") = vbYes) Then Application.DisplayAlerts = False ' get rid of annoying confirmation of delete for each For Each Sheet In ThisWorkbook.Sheets If (Left(Sheet.Name, 2) = "T-") Then ' if sheet begin with "T-" delete it Sheet.Delete End If Next Application.DisplayAlerts = True ' turn alerts back on With Worksheets("Team List") ' get a collection of cells, starting a A2, going down Set r = .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown)) End With For Each cell In r ' for each team # LastSheet = Sheets.Count ' get last sheet # TeamNumber = cell.Value ' copy team # Sheets("Team Template").Copy After:=Sheets(LastSheet) ' copy template sheet to new sheet at end Sheets(LastSheet + 1).Name = "T-" & TeamNumber ' rename sheet to T-<team #> ' Sheets(LastSheet + 1).Range("B1").Value = TeamNumber ' set one of the cells in new sheet to team # Next End If End Sub I'm no expert on programming Excel. This is just a bunch of code I've cobbled together based on previous VB programming experience and lots of Google searches. There is probably plenty of room for improvement on this code. Good luck |
|
#6
|
|||
|
|||
|
Re: 1510's Scouting
Thanks for your suggestions. I knew there was a way to get the number of cells in a column/row but I don't know the exact code. That's why I went with the inputbox. I did make a template sheet for each team sheet, but I then transcribed it to VBA (it took forever). Because I am creating an entirely new workbook, this was the "easiest" way without having to worry about cross-copying from one workbook to another. I know that that is relatively easy, but this way I can add (in the future) something to automate the different scoring types (last year we had herding, hurdling, racing; this year we have only moon rocks, empty cells). That's something I plan do to over the summer. I'll try to make the readme a little more user friendly and I'll put that up sometime over this weekend.
That should cover everything. |
|
#7
|
|||
|
|||
|
Re: 1510's Scouting
Version 2.4 is out. I have rewritten the readme to make it a little easier to read (hopefully) and updated a couple things. Thanks for the suggestions.
|
|
#8
|
|||
|
|||
|
Re: 1510's Scouting
Quote:
If you're confused about the quoted statement, what I mean is that, every year we have a different challenge. So every year we have to remake the scouting system for that year, to incorporate the new scoring types. My scouting macro will auto-create the workbook based on user input. This way, you will drastically cut down on time and the amount of mistakes you might possibly make. This is similar to the team numbers sheet in my previous macro, where you can have as many, or as few, teams as you want, and the macro will make the workbook for that regional. Currently, I am about 50% down with the new program. Please tell me if you think I should add/remove anything, and what you think of the project in general. |
|
#9
|
|||
|
|||
|
Re: 1510's Scouting
I should be done with the program by Christmas (about 90% done now), and then I'll upload it here. Until then, you can download what I'm currently working on here. It will give you an idea of what to expect.
If you have any questions, or want me to add/remove anything, just email me/pm me/post here. EDIT: To view the readme, click here. Like with Scouting Template.xls, this is by no means the final version, and is constantly being updated. If you have any questions about wording, PLEASE voice them, I am trying to make this as user friendly as possible. Last edited by little.goetz : 24-11-2009 at 18:49. |
![]() |
| Thread Tools | |
| Display Modes | Rate This Thread |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Web based scouting system (498-Scouting, v2) | Nibbles | Scouting | 6 | 08-03-2009 21:48 |
| Team 1510's Electronic's Board | Andy Goetz | Robot Showcase | 1 | 09-02-2008 20:35 |
| Scouting Made Easy - Announcing the FIRST Scouting Network | Adam Richards | Scouting | 24 | 17-11-2007 14:58 |
| Scouting | CarsomyrXIII | Regional Competitions | 1 | 30-03-2005 23:32 |
| SCOUTING threads Go in the SCOUTING forum -- They will be __DELETED__ from General! | Brandon Martus | General Forum | 3 | 19-02-2003 22:05 |