Excel project has me at a loss

[JRT2006]

I'm creating a fantasy drafting tool to use in fantasy football. I have it set up how I want it, but I am lost with all things VBA. Currently, I'm just recording Macros on each checkbox click, but I'm pretty sure some VBA code would work a lot better.

Background on the tool, it is a VBD (Value Based Drafting) drafting tool. The way it works is you input your player projections for the season and your leagues scoring, and it totals the fantasy points for that player. The VBD part comes in by taking a baseline number for each position and subtracting each player's total fantasy points from that baseline number. The way to get that baseline number is by counting/guessing/figuring out how many players at each position will be drafted in the first 100 pick selections. For instance, I'm estimating 11 quarterbacks will be selected in the first 100 picks, so the total fantasy points for the 11th quarterback on the list becomes the baseline number. Every quarterback's fantasy points are subtracted from this figure giving you an X-Value, this is done for each position. The 11th QB on my list is Eli Manning, and I project him to get 332.72 points, 332.72 is the baseline for QBs, and Eli Mannings x-value would be zero. The 4th ranked QB on my list is Drew Brees, and I project him to get 381.08 points. 381.08 points minus the baseline set by Eli Manning of 332.72 points gives Brees an x-value of 47.36. When a quarterback is drafted, he is removed from the equation, and every quarterback below him moves up, so the baseline changes to the new 11th ranked quarterback and all x-values will adjust.

I've got an excel sheet representing this but the only way I know how to make it all work is by putting a macro enabled check box for all 500 players, when a box is ticked next to the player it deletes the fantasy point projection for that player and sorts the list by x-value. Very clunky and sloppy and I know there's an easier way to do this I just don't know how or it requires VBA which is out of my league (at least until I get my computer science degree).
Have a look; input is very welcomed. Thank You

Dropbox was the only way I could attach
Dropbox - Fantasy Tool_test.xlsm

 

[mole999]

one thing to clear up your recorded macros ARE VBA Code, if you look at the module window you will see Microsoft Visual Basic as the title

 

[JRT2006]

one thing to clear up your recorded macros ARE VBA Code, if you look at the module window you will see Microsoft Visual Basic as the title

I understand that, which is the problem. VBA Code... I have never created code from scratch, I have only looked at examples and built upon them.

I guess I should also mention that I have posted this question in another forum, Excel & VBA Help

 

[Fluff]

Hia
This is untested as I've only got xl2003.
Both macros go in a normal module.
The first will set all checkboxes on sheet "Overall" to run the macro called Fluff
The second macro replaces all your individual player subs, so you will need to remove them (I'd recommend that, initially, you turn them into comments, rather than delete them)

Option Explicit
Sub SetBox()
'    Contextures
    Dim chkBx As CheckBox
    
    For Each chkBx In Sheets("Overall").CheckBoxes
        chkBx.OnAction = "Fluff"
    Next
    
End Sub

Sub Fluff()

    Dim Sht As Worksheet
    Dim Chk As CheckBox
    Dim Rw As Long
    Dim ShtRw As Long
    Dim FantCol As Long
    Dim XCol As Long
    Dim ShtUsdRws As Long
    Dim Nme As String
    Dim OvUsdRws As Long
    
    OvUsdRws = Sheets("Overall").Range("B" & Rows.Count).End(xlUp).Row
    Set Chk = Sheets("Overall").CheckBoxes(Application.Caller)
    If Chk = 1 Then
        Rw = Chk.TopLeftCell.Row
        Nme = Range("B" & Rw).Value
        
        Set Sht = Sheets(Range("C" & Rw).Value)
        With Sht
            ShtUsdRws = .Range("C" & Rows.Count).End(xlUp).Row
            ShtRw = .Range("C2:C" & ShtUsdRws).Find(Nme).Row
            FantCol = .Rows(1).Find("FantPt").col
            XCol = .Rows(1).Find("X-Factor")
            .Cells(ShtRw, FantCol).ClearContents
            
            .AutoFilter.Sort.SortFields.Clear
            .AutoFilter.Sort.SortFields.Add Key:=.Range( _
                .Cells(1, XCol), .Cells(ShtUsdRws, XCol)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
                xlSortNormal
            With .AutoFilter.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
        With Sheets("Overall")
            .AutoFilter.Sort.SortFields.Clear
            .AutoFilter.Sort.SortFields.Add Key:= _
                Range("G1:G" & OvUsdRws), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
                :=xlSortNormal
            With .AutoFilter.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End If

End Sub

HTH

 

[JRT2006]

Fluff,

Greatly appreciated. Thank you, I really want to study this code and try to learn how everyone's brains tick when creating code from scratch.

 

[Fluff]

Glad to help & if there's anything you don't understand, just shout.