+ Reply to Thread
Results 1 to 7 of 7

Speed up the macro coding

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Speed up the macro coding

    Is this faster?

    Option Explicit
    
    Sub Check_Box()
        Dim i As Long
    
        With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        With Sheets("Filters")
            If .Shapes("Check Box 42").ControlFormat.Value = 1 Then
                For i = 43 To 76
                    .Shapes("Check Box " & i).ControlFormat.Value = True
                Next i
            Else
                For i = 43 To 76
                    .Shapes("Check Box " & i).ControlFormat.Value = False
                Next i
            End If
        End With
    
        With Application
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,692

    Re: Speed up the macro coding

    I doubt that it will be faster but who knows.
    Change references if and where required.
    Sub AAAAA()
    Dim a(33), i As Long, j As Long
    For i = 1 To 34
    a(i - 1) = "Check Box " & i + 42
    Next i
        If Sheets("Filters").Shapes("Check Box 42").ControlFormat.Value = 1 Then
            For j = LBound(a) To UBound(a)
                Sheets("Filters").Shapes(a(j)).ControlFormat.Value = True
            Next j
        Else
            For j = LBound(a) To UBound(a)
                Sheets("Filters").Shapes(a(j)).ControlFormat.Value = False
            Next j
        End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to avoid cell SELECTION but still format cells to speed up the macro running speed
    By BeefyBerts in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2018, 08:18 AM
  2. Everage Speed km/time (european speed)
    By GerryZucca in forum Excel General
    Replies: 3
    Last Post: 02-23-2015, 03:02 PM
  3. How to make VBA offset and speed up coding
    By Toyo613 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-05-2014, 10:34 AM
  4. macro coding help (i have no background in coding)
    By notgoodenough in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2014, 10:22 PM
  5. [SOLVED] Vb macro coding for transpose - complete coding?
    By Dharani Suresh in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-02-2013, 11:16 PM
  6. [SOLVED] Vb macro coding for transpose - complete coding?
    By Dharani Suresh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-02-2013, 05:04 AM
  7. [SOLVED] Implant macro coding into ASP coding
    By Sam yong in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1