Slow VBA Sheet performance when ticking numbers off sheet.

[sibley14]

I have a VBA sheet which seems to run really slowly on excel 2016 vs excel 2007.

The sheet contains a list of consecutive usable numbers which can be ticked off as used. When a number is clicked a tick goes into the box and the cell of that number fills green with a red line through the number showing as used.

Until today we had one user accessing the sheet from cloud storage via excel 2007 and one from excel 2016. The 2016 version always ran slow with slight lagging when a box was ticked. however the excel 2007 user found the sheet to be quite slick.

Other than the fact that both users are now on outlook 2016 can anyone suggest a fix to improve the sheet performance?

i'll hold my hands up as a beginner when it comes to VBA/Macros. happy to send the sheet for someone to look at.

Thank you in advance.

 

[Caleeco]

Hello Sibley,

There are many things you can do to speed up VBA execution. Are you able to post the VBA code here? (hit ALT+F11) to get to the VB Editor.

Without seeing the code, I won't be able to say with certainty what the best fix is.

Cheers
Caleeco

 

[sibley14]

Hello Sibley,

There are many things you can do to speed up VBA execution. Are you able to post the VBA code here? (hit ALT+F11) to get to the VB Editor.

Without seeing the code, I won't be able to say with certainty what the best fix is.

Cheers
Caleeco

Hey Caleeco,

sorry for the delayed response. The code is in modules:
Module 1:

Sub ChBox()
'This code Places the Check Boxes in the sheet
Dim cboxLabel As String
Dim linkedColumn As String
Dim cellRange As String
Dim myCell As Range
Dim myBox As Object
Dim rng As Range
'This is the Range with "CheckBoxes"
 Set rng = Range("A2:A49,D2:D49,G2:G49,J2:J49,M2:M49,P2:p49,S2:S49")
With ActiveSheet
    For Each myCell In rng
      With myCell
        Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
          Width:=.Width, Left:=.Left, Height:=.Height)
 
        With myBox
          .LinkedCell = myCell.Offset(, 1).Address '
          .Caption = "CB" & myCell.Row - 1
          .Name = "checkbox_" & myCell.Address(0, 0)
          .OnAction = "Tick"
        End With
    End With
  Next myCell
End With
End Sub

Module 2:

Sub TicK()
Dim nRng As Range
Set nRng = Range(ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.LinkedCell).Offset(, 1)
If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = 1 Then
nRng.Interior.ColorIndex = 4
With nRng.Font
    .ColorIndex = 3
    .FontStyle = "Bold"
    .Strikethrough = True
End With
Else
nRng.Interior.ColorIndex = xlNone
With nRng.Font
    .ColorIndex = 1
    .FontStyle = "General"
    .Strikethrough = False
End With
End If
End Sub

Module 3:

Sub MG04Mar09()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
CB.Caption = ""




'Remove "Rem" mark to Use Line below to set all "Checkbox values to "False
'CB.Value = False
Next CB
End Sub

Module 4:

Sub clearcheck()
Dim sh As Worksheet
Dim rng As Range
For Each sh In Sheets
Set rng = sh.Range("A2:A49,D2:D49,G2:G49,J2:J49,M2:M49,P2:p49,S2:S49,V2:V49")
On Error Resume Next
sh.CheckBoxes.Value = False
rng.Offset(, 2).Interior.ColorIndex = xlNone
With rng.Offset(, 2).Font
    .ColorIndex = 1
    .FontStyle = "General"
    .Strikethrough = False
    .Bold = False
End With
On Error GoTo 0
Next sh
End Sub

I hope this helps. Happy to send a copy of the sheet to you though.

It's really slow when clicking on the numbers and waiting for the tick plus highlight feature.

hope you can still help.

Cheers