Combo Box selection to validate FORM population

[HDfatboy03]

Trying to create a form where the Combo Box "cboCourse" is loaded from a sheet "CourseInfo". There are 2 ranges "CLkup" containing 2 columns txtCourse and txtTbox and "Database" containing all the columns in the "CourseInfo". Currently my combo box is working thru the properties option Rowsource "CourseInfo!CLkUp" and is working. It's the next part where my opportunity lies ... lol

What I am trying to accomplish is when a selection is made in the combo box example: "Lindale GC (Black)" ... I want the rest of the text fields in the form "txtPar", "txtRating" & "txtSlope" to populate automatically from the excel worksheet "CourseInfo".

Please point me in the right direction, an example, something to read. I am 3 weeks new to all of this ... what started out as a simple spread sheet now seems to have a life of its own. Starting to get a lil addicted ... lol Any help will be greatly appreciated.

thanks HDfatboy03

 

[SamTYler]

ComboBox.TopIndex Property corresponds to "Selected Item.Row" - 1

Assuming your database table has a header, the top DB Item is in Row 2

Therefore TopIndex(0) = DbRow(2)

And Therefore redux:

txtPar.Value = DBSheet.Cells(cboCourse.TopIndex + 2, Column#)

 

[HDfatboy03]

Thanks for the quick reply .... Are you saying thats all I have to do for txtPar for it to populate?

I'm assuming that I need to change DBSheet to CourseInfo?

Thanks again.

 

[HDfatboy03]

Thanks for the suggestion. I tried it this morning and I can't seem to get it to work. I've included some of my code ... can you see anything that I'm doing wrong.

<vba>
Private Sub UserForm_Click()
Private Sub cmdAdd3_Click()
Dim iRow As Long
Dim ws, ws2, ws3 As Worksheet
Set ws = Worksheets("CoursesDB")
Set ws2 = Worksheets("CurDB")
Set ws3 = Worksheets("CourseInfo")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'this works for manual input
ws.Cells(iRow, 190).Value = Me.cboLocation.Value
ws2.Cells(4, "K").Value = Me.cboLocation.Value
ws.Cells(iRow, 191).Value = Me.cbopart.Value
ws2.Cells(4, "J").Value = Me.cbopart.Value

'auto populate tip from excel thread
'txtPar.Value = DBSheet.Cells(cboCourse.TopIndex + 2, Column#)
Me.txtPar.Value = ws3.Cells(cboCourse.TopIndex + 2, 5)

</vba>

Thanks in advance

HDfatboy03

 

[SamTYler]

Private Sub UserForm_Click()
Private Sub cmdAdd3_Click()
Can't use two Click statements to run procedure. Delete first(?) line

Dim iRow As Long
Dim ws, ws2, ws3 As Worksheet
Only ws3 is declared a worksheet,ws & ws2 declared as Variants here. Variants can work, but bad practice. Examples follow:
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
.
Dim ws As Worksheet _
ws2 As Worksheet _
ws3 As Worksheet


Set ws = Worksheets("CoursesDB")
Set ws2 = Worksheets("CurDB")
Set ws3 = Worksheets("CourseInfo")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Suggestion: To speed up procedure and reduce resources
With Me 'Delete all "Me" to End With
'this works for manual input
ws.Cells(iRow, 190).Value = Me.cboLocation.Value
ws2.Cells(4, "K").Value = Me.cboLocation.Value
ws.Cells(iRow, 191).Value = Me.cbopart.Value
ws2.Cells(4, "J").Value = Me.cbopart.Value

'auto populate tip from excel thread
'txtPar.Value = DBSheet.Cells(cboCourse.TopIndex + 2, Column#)

Me.txtPar.Value = ws3.Cells(Me.cboCourse.TopIndex + 2, 5)

End With 'End Suggestion
</VBA>

 

[HDfatboy03]

I made the changes that you suggested and txtPar is still not populating ... any other suggestions. I'm off to work but I will check back tomorrow. Thanks so much for your assistance. I'm really stuck ... lol

 

[SamTYler]

Make this change

'auto populate tip from excel thread
'txtPar.Value = DBSheet.Cells(cboCourse.TopIndex + 2, Column#)

X = .cboCourse.TopIndex
.txtPar.Text = ws3.Cells(.cboCourse.TopIndex + 2, "E")

Then click in the wide grey border on the left edge of the CodePage next to the ".txtPar.Value =" line. That will place a Stop before that line. Click the Stop to remove it.

When the procedure stops, hover the mouse on the X and a tooltip will give the Value of X. Verify there is a value in Column("E"), Row(X + 2).

Press F5 to finish running the procedure.

 

[HDfatboy03]

You lost me ... I was able to add the stop ... not sure how to run the procedure when I press F5 or > button my form shows up and I can't see the code. txtPar doesn't populate. Sorry if I'm not keeping up. I've looked for days ... can't find an example of this: vlookup but in vba. I knew it was going to be a challenge but had no idea ... lol

 

[Norie]

You shouldn't need to use TopIndex, just use ListIndex.

To populate the textboxes you should use the comboboxes change event.

If you can post some sample data it might help - your explanation in the first post didn't quite add up.

Do you have course names, course info, people's names etc?

 

[HDfatboy03]

Hopefully I can clear things up

I have a Form "frmScores" that I want to retrieve data (Like VLOOKUP) from "CourseInfo" database. In "CourseInfo" ... there are 2 ranges: 1. "CLkUp" that contains 2 columns "Course Name" & "Tee Box" & the seond range "FavCourses" that contains ALL the info in that database: course name, tee box, par, rating, slope ... etc.

(Currently combo box "cboCourse" is being populated from the properties window: "RowSource": CourseInfo!CLkUp ... and is working)
What I am TRYING to do ... lol is when combo box "cboCourse" is selected ex.[Lindale Golf Club (BLACK)] the following text boxes I want populated from database "CourseInfo": txtPar, txtSlope, txtRating and 36 other boxes containing yardage and par for all 18 holes for that specific course from that specific tee box.

The remainder of the form "frmScores" is manual input and ALL the information is copied to 2 databases: "CoursesDB" & "CurDB".

The form is working except for this last part that I am having trouble with. I tried to place a screen shot of the form "frmScores" but was unable to. Sorry

Below is ALL the code for the form "frmScores". I know you made the suggestion of deleting all the ME's and I'm going to when I get this form working.

<vba>

Private Sub cmdAdd3_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set ws = Worksheets("CoursesDB")
Set ws2 = Worksheets("CurDB")
Set ws3 = Worksheets("CourseInfo")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the databases
'this works for manual input of first & last name
ws.Cells(iRow, 190).Value = Me.cboLocation.Value
ws2.Cells(4, "K").Value = Me.cboLocation.Value
ws.Cells(iRow, 191).Value = Me.cbopart.Value
ws2.Cells(4, "J").Value = Me.cbopart.Value

'auto populate I hope tip from excel thread
'txtPar.Value = DBSheet.Cells(cboCourse.TopIndex + 2, Column#)
'try 1 Me.txtQty.Value = ws3.Cells(cboCourse.TopIndex + 2, 5)
'try 2 txtPar.Value = Sheets("CourseInfo").Range("E2").Value
'X = .cboCourse.TopIndex
'.txtPar.Text = ws3.Cells(.cboCourse.TopIndex + 2, "E")
.cboCourse.ListIndex
.txtPar.Text = ws3.Cells(.cboCourse.ListIndex + 2, "E")

'copy the data to the databases
'ComboTees tee markers copy to databases
'ws.Cells(iRow, 186).Value = Me.ComboTees.Value
'ws2.Cells(4, "E").Value = Me.ComboTees.Value

'copy the data to the databases
ws.Cells(iRow, 5).Value = Me.cboCourse.Value
ws2.Cells(4, "C").Value = Me.cboCourse.Value
ws.Cells(iRow, 2).Value = Me.txtRating.Value
ws2.Cells(4, "G").Value = Me.txtRating.Value
ws.Cells(iRow, 3).Value = Me.txtSlope.Value
ws2.Cells(4, "H").Value = Me.txtSlope.Value
ws.Cells(iRow, 4).Value = Me.txtPar.Value
ws2.Cells(4, "I").Value = Me.txtPar.Value

'copy the NEW DATA Yardage to databases
ws.Cells(iRow, 6).Value = Me.Yd1.Value
ws2.Cells(7, "C").Value = Me.Yd1.Value
ws.Cells(iRow, 7).Value = Me.Yd2.Value
ws2.Cells(7, "D").Value = Me.Yd2.Value
ws.Cells(iRow, 8).Value = Me.Yd3.Value
ws2.Cells(7, "E").Value = Me.Yd3.Value
ws.Cells(iRow, 9).Value = Me.Yd4.Value
ws2.Cells(7, "F").Value = Me.Yd4.Value
ws.Cells(iRow, 10).Value = Me.Yd5.Value
ws2.Cells(7, "G").Value = Me.Yd5.Value
ws.Cells(iRow, 11).Value = Me.Yd6.Value
ws2.Cells(7, "H").Value = Me.Yd6.Value
ws.Cells(iRow, 12).Value = Me.Yd7.Value
ws2.Cells(7, "I").Value = Me.Yd7.Value
ws.Cells(iRow, 13).Value = Me.Yd8.Value
ws2.Cells(7, "J").Value = Me.Yd8.Value
ws.Cells(iRow, 14).Value = Me.Yd9.Value
ws2.Cells(7, "K").Value = Me.Yd9.Value
ws.Cells(iRow, 15).Value = Me.Yd10.Value
ws2.Cells(7, "M").Value = Me.Yd10.Value
ws.Cells(iRow, 16).Value = Me.Yd11.Value
ws2.Cells(7, "N").Value = Me.Yd11.Value
ws.Cells(iRow, 17).Value = Me.Yd12.Value
ws2.Cells(7, "O").Value = Me.Yd12.Value
ws.Cells(iRow, 18).Value = Me.Yd13.Value
ws2.Cells(7, "P").Value = Me.Yd13.Value
ws.Cells(iRow, 19).Value = Me.Yd14.Value
ws2.Cells(7, "Q").Value = Me.Yd14.Value
ws.Cells(iRow, 20).Value = Me.Yd15.Value
ws2.Cells(7, "R").Value = Me.Yd15.Value
ws.Cells(iRow, 21).Value = Me.Yd16.Value
ws2.Cells(7, "S").Value = Me.Yd16.Value
ws.Cells(iRow, 22).Value = Me.Yd17.Value
ws2.Cells(7, "T").Value = Me.Yd17.Value
ws.Cells(iRow, 23).Value = Me.yd18.Value
ws2.Cells(7, "U").Value = Me.yd18.Value

'copy the NEW DATA Par to databases
ws.Cells(iRow, 24).Value = Me.Pr1.Value
ws2.Cells(8, "C").Value = Me.Pr1.Value
ws.Cells(iRow, 25).Value = Me.Pr2.Value
ws2.Cells(8, "D").Value = Me.Pr2.Value
ws.Cells(iRow, 26).Value = Me.Pr3.Value
ws2.Cells(8, "E").Value = Me.Pr3.Value
ws.Cells(iRow, 27).Value = Me.Pr4.Value
ws2.Cells(8, "F").Value = Me.Pr4.Value
ws.Cells(iRow, 28).Value = Me.Pr5.Value
ws2.Cells(8, "G").Value = Me.Pr5.Value
ws.Cells(iRow, 29).Value = Me.Pr6.Value
ws2.Cells(8, "H").Value = Me.Pr6.Value
ws.Cells(iRow, 30).Value = Me.Pr7.Value
ws2.Cells(8, "I").Value = Me.Pr7.Value
ws.Cells(iRow, 31).Value = Me.Pr8.Value
ws2.Cells(8, "J").Value = Me.Pr8.Value
ws.Cells(iRow, 32).Value = Me.Pr9.Value
ws2.Cells(8, "K").Value = Me.Pr9.Value
ws.Cells(iRow, 33).Value = Me.Pr10.Value
ws2.Cells(8, "M").Value = Me.Pr10.Value
ws.Cells(iRow, 34).Value = Me.Pr11.Value
ws2.Cells(8, "N").Value = Me.Pr11.Value
ws.Cells(iRow, 35).Value = Me.Pr12.Value
ws2.Cells(8, "O").Value = Me.Pr12.Value
ws.Cells(iRow, 36).Value = Me.Pr13.Value
ws2.Cells(8, "P").Value = Me.Pr13.Value
ws.Cells(iRow, 37).Value = Me.Pr14.Value
ws2.Cells(8, "Q").Value = Me.Pr14.Value
ws.Cells(iRow, 38).Value = Me.Pr15.Value
ws2.Cells(8, "R").Value = Me.Pr15.Value
ws.Cells(iRow, 39).Value = Me.Pr16.Value
ws2.Cells(8, "S").Value = Me.Pr16.Value
ws.Cells(iRow, 40).Value = Me.Pr17.Value
ws2.Cells(8, "T").Value = Me.Pr17.Value
ws.Cells(iRow, 41).Value = Me.Pr18.Value
ws2.Cells(8, "U").Value = Me.Pr18.Value

'START box buttons for FAIRWAYS HIT H E R E
'copy the NEW DATA FAIRWAYS HIT to database
If FH1 = True Then
ws.Cells(iRow, 42).Value = Me.FH1.Value
ws2.Cells(9, "C").Value = Me.FH1.Value
End If
If FH2 = True Then
ws.Cells(iRow, 43).Value = Me.FH2.Value
ws2.Cells(9, "D").Value = Me.FH2.Value
End If
If FH3 = True Then
ws.Cells(iRow, 44).Value = Me.FH3.Value
ws2.Cells(9, "E").Value = Me.FH3.Value
End If
If FH4 = True Then
ws.Cells(iRow, 45).Value = Me.FH4.Value
ws2.Cells(9, "F").Value = Me.FH4.Value
End If
If FH5 = True Then
ws.Cells(iRow, 46).Value = Me.FH5.Value
ws2.Cells(9, "G").Value = Me.FH5.Value
End If
If FH6 = True Then
ws.Cells(iRow, 47).Value = Me.FH6.Value
ws2.Cells(9, "H").Value = Me.FH6.Value
End If
If FH7 = True Then
ws.Cells(iRow, 48).Value = Me.FH7.Value
ws2.Cells(9, "I").Value = Me.FH7.Value
End If
If FH8 = True Then
ws.Cells(iRow, 49).Value = Me.FH8.Value
ws2.Cells(9, "J").Value = Me.FH8.Value
End If
If FH9 = True Then
ws.Cells(iRow, 50).Value = Me.FH9.Value
ws2.Cells(9, "K").Value = Me.FH9.Value
End If
If FH10 = True Then
ws.Cells(iRow, 51).Value = Me.FH10.Value
ws2.Cells(9, "M").Value = Me.FH10.Value
End If
If FH11 = True Then
ws.Cells(iRow, 52).Value = Me.FH11.Value
ws2.Cells(9, "N").Value = Me.FH1.Value
End If
If FH12 = True Then
ws.Cells(iRow, 53).Value = Me.FH12.Value
ws2.Cells(9, "O").Value = Me.FH12.Value
End If
If FH13 = True Then
ws.Cells(iRow, 54).Value = Me.FH13.Value
ws2.Cells(9, "P").Value = Me.FH13.Value
End If
If FH14 = True Then
ws.Cells(iRow, 55).Value = Me.FH14.Value
ws2.Cells(9, "Q").Value = Me.FH14.Value
End If
If FH15 = True Then
ws.Cells(iRow, 56).Value = Me.FH15.Value
ws2.Cells(9, "R").Value = Me.FH15.Value
End If
If FH16 = True Then
ws.Cells(iRow, 57).Value = Me.FH16.Value
ws2.Cells(9, "S").Value = Me.FH16.Value
End If
If FH17 = True Then
ws.Cells(iRow, 58).Value = Me.FH17.Value
ws2.Cells(9, "T").Value = Me.FH17.Value
End If
If FH18 = True Then
ws.Cells(iRow, 59).Value = Me.FH18.Value
ws2.Cells(9, "U").Value = Me.FH18.Value
End If

'START box buttons for MISSED RIGHT H E R E
'copy the NEW DATA MISSED RIGHT to database
If MR1 = True Then
ws.Cells(iRow, 60).Value = Me.MR1.Value
ws2.Cells(11, "C").Value = Me.MR1.Value
End If
If MR2 = True Then
ws.Cells(iRow, 61).Value = Me.MR2.Value
ws2.Cells(11, "D").Value = Me.MR2.Value
End If
If MR3 = True Then
ws.Cells(iRow, 62).Value = Me.MR3.Value
ws2.Cells(11, "E").Value = Me.MR3.Value
End If
If MR4 = True Then
ws.Cells(iRow, 63).Value = Me.MR4.Value
ws2.Cells(11, "F").Value = Me.MR4.Value
End If
If MR5 = True Then
ws.Cells(iRow, 64).Value = Me.MR5.Value
ws2.Cells(11, "G").Value = Me.MR5.Value
End If
If MR6 = True Then
ws.Cells(iRow, 65).Value = Me.MR6.Value
ws2.Cells(11, "H").Value = Me.MR6.Value
End If
If MR7 = True Then
ws.Cells(iRow, 66).Value = Me.MR7.Value
ws2.Cells(11, "I").Value = Me.MR7.Value
End If
If MR8 = True Then
ws.Cells(iRow, 67).Value = Me.MR8.Value
ws2.Cells(11, "J").Value = Me.MR8.Value
End If
If MR9 = True Then
ws.Cells(iRow, 68).Value = Me.MR9.Value
ws2.Cells(11, "K").Value = Me.MR9.Value
End If
If MR10 = True Then
ws.Cells(iRow, 69).Value = Me.MR10.Value
ws2.Cells(11, "M").Value = Me.MR10.Value
End If
If MR11 = True Then
ws.Cells(iRow, 70).Value = Me.MR11.Value
ws2.Cells(11, "N").Value = Me.MR1.Value
End If
If MR12 = True Then
ws.Cells(iRow, 71).Value = Me.MR12.Value
ws2.Cells(11, "O").Value = Me.MR12.Value
End If
If MR13 = True Then
ws.Cells(iRow, 72).Value = Me.MR13.Value
ws2.Cells(11, "P").Value = Me.MR13.Value
End If
If MR14 = True Then
ws.Cells(iRow, 73).Value = Me.MR14.Value
ws2.Cells(11, "Q").Value = Me.MR14.Value
End If
If MR15 = True Then
ws.Cells(iRow, 74).Value = Me.MR15.Value
ws2.Cells(11, "R").Value = Me.MR15.Value
End If
If MR16 = True Then
ws.Cells(iRow, 75).Value = Me.MR16.Value
ws2.Cells(11, "S").Value = Me.MR16.Value
End If
If MR17 = True Then
ws.Cells(iRow, 76).Value = Me.MR17.Value
ws2.Cells(11, "T").Value = Me.MR17.Value
End If
If MR18 = True Then
ws.Cells(iRow, 77).Value = Me.MR18.Value
ws2.Cells(11, "U").Value = Me.MR18.Value
End If

'START box buttons for MISSED LEFT H E R E
'copy the NEW DATA MISSED LEFT to database
If ML1 = True Then
ws.Cells(iRow, 78).Value = Me.ML1.Value
ws2.Cells(10, "C").Value = Me.ML1.Value
End If
If ML2 = True Then
ws.Cells(iRow, 79).Value = Me.ML2.Value
ws2.Cells(10, "D").Value = Me.ML2.Value
End If
If ML3 = True Then
ws.Cells(iRow, 80).Value = Me.ML3.Value
ws2.Cells(10, "E").Value = Me.ML3.Value
End If
If ML4 = True Then
ws.Cells(iRow, 81).Value = Me.ML4.Value
ws2.Cells(10, "F").Value = Me.ML4.Value
End If
If ML5 = True Then
ws.Cells(iRow, 82).Value = Me.ML5.Value
ws2.Cells(10, "G").Value = Me.ML5.Value
End If
If ML6 = True Then
ws.Cells(iRow, 83).Value = Me.ML6.Value
ws2.Cells(10, "H").Value = Me.ML6.Value
End If
If ML7 = True Then
ws.Cells(iRow, 84).Value = Me.ML7.Value
ws2.Cells(10, "I").Value = Me.ML7.Value
End If
If ML8 = True Then
ws.Cells(iRow, 85).Value = Me.ML8.Value
ws2.Cells(10, "J").Value = Me.ML8.Value
End If
If ML9 = True Then
ws.Cells(iRow, 86).Value = Me.ML9.Value
ws2.Cells(10, "K").Value = Me.ML9.Value
End If
If ML10 = True Then
ws.Cells(iRow, 87).Value = Me.ML10.Value
ws2.Cells(10, "M").Value = Me.ML10.Value
End If
If ML11 = True Then
ws.Cells(iRow, 88).Value = Me.ML11.Value
ws2.Cells(10, "N").Value = Me.ML11.Value
End If
If ML12 = True Then
ws.Cells(iRow, 89).Value = Me.ML12.Value
ws2.Cells(10, "O").Value = Me.ML12.Value
End If
If ML13 = True Then
ws.Cells(iRow, 90).Value = Me.ML13.Value
ws2.Cells(10, "P").Value = Me.ML13.Value
End If
If ML14 = True Then
ws.Cells(iRow, 91).Value = Me.ML14.Value
ws2.Cells(10, "Q").Value = Me.ML14.Value
End If
If ML15 = True Then
ws.Cells(iRow, 92).Value = Me.ML15.Value
ws2.Cells(10, "R").Value = Me.ML15.Value
End If
If ML16 = True Then
ws.Cells(iRow, 93).Value = Me.ML16.Value
ws2.Cells(10, "S").Value = Me.ML16.Value
End If
If ML17 = True Then
ws.Cells(iRow, 94).Value = Me.ML17.Value
ws2.Cells(10, "T").Value = Me.ML17.Value
End If
If ML18 = True Then
ws.Cells(iRow, 95).Value = Me.ML18.Value
ws2.Cells(10, "U").Value = Me.ML18.Value
End If

'copy the NEW DATA Distance Left to databases
ws.Cells(iRow, 96).Value = Me.DL1.Value
ws2.Cells(12, "C").Value = Me.DL1.Value
ws.Cells(iRow, 97).Value = Me.DL2.Value
ws2.Cells(12, "D").Value = Me.DL2.Value
ws.Cells(iRow, 98).Value = Me.DL3.Value
ws2.Cells(12, "E").Value = Me.DL3.Value
ws.Cells(iRow, 99).Value = Me.DL4.Value
ws2.Cells(12, "F").Value = Me.DL4.Value
ws.Cells(iRow, 100).Value = Me.DL5.Value
ws2.Cells(12, "G").Value = Me.DL5.Value
ws.Cells(iRow, 101).Value = Me.DL6.Value
ws2.Cells(12, "H").Value = Me.DL6.Value
ws.Cells(iRow, 102).Value = Me.DL7.Value
ws2.Cells(12, "I").Value = Me.DL7.Value
ws.Cells(iRow, 103).Value = Me.DL8.Value
ws2.Cells(12, "J").Value = Me.DL8.Value
ws.Cells(iRow, 104).Value = Me.DL9.Value
ws2.Cells(12, "K").Value = Me.DL9.Value
ws.Cells(iRow, 105).Value = Me.DL10.Value
ws2.Cells(12, "M").Value = Me.DL10.Value
ws.Cells(iRow, 106).Value = Me.DL11.Value
ws2.Cells(12, "N").Value = Me.DL11.Value
ws.Cells(iRow, 107).Value = Me.DL12.Value
ws2.Cells(12, "O").Value = Me.DL12.Value
ws.Cells(iRow, 108).Value = Me.DL13.Value
ws2.Cells(12, "P").Value = Me.DL13.Value
ws.Cells(iRow, 109).Value = Me.DL14.Value
ws2.Cells(12, "Q").Value = Me.DL14.Value
ws.Cells(iRow, 110).Value = Me.DL15.Value
ws2.Cells(12, "R").Value = Me.DL15.Value
ws.Cells(iRow, 111).Value = Me.DL16.Value
ws2.Cells(12, "S").Value = Me.DL16.Value
ws.Cells(iRow, 112).Value = Me.DL17.Value
ws2.Cells(12, "T").Value = Me.DL17.Value
ws.Cells(iRow, 113).Value = Me.DL18.Value
ws2.Cells(12, "U").Value = Me.DL18.Value

'START box buttons for Greens in Regulation Left H E R E
'copy the NEW DATA Missed Left to database
If GH1 = True Then
ws.Cells(iRow, 114).Value = Me.GH1.Value
ws2.Cells(13, "C").Value = Me.GH1.Value
End If
If GH2 = True Then
ws.Cells(iRow, 115).Value = Me.GH2.Value
ws2.Cells(13, "D").Value = Me.GH2.Value
End If
If GH3 = True Then
ws.Cells(iRow, 116).Value = Me.GH3.Value
ws2.Cells(13, "E").Value = Me.GH3.Value
End If
If GH4 = True Then
ws.Cells(iRow, 117).Value = Me.GH4.Value
ws2.Cells(13, "F").Value = Me.GH4.Value
End If
If GH5 = True Then
ws.Cells(iRow, 118).Value = Me.GH5.Value
ws2.Cells(13, "G").Value = Me.GH5.Value
End If
If GH6 = True Then
ws.Cells(iRow, 119).Value = Me.GH6.Value
ws2.Cells(13, "H").Value = Me.GH6.Value
End If
If GH7 = True Then
ws.Cells(iRow, 120).Value = Me.GH7.Value
ws2.Cells(13, "I").Value = Me.GH7.Value
End If
If GH8 = True Then
ws.Cells(iRow, 121).Value = Me.GH8.Value
ws2.Cells(13, "J").Value = Me.GH8.Value
End If
If GH9 = True Then
ws.Cells(iRow, 122).Value = Me.GH9.Value
ws2.Cells(13, "K").Value = Me.GH9.Value
End If
If GH10 = True Then
ws.Cells(iRow, 123).Value = Me.GH10.Value
ws2.Cells(13, "M").Value = Me.GH10.Value
End If
If GH11 = True Then
ws.Cells(iRow, 124).Value = Me.GH11.Value
ws2.Cells(13, "N").Value = Me.GH11.Value
End If
If GH12 = True Then
ws.Cells(iRow, 125).Value = Me.GH12.Value
ws2.Cells(13, "O").Value = Me.GH12.Value
End If
If GH13 = True Then
ws.Cells(iRow, 126).Value = Me.GH13.Value
ws2.Cells(13, "P").Value = Me.GH13.Value
End If
If GH14 = True Then
ws.Cells(iRow, 127).Value = Me.GH14.Value
ws2.Cells(13, "Q").Value = Me.GH14.Value
End If
If GH15 = True Then
ws.Cells(iRow, 128).Value = Me.GH15.Value
ws2.Cells(13, "R").Value = Me.GH15.Value
End If
If GH16 = True Then
ws.Cells(iRow, 129).Value = Me.GH16.Value
ws2.Cells(13, "S").Value = Me.GH16.Value
End If
If GH17 = True Then
ws.Cells(iRow, 130).Value = Me.GH17.Value
ws2.Cells(13, "T").Value = Me.GH17.Value
End If
If GH18 = True Then
ws.Cells(iRow, 131).Value = Me.GH18.Value
ws2.Cells(13, "U").Value = Me.GH18.Value
End If

'copy the NEW DATA Putting Distance to databases
ws.Cells(iRow, 132).Value = Me.PTD1.Value
ws2.Cells(14, "C").Value = Me.PTD1.Value
ws.Cells(iRow, 133).Value = Me.PTD2.Value
ws2.Cells(14, "D").Value = Me.PTD2.Value
ws.Cells(iRow, 134).Value = Me.PTD3.Value
ws2.Cells(14, "E").Value = Me.PTD3.Value
ws.Cells(iRow, 135).Value = Me.PTD4.Value
ws2.Cells(14, "F").Value = Me.PTD4.Value
ws.Cells(iRow, 136).Value = Me.PTD5.Value
ws2.Cells(14, "G").Value = Me.PTD5.Value
ws.Cells(iRow, 137).Value = Me.PTD6.Value
ws2.Cells(14, "H").Value = Me.PTD6.Value
ws.Cells(iRow, 138).Value = Me.PTD7.Value
ws2.Cells(14, "I").Value = Me.PTD7.Value
ws.Cells(iRow, 139).Value = Me.PTD8.Value
ws2.Cells(14, "J").Value = Me.PTD8.Value
ws.Cells(iRow, 140).Value = Me.PTD9.Value
ws2.Cells(14, "K").Value = Me.PTD9.Value
ws.Cells(iRow, 141).Value = Me.PTD10.Value
ws2.Cells(14, "M").Value = Me.PTD10.Value
ws.Cells(iRow, 142).Value = Me.PTD11.Value
ws2.Cells(14, "N").Value = Me.PTD11.Value
ws.Cells(iRow, 143).Value = Me.PTD12.Value
ws2.Cells(14, "O").Value = Me.PTD12.Value
ws.Cells(iRow, 144).Value = Me.PTD13.Value
ws2.Cells(14, "P").Value = Me.PTD13.Value
ws.Cells(iRow, 145).Value = Me.PTD14.Value
ws2.Cells(14, "Q").Value = Me.PTD14.Value
ws.Cells(iRow, 146).Value = Me.PTd15.Value
ws2.Cells(14, "R").Value = Me.PTd15.Value
ws.Cells(iRow, 147).Value = Me.PTD16.Value
ws2.Cells(14, "S").Value = Me.PTD16.Value
ws.Cells(iRow, 148).Value = Me.PTD17.Value
ws2.Cells(14, "T").Value = Me.PTD17.Value
ws.Cells(iRow, 149).Value = Me.PTD18.Value
ws2.Cells(14, "U").Value = Me.PTD18.Value
'copy the NEW DATA Number of Putts to databases
ws.Cells(iRow, 150).Value = Me.PT1.Value
ws2.Cells(15, "C").Value = Me.PT1.Value
ws.Cells(iRow, 151).Value = Me.PT2.Value
ws2.Cells(15, "D").Value = Me.PT2.Value
ws.Cells(iRow, 152).Value = Me.PT3.Value
ws2.Cells(15, "E").Value = Me.PT3.Value
ws.Cells(iRow, 153).Value = Me.PT4.Value
ws2.Cells(15, "F").Value = Me.PT4.Value
ws.Cells(iRow, 154).Value = Me.PT5.Value
ws2.Cells(15, "G").Value = Me.PT5.Value
ws.Cells(iRow, 155).Value = Me.PT6.Value
ws2.Cells(15, "H").Value = Me.PT6.Value
ws.Cells(iRow, 156).Value = Me.PT7.Value
ws2.Cells(15, "I").Value = Me.PT7.Value
ws.Cells(iRow, 157).Value = Me.PT8.Value
ws2.Cells(15, "J").Value = Me.PT8.Value
ws.Cells(iRow, 158).Value = Me.PT9.Value
ws2.Cells(15, "K").Value = Me.PT9.Value
ws.Cells(iRow, 159).Value = Me.PT10.Value
ws2.Cells(15, "M").Value = Me.PT10.Value
ws.Cells(iRow, 160).Value = Me.PT11.Value
ws2.Cells(15, "N").Value = Me.PT11.Value
ws.Cells(iRow, 161).Value = Me.PT12.Value
ws2.Cells(15, "O").Value = Me.PT12.Value
ws.Cells(iRow, 162).Value = Me.PT13.Value
ws2.Cells(15, "P").Value = Me.PT13.Value
ws.Cells(iRow, 163).Value = Me.PT14.Value
ws2.Cells(15, "Q").Value = Me.PT14.Value
ws.Cells(iRow, 164).Value = Me.PT15.Value
ws2.Cells(15, "R").Value = Me.PT15.Value
ws.Cells(iRow, 165).Value = Me.PT16.Value
ws2.Cells(15, "S").Value = Me.PT16.Value
ws.Cells(iRow, 166).Value = Me.PT17.Value
ws2.Cells(15, "T").Value = Me.PT17.Value
ws.Cells(iRow, 167).Value = Me.PT18.Value
ws2.Cells(15, "U").Value = Me.PT18.Value
'copy the NEW DATA Score to databases
ws.Cells(iRow, 168).Value = Me.SC1.Value
ws2.Cells(16, "C").Value = Me.SC1.Value
ws.Cells(iRow, 169).Value = Me.SC2.Value
ws2.Cells(16, "D").Value = Me.SC2.Value
ws.Cells(iRow, 170).Value = Me.SC3.Value
ws2.Cells(16, "E").Value = Me.SC3.Value
ws.Cells(iRow, 171).Value = Me.SC4.Value
ws2.Cells(16, "F").Value = Me.SC4.Value
ws.Cells(iRow, 172).Value = Me.SC5.Value
ws2.Cells(16, "G").Value = Me.SC5.Value
ws.Cells(iRow, 173).Value = Me.SC6.Value
ws2.Cells(16, "H").Value = Me.SC6.Value
ws.Cells(iRow, 174).Value = Me.SC7.Value
ws2.Cells(16, "I").Value = Me.SC7.Value
ws.Cells(iRow, 175).Value = Me.SC8.Value
ws2.Cells(16, "J").Value = Me.SC8.Value
ws.Cells(iRow, 176).Value = Me.SC9.Value
ws2.Cells(16, "K").Value = Me.SC9.Value
ws.Cells(iRow, 177).Value = Me.SC10.Value
ws2.Cells(16, "M").Value = Me.SC10.Value
ws.Cells(iRow, 178).Value = Me.SC11.Value
ws2.Cells(16, "N").Value = Me.SC11.Value
ws.Cells(iRow, 179).Value = Me.SC12.Value
ws2.Cells(16, "O").Value = Me.SC12.Value
ws.Cells(iRow, 180).Value = Me.SC13.Value
ws2.Cells(16, "P").Value = Me.SC13.Value
ws.Cells(iRow, 181).Value = Me.SC14.Value
ws2.Cells(16, "Q").Value = Me.SC14.Value
ws.Cells(iRow, 182).Value = Me.SC15.Value
ws2.Cells(16, "R").Value = Me.SC15.Value
ws.Cells(iRow, 183).Value = Me.SC16.Value
ws2.Cells(16, "S").Value = Me.SC16.Value
ws.Cells(iRow, 184).Value = Me.SC17.Value
ws2.Cells(16, "T").Value = Me.SC17.Value
ws.Cells(iRow, 185).Value = Me.SC18.Value
ws2.Cells(16, "U").Value = Me.SC18.Value

'D E L E T E T H E F O R M STARTS HERE
'clear the ComboTees
'Me.ComboTees.Value = ""
Me.cboCourse.Value = ""

'clear the last name cboLocation & first name cbopart
Me.cboLocation.Value = ""
Me.cbopart.Value = ""

'clear the data
Me.txtRating.Value = ""
Me.txtSlope.Value = ""
Me.txtPar.Value = ""

'clear the NEW DATA Yardage
Me.Yd1.Value = ""
Me.Yd2.Value = ""
Me.Yd3.Value = ""
Me.Yd4.Value = ""
Me.Yd5.Value = ""
Me.Yd6.Value = ""
Me.Yd7.Value = ""
Me.Yd8.Value = ""
Me.Yd9.Value = ""
Me.Yd10.Value = ""
Me.Yd11.Value = ""
Me.Yd12.Value = ""
Me.Yd13.Value = ""
Me.Yd14.Value = ""
Me.Yd15.Value = ""
Me.Yd16.Value = ""
Me.Yd17.Value = ""
Me.yd18.Value = ""

'clear the NEW DATA Par
Me.Pr1.Value = ""
Me.Pr2.Value = ""
Me.Pr3.Value = ""
Me.Pr4.Value = ""
Me.Pr5.Value = ""
Me.Pr6.Value = ""
Me.Pr7.Value = ""
Me.Pr8.Value = ""
Me.Pr9.Value = ""
Me.Pr10.Value = ""
Me.Pr11.Value = ""
Me.Pr12.Value = ""
Me.Pr13.Value = ""
Me.Pr14.Value = ""
Me.Pr15.Value = ""
Me.Pr16.Value = ""
Me.Pr17.Value = ""
Me.Pr18.Value = ""

'clear the NEW DATA Fairway Hit
Me.FH1.Value = ""
Me.FH2.Value = ""
Me.FH3.Value = ""
Me.FH4.Value = ""
Me.FH5.Value = ""
Me.FH6.Value = ""
Me.FH7.Value = ""
Me.FH8.Value = ""
Me.FH9.Value = ""
Me.FH10.Value = ""
Me.FH11.Value = ""
Me.FH12.Value = ""
Me.FH13.Value = ""
Me.FH14.Value = ""
Me.FH15.Value = ""
Me.FH16.Value = ""
Me.FH17.Value = ""
Me.FH18.Value = ""

'clear the NEW DATA Missed Right
'Me.MR1.Value = ""
Me.MR1.Value = ""
Me.MR2.Value = ""
Me.MR3.Value = ""
Me.MR4.Value = ""
Me.MR5.Value = ""
Me.MR6.Value = ""
Me.MR7.Value = ""
Me.MR8.Value = ""
Me.MR9.Value = ""
Me.MR10.Value = ""
Me.MR11.Value = ""
Me.MR12.Value = ""
Me.MR13.Value = ""
Me.MR14.Value = ""
Me.MR15.Value = ""
Me.MR16.Value = ""
Me.MR17.Value = ""
Me.MR18.Value = ""

'clear the NEW DATA Missed Left
'Me.ML1.Value = ""
Me.ML1.Value = ""
Me.ML2.Value = ""
Me.ML3.Value = ""
Me.ML4.Value = ""
Me.ML5.Value = ""
Me.ML6.Value = ""
Me.ML7.Value = ""
Me.ML8.Value = ""
Me.ML9.Value = ""
Me.ML10.Value = ""
Me.ML11.Value = ""
Me.ML12.Value = ""
Me.ML13.Value = ""
Me.ML14.Value = ""
Me.ML15.Value = ""
Me.ML16.Value = ""
Me.ML17.Value = ""
Me.ML18.Value = ""

'clear the NEW DATA Distance Left
Me.DL1.Value = ""
Me.DL2.Value = ""
Me.DL3.Value = ""
Me.DL4.Value = ""
Me.DL5.Value = ""
Me.DL6.Value = ""
Me.DL7.Value = ""
Me.DL8.Value = ""
Me.DL9.Value = ""
Me.DL10.Value = ""
Me.DL11.Value = ""
Me.DL12.Value = ""
Me.DL13.Value = ""
Me.DL14.Value = ""
Me.DL15.Value = ""
Me.DL16.Value = ""
Me.DL17.Value = ""
Me.DL18.Value = ""

'START Greens in Regulation Left H E R E
'clear the NEW DATA Greens in Regulation
Me.GH1.Value = ""
Me.GH2.Value = ""
Me.GH3.Value = ""
Me.GH4.Value = ""
Me.GH5.Value = ""
Me.GH6.Value = ""
Me.GH7.Value = ""
Me.GH8.Value = ""
Me.GH9.Value = ""
Me.GH10.Value = ""
Me.GH11.Value = ""
Me.GH12.Value = ""
Me.GH13.Value = ""
Me.GH14.Value = ""
Me.GH15.Value = ""
Me.GH16.Value = ""
Me.GH17.Value = ""
Me.GH18.Value = ""

'clear the NEW DATA Putting Distance
Me.PTD1.Value = ""
Me.PTD2.Value = ""
Me.PTD3.Value = ""
Me.PTD4.Value = ""
Me.PTD5.Value = ""
Me.PTD6.Value = ""
Me.PTD7.Value = ""
Me.PTD8.Value = ""
Me.PTD9.Value = ""
Me.PTD10.Value = ""
Me.PTD11.Value = ""
Me.PTD12.Value = ""
Me.PTD13.Value = ""
Me.PTD14.Value = ""
Me.PTd15.Value = ""
Me.PTD16.Value = ""
Me.PTD17.Value = ""
Me.PTD18.Value = ""

'clear the NEW DATA Number of Putts
Me.PT1.Value = ""
Me.PT2.Value = ""
Me.PT3.Value = ""
Me.PT4.Value = ""
Me.PT5.Value = ""
Me.PT6.Value = ""
Me.PT7.Value = ""
Me.PT8.Value = ""
Me.PT9.Value = ""
Me.PT10.Value = ""
Me.PT11.Value = ""
Me.PT12.Value = ""
Me.PT13.Value = ""
Me.PT14.Value = ""
Me.PT15.Value = ""
Me.PT16.Value = ""
Me.PT17.Value = ""
Me.PT18.Value = ""

'clear the NEW DATA Score
Me.SC1.Value = ""
Me.SC2.Value = ""
Me.SC3.Value = ""
Me.SC4.Value = ""
Me.SC5.Value = ""
Me.SC6.Value = ""
Me.SC7.Value = ""
Me.SC8.Value = ""
Me.SC9.Value = ""
Me.SC10.Value = ""
Me.SC11.Value = ""
Me.SC12.Value = ""
Me.SC13.Value = ""
Me.SC14.Value = ""
Me.SC15.Value = ""
Me.SC16.Value = ""
Me.SC17.Value = ""
Me.SC18.Value = ""

End Sub
---------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("GolferDB")
For Each cLoc In ws.Range("LastName")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc

For Each cPart In ws.Range("FirstName")
With Me.cbopart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
'adds date automatically
Me.txtBscore.Value = Format(Date, "Medium Date")
Me.cbopart.SetFocus

End Sub

</vba>

Thanks so much

HDfatboy03