Entering results by Data form - is it possible?

[neil40]

Hi

I have a spreadsheet of Football results in a table such that each team is listed in Column A and also Row 1 so that you can enter the scores as a season progresses. Results are calculated to produce a league table on another sheet.
There are 24 Teams for a League, so Teams in the rows 2 to 25
In the columns, I use 2 cells per result, one for Home score, one for away score, thus 48 columns.

Is it possible to use some sort of Data Form to enter the results, maybe using drop down boxes to select the Home and Away team?

Thanks for any thoughts on this

Neil

[Melvinrobb]

I suggest posting a sample workbook. Would be helpful.

[neil40]

OK, here it is. Created in Excel 2010

[Melvinrobb]

I'm having some trouble with this. Methods I would use don't seem to be working. Hopefully someone can help you out.

[tigeravatar]

Neil,

Attached is a modified version of your provided workbook.
In the 'Results' sheet cell A1 I have added a Form Control Button labelled "Enter Results", which has been assigned to the following macro (in Module1):

Please Login or Register  to view this content.

I created a userform named frm_EnterGameResults which contains 2 comboboxes (for selected the home and away teams), 2 textboxes (for entering their scores), and 2 buttons (Submit - to add an entry to the Results sheet, and Done - to close the userform). Here is the full code I used for the userform (commented as much as possible):

Please Login or Register  to view this content.

[neil40]

Thanks a lot for what you've done tigervator.

However, I tried it out, and entered my first score and got a
Microsoft Visual Basic pop up with
Run-time error '91':
Object variable or With block variable not set

When I Debug, it highlights:
lHomeCol = ws.Rows(1).Find(Me.cbo_HomeTeam.Text).Column 'Find which column the home team is on

Neil

[tigeravatar]

Neil,

Weird, I didn't get that error. Here, give this a try. Replace those Home/Away Row/Col lines with this instead:

Please Login or Register  to view this content.

[neil40]

tigervator

That now works thank you.
One 'however' though, it put the result in the wrong place!
Perhaps I forgot to explain that the Home team is in the rows and the away team in the columns!

Well, on second glance, if I fill in for example Yeovil home team, Coventry away team, it actually filled the result in two places on the grid, namely with each being at home, and each being away

[tigeravatar]

I'm not sure I understand. If Brentford is the home team with 3 points and Carlisle United is the away team with 1 point, what should get populated where?

[neil40]

In that scenario, where Brentford are at home, scoring 3 goals (not points!!) and Carlisle are away scoring 1 goal the following cells would be populated.
The 3 would go in H3 and the 1 in I3
In the scenario I reported (Yeovil Home Vs Coventry Away) the following cells were populated - AV7/AW7 and also L25/M25 (The latter should be the correct population)

[tigeravatar]

In that case, change the btn_Submit_Click() code to the following:

Please Login or Register  to view this content.

[neil40]

tigeravatar (spelt it right this time!!)

This is perfect. Thanks so much for the work you did on this.

Have a great weekend
Neil