Results 1 to 4 of 4

VBA code runs slow

Threaded View

jamfz VBA code runs slow 08-24-2016, 09:39 AM
Kenneth Hobson Re: VBA code runs slow 08-24-2016, 10:08 AM
jamfz Re: VBA code runs slow 08-24-2016, 11:24 AM
Kenneth Hobson Re: VBA code runs slow 08-24-2016, 12:25 PM
  1. #3
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: VBA code runs slow

    Thanks for the reply Kenneth. I am not a VBA pro by any means, I'm still learning. Your comment reminded me of an important part I omitted from original posts code. I omitted parts to try and keep it clean and not take focus away from the problem area. The part that hides and unhides sheets is controlled by an intersect. If the users enter their initials in a cell within the range it enters the date in the column next to it, and also unhides the issue sheet that goes with it.
    There is also another single cell that needs to run the macro every time the value changes. Cell B4 - the user enters the number of rows they need for data and the macro unhides only the rows needed.
    The problem is every time anything is entered there is about a 3 second pause before the macro finishes and you are able to enter next cell value.
    I don't need to run everything when any cell is changed but at the time I started it I didn't know any better. It worked fine until I added all the sheets to unhide.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Set rng = ActiveCell
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    If Target.Cells.Count > 1 Then Exit Sub
    
            If Not Intersect(Target, Range("AD193:AD213")) Is Nothing Then
                With Target(1, -1)
                    .Value = Now
                End With 
    
    'this part hides and unhides rows on the sheet as needed by users for data entry. Rows 8-186
    If Target.Address = "$B$4" Then
        Toggle_Rows
        End If
    
    If Range("AB193") <> "" Then
            Worksheets("Issue 1").Visible = xlSheetVisible
        Else
            Worksheets("Issue 1").Visible = xlSheetHidden
        End If
        If Range("AB194") <> "" Then
            Worksheets("Issue 2").Visible = xlSheetVisible
        Else
            Worksheets("Issue 2").Visible = xlSheetHidden
        End If
        If Range("AB195") <> "" Then
            Worksheets("Issue 3").Visible = xlSheetVisible
        Else
            Worksheets("Issue 3").Visible = xlSheetHidden
        End If
        If Range("AB196") <> "" Then
            Worksheets("Issue 4").Visible = xlSheetVisible
        Else
            Worksheets("Issue 4").Visible = xlSheetHidden
        End If
        If Range("AB197") <> "" Then
            Worksheets("Issue 5").Visible = xlSheetVisible
        Else
            Worksheets("Issue 5").Visible = xlSheetHidden
        End If
        If Range("AB198") <> "" Then
            Worksheets("Issue 6").Visible = xlSheetVisible
        Else
            Worksheets("Issue 6").Visible = xlSheetHidden
        End If
        If Range("AB199") <> "" Then
            Worksheets("Issue 7").Visible = xlSheetVisible
        Else
            Worksheets("Issue 7").Visible = xlSheetHidden
        End If
        If Range("AB200") <> "" Then
            Worksheets("Issue 8").Visible = xlSheetVisible
        Else
            Worksheets("Issue 8").Visible = xlSheetHidden
        End If
        If Range("AB201") <> "" Then
            Worksheets("Issue 9").Visible = xlSheetVisible
        Else
            Worksheets("Issue 9").Visible = xlSheetHidden
        End If
        If Range("AB202") <> "" Then
            Worksheets("Issue 10").Visible = xlSheetVisible
        Else
            Worksheets("Issue 10").Visible = xlSheetHidden
        End If
        If Range("AB203") <> "" Then
            Worksheets("Issue 11").Visible = xlSheetVisible
        Else
            Worksheets("Issue 11").Visible = xlSheetHidden
        End If
        If Range("AB204") <> "" Then
            Worksheets("Issue 12").Visible = xlSheetVisible
        Else
            Worksheets("Issue 12").Visible = xlSheetHidden
        End If
        If Range("AB205") <> "" Then
            Worksheets("Issue 13").Visible = xlSheetVisible
        Else
            Worksheets("Issue 13").Visible = xlSheetHidden
        End If
        If Range("AB206") <> "" Then
            Worksheets("Issue 14").Visible = xlSheetVisible
        Else
            Worksheets("Issue 14").Visible = xlSheetHidden
        End If
        If Range("AB207") <> "" Then
            Worksheets("Issue 15").Visible = xlSheetVisible
        Else
            Worksheets("Issue 15").Visible = xlSheetHidden
        End If
        If Range("AB208") <> "" Then
            Worksheets("Issue 16").Visible = xlSheetVisible
        Else
            Worksheets("Issue 16").Visible = xlSheetHidden
        End If
         If Range("AB209") <> "" Then
            Worksheets("Issue 17").Visible = xlSheetVisible
        Else
            Worksheets("Issue 17").Visible = xlSheetHidden
        End If
        If Range("AB210") <> "" Then
            Worksheets("Issue 18").Visible = xlSheetVisible
        Else
            Worksheets("Issue 18").Visible = xlSheetHidden
        End If
        If Range("AB211") <> "" Then
            Worksheets("Issue 19").Visible = xlSheetVisible
        Else
            Worksheets("Issue 19").Visible = xlSheetHidden
        End If
        If Range("AB212") <> "" Then
            Worksheets("Issue 20").Visible = xlSheetVisible
        Else
            Worksheets("Issue 20").Visible = xlSheetHidden
        End If
        If Range("AB213") <> "" Then
            Worksheets("Issue 21").Visible = xlSheetVisible
        Else
            Worksheets("Issue 21").Visible = xlSheetHidden
        End If
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by jamfz; 08-24-2016 at 11:28 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA code runs slow when other workbooks are open
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2016, 12:47 AM
  2. 2003 code runs super slow in 2010
    By emanresu65 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-04-2013, 05:25 PM
  3. VBA Code runs too slow
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2012, 07:19 AM
  4. Handling Pivot Tables with macros - code runs slow
    By Pichingualas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2012, 10:58 AM
  5. Code runs slow until I bring some other app to foreground
    By patatvs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2011, 11:48 AM
  6. Fibonacci Code Runs too slow
    By MarvinP in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-20-2010, 02:14 PM
  7. VBA code runs slow until I push ESC
    By MCCCLXXXV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2007, 11:28 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