Thread: creating a chart from a userform

You don't need a userform for this.

You can use a worksheet.
See attached.
On Sheet2 cells B1:B3 use different drop down lists to select player, course and hole.

A macro is assigned to a command button. Drop down selections are passed as filter criteria for filtering the data table on Sheet1.
Dates and scores from the matching hole are copied to Sheet2.

Lists were set up on sheet3 and are used in the DV drop down.

On Sheet1, I suggest you use drop down selections for players and courses to avoid issues with typographical errors that can cause the code to fail.

This is basic code, not much error handling beyond testing for blanks in the drop downs.

Option Explicit

Sub Get_Scores()

Dim lastrow As Long, lngCol As Long
Dim sName As String, sCourse As String, sHole As String

Application.ScreenUpdating = False

With Sheet2

    '// set the filter criteria to string variables based on drop down selections
    sName = .Range("B1").Value
    sCourse = Range("B2").Value
    sHole = .Range("B3").Value
    
    '// clear the output cells to receive new data - -- adjust range if required
    .Range("C6:D500").ClearContents
    
End With

'// confirm none of the selections are blank before applying filter

Select Case True
    Case sName = "": MsgBox "Player not selected", vbExclamation: Exit Sub
    Case sCourse = "": MsgBox "Course not selected", vbExclamation: Exit Sub
    Case sHole = "": MsgBox "Hole not selected", vbExclamation: Exit Sub
End Select

'// find last data row and column matching the hole selection then apply filter and copy results to sheet2

With Sheet1
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    lngCol = WorksheetFunction.Match(sHole, .Rows(1), 0)
    .AutoFilterMode = False
    .Range("A1:W" & lastrow).AutoFilter Field:=1, Criteria1:=sName
    .Range("A1:W" & lastrow).AutoFilter Field:=3, Criteria1:=sCourse
    .Range("B1:B" & lastrow - 1).Offset(1, 0).SpecialCells(12).Copy Sheet2.Range("C6")
    .Range(.Cells(1, lngCol), .Cells(lastrow - 1, lngCol)).Offset(1, 0).SpecialCells(12).Copy Sheet2.Range("D6")
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Excel Help _807056.xlsm

A user form is not a requirement and, IMO, not needed. It is simpler to go with populating cells in a worksheet.

However, if you intend to allow multiple holes to be selected then you would need a different approach for hole selection(s). Data validation allows only one selection.

imo= in my opinion

I would use Data Validation drop down list - not the combo box controls. This is what I modeled the code on. It will require significant changes to adapt to using controls.
The drop down list for the holes was set by create a named range referring to the range of cells E1:V1 on sheet1 of the example file.

Since these are fixed values that will not change, you could just type the list in to the list source for the Data Validation.

If you want to include any combination of holes, then try using Marlett check boxes

Modified code below and now includes additional code to use Marlett check boxes in the worksheet selection change event.

Worksheet code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("D3:U3")) Is Nothing Then
        Target.Font.Name = "Marlett"
        If Target = vbNullString Then
            Target = "a"
        Else
            Target = vbNullString
        End If
    End If
    
End Sub

Code modified to use the selections for multiple holes:

Option Explicit
Option Base 1

Sub Get_Scores()

    Dim lastrow As Long, lngCol As Long, chkSum As Long, i As Long
    Dim sName As String, sCourse As String
    
    Application.ScreenUpdating = False
    
    With Sheet2
    
        '// set the filter criteria to string variables based on drop down selections
        sName = .Range("B1").Value
        sCourse = Range("B2").Value
        chkSum = WorksheetFunction.CountA(.Range("D3:U3"))
        
        '// clear the output cells to receive new data - -- adjust range if required
        .Range("C6:U500").ClearContents
        
    End With
    
    '// confirm none of the selections are blank before applying filter and that at least one hole is picked
    
    Select Case True
        Case sName = "": MsgBox "Player not selected", vbExclamation: Exit Sub
        Case sCourse = "": MsgBox "Course not selected", vbExclamation: Exit Sub
        Case chkSum < 1: MsgBox "At least one hole must be selected", vbExclamation: Exit Sub
    End Select
    
    
    '// find last data row and column matching the hole selection then apply filter and copy results to sheet2
    
    With Sheet1
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        .AutoFilterMode = False
        .Range("A1:W" & lastrow).AutoFilter Field:=1, Criteria1:=sName
        .Range("A1:W" & lastrow).AutoFilter Field:=3, Criteria1:=sCourse
        .Range("B1:B" & lastrow - 1).Offset(1, 0).SpecialCells(12).Copy Sheet2.Range("C6")
        
        On Error Resume Next
        For i = 4 To 21
            If Sheet2.Cells(3, i) = "a" Then
                lngCol = Sheet2.Cells(2, i).Value + 4
            End If
            .Range(.Cells(1, lngCol), .Cells(lastrow - 1, lngCol)).Offset(1, 0).SpecialCells(12).Copy Sheet2.Cells(6, lngCol - 1)
        Next i
        
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Also, you may want to clear the selection in one go to start fresh.

Sub reset()
    With Sheet2
        .Range("B1:B2").ClearContents
        .Range("D3:U3").ClearContents
        .Range("C6:U500").ClearContents
    End With
End Sub

Excel Help _807056.xlsm