Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Form design question.
A lot of you have been helping me out over the last week with my scoring
program. I have got a basic spreadsheet that works now but is slow to operate because of the number of formulas that are in operation. I am trying to make things more efficient with a form and a bit of code. What I have at the moment is 7 classes of riders with 50 teams in each class and 20 laps per team. I have a running sheet on the front page where you type in the riders number and the time gets automatically stored in the cell beside the rider number (code with the now function). I use countif on the rider numbers to count what lap it is and then I create a cell that is the combination of the rider number and lap so I can do a single vlookup on the data. eg. If it is rider 100 and his lap 2 the number that represents this is 100.02 (100+lap/100). So sheet looks like this Rider Time Lap ID No. 100 7:34:23 1 100.01 100 7:50:26 2 100.02 I then have a worksheet for each class where the lap times get sorted.It is in the format Rider Lap1 Lap2 Lap3 Lap4 .......Lap20 100 101 102 ...... 150 As you can imagine there 50x20 vlookups for each class or 7000 vlookups in total happening with every press of enter. It works but probably won't be too good on an old computer. My latest idea is to use this form to get the data and store it in the right cell straight up. That way there are only a few calcs at any one time and the number of riders/laps/classes can be unlimited without using much more computer power. I think I can write the code for the form (well probably will need some assistance on a few points) but my concern is this. I would like to set it up so you just type rider number in the form press enter and the data is stored and the form resets to blank ready for the next rider number. My problem is how do I handle fixing up typos in the rider number. The average user is not going to be an excel expert and will not necessarily be familiar with the spreadsheet layout. They need to be able to fix them on the run. My thoughts were to have say 5 text boxes on the form and every time you press enter the rider numbers move down the boxes one. This would mean you have 5 chances to fix it before it disappears off the screen. To do this the time would have to stay associated with the rider number in the box and move down with it and only get saved to the spreadsheet after it goes from box 5. Can anyone think of an easier way or is the code to do the above simpler than I think. Sorry for the very long story and thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Order form design | Excel Discussion (Misc queries) | |||
how to design a form | New Users to Excel | |||
Design a form | New Users to Excel | |||
Design complicated form | New Users to Excel | |||
design form? | New Users to Excel |