creating a chart from a userform

[shakey775]

I have a dynamic table on a worksheet that shows in

col A name of player
col B Date
Col C Course
Col D Course Par
Cols E-V individual hole scores

what I am wanting to do is create a userform which uses drop down boxes to select the player and the course an option button to select a hole and when command button is clicked produces a chart showing date and scores for selected hole. This could be in another worksheet or the origional userform or a new userform. Would this be possible?
james

[Palmetto]

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

[shakey775]

So thinking outload and for the benefit of anyone else looking for a similar solution
I would need to create a dynamic table which cleared itself and repopulated with new data from a userform and then showed the chart in a worksheet or new userform that popped up on clicking a "create Chart" button

[shakey775]

Thanks this is Great

I wondered if your code could be adapted to work in a userform
You have shown me that check boxes are not needed for the holes unless multiple hole analysis is required so a combobox is all that is needed
thanks again for your help
James

[Palmetto]

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.

[shakey775]

Ok

I have started to put your work into my master work book and have staight away come up against a problem

I am going to use your method of bringing up a chart but cannot for the life in me work out how you populated the "hole" dropdown box

I have attached workbook for you to look at

can you point me in a general direction

I have populated the name and course boxes using the source in the properties
have I completely missed or am i a little bit on target
James

P.S What does IMO mean
Jamesgolf handicapper v1.1.2.2 forum copy.xlsm

[ianh]

imo= in my opinion

[shakey775]

Thanks ian
Couldn't work it out at all

[Palmetto]

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.

[Palmetto]

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

[shakey775]

Fan Tast Ic !!!!!

Thank you This will help even more that the first solution and I have a 9 hour flight to figure it all out on friday

Brill thanks Palmetto