Too Many checkboxes in form are bogging down the routine - How to make faster?

[Dman333]

I have a userform with checkboxes that push a yes or no into another sheet that drives the model. The problem is the code is bogging down and taking too long to refresh. Does anyone have an easier way to loop through all the checkboxes on the userform and push the yes / no into the cells? Below is an example of what I'm doing:


Private Sub UpdateTable()


Dim DBoard As Worksheet
Dim CBoxRow As Long
Dim TrackerOff As String




Set DBoard = ThisWorkbook.Sheets("Woodside Dashboard")
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False



If CboxRow4.Value = True Then DBoard.Cells(4, 4) = "Yes" Else DBoard.Cells(4, 4) = "No"
If CboxRow5.Value = True Then DBoard.Cells(5, 4) = "Yes" Else DBoard.Cells(5, 4) = "No"
If CboxRow6.Value = True Then DBoard.Cells(6, 4) = "Yes" Else DBoard.Cells(6, 4) = "No"
If CboxRow7.Value = True Then DBoard.Cells(7, 4) = "Yes" Else DBoard.Cells(7, 4) = "No"
If CboxRow8.Value = True Then DBoard.Cells(8, 4) = "Yes" Else DBoard.Cells(8, 4) = "No"


I do this around 60 times - I have another worksheet module that tracks the sheet changes which is why there is an Application.EnableEvents line. I'm using 2016.

Anyone ideas out there. Anything is greatly appreciated

 

[My Aswer Is This]

I did this part for you.
You should see the ideal

My loop goes from 4 to 9
Yours would go from 4 to 64 or what ever.

Private Sub CommandButton3_Click()
'Modified  4/15/2019  10:04:05 PM  EDT
Dim i As Long
For i = 4 To 9
    Select Case Me.Controls("CboxRow" & i).Value
        Case True
            Sheets("Woodside Dashboard").Cells(i, 4).Value = "Yes"
        Case False
            Sheets("Woodside Dashboard").Cells(i, 4).Value = "No"
    End Select
Next
End Sub

 

[My Aswer Is This]

Not really sure why you decided to rename all your CheckBoxes
That was a lot of work renaming 60 CheckBoxes

But it works your way but would also have worked with Checkbox default names
Unless you have other CheckBoxes you wanted to not be included.

 

[Dman333]

Thanks a ton. I'll give that a shot and that should help.

After digging further, I found my biggest problem was in a worksheet module that I'm using to track user changes to budgets. I'm really new to this stuff and trying to convert my worksheet knowledge to vba. Currently, I'm using too many sumifs commands in the sheet so I can grab an aggregated budget of multiple line items. I think it's all those calc's that are bogging it down. I'm trying to do a sumifs in a sheetselectionchange module so I only have to calc it once. any ideas on why this is returning a zero for a value? its the cOldCodeBudget (at the top of the module its defined with a Dim statement as a variant unter Option Explicit.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim vOldTotal As Variant
Dim ws As Worksheet
Set ws = ActiveSheet


If ActiveSheet.Name = "Dashboard" Then Exit Sub ' This was put in to avoid userform loop


With Target
sOldAddress = .Address(external:=True)

If ActiveSheet.Name <> "Contingency" And Target.Row > 73 Then

If .Count > 1 Then
vOldValue = "Multiple Cells"
sOldFormula = vbNullString
vOldBudget = ActiveSheet.Cells(Target.Row, 14).Value
vOldCodeBudget = ws.Application.WorksheetFunction.SumIfs(ws.Range("N17:N9000"), ws.Range("B17:B9000"), ws.Range("B22"), ws.Range("D17:D9000"), ws.Range("D22"))
'vOldCodeBudget = ActiveSheet.Cells(Target.Row, 26).Value

 

[My Aswer Is This]

Well your second question seems to have nothing to do with you first question which I helped you with.
You seemed to gloss over my answer.

So you need to explain in words what your attempting to do with this sheet change event script.

And it looks like the way you have some of it working is if any cell on the sheet is changed this script will run which is surely something that will bog things down.

A sheet change again script is normally restricted to if changes are made to one cell or a Range of cells and not to all the cells in your sheet.

Like if a manual change is made to any cell in column A

And I'm not sure what your referring to when you say this line of code is used to stop Userform form looping

And like I have no ideal what this means:

sOldAddress = .Address(external:=True)
​