VBA Forms in Excel (viewing a list of data via a form)

[Calum McLean]

Hello,

Can anyone help me, I have created a workbook for entering competitors
details into one worksheet, then I would like another worksheet to
enter their scores.

I'm using vba forms to enter the data into the worksheets with MS
Excel XP.

I can enter the competitor information via a form no problem, and if I
just use another simple form I can enter scores, however I want to
ensure that the data being entered is not messed up, or the same
competitor is added twice etc.

As you can see I'm falling down a little on the scores part. What I've
done/am trying to do is as follows:

When a new competitor is added their full details are added to the
competitors worksheet, I then copy their number only, and add this to
the scores sheet, in a little 'table', there are three events, I would
like to use a combo box to look up their number then add the scores
for 1, 2 or all three events.

Is there a way of linking the form to the 'table' in the scores
worksheet so that as I scroll through the competitors I can see if
I've added a score already, if not, I could enter the score from one
of the three combo boxes? If I have added a score previously I would
like to be able to see it.

I know that I could do this using the datasource and datafield with
full VB and a database backend, but for this I'd like to stick to
Excel.

Thanks in advance.

 

[Dick Kusleika]

Calum

I think this is definitely do-able, but I'm unclear on how things are set
up.

It sounds like you need one form (or nested forms) for both scores and
competitors. Maybe even a multitab with competitor information on the first
tab and score comboboxes on the second tab. You can't easily "link" the
values of the comboboxes, but with a little code it should be OK. For
instance, if you have textbox with the competitor number, you could use the
value of that textbox to find it on the scores sheets and populate the
comboboxes with the scores.

Private Sub TextBox1_Change()

Dim FndComp as Range

If Len(Me.TextBox1.Text) > 0 Then

Set FndComp = Sheets("Scores").Columns(1).Find(Me.TextBox1.Text)

If Not FndComp Is Nothing Then
Me.ComboBox1.Value = FndComp.Offset(0,1).Value
Me.ComboBox2.Value = FndComp.Offset(0,2).Value
Me.ComboBox3.Value = FndComp.Offset(0,3).Value
End If

End Sub