+ Reply to Thread
Results 1 to 4 of 4

ScreenUpdate does not work with a macro that activates different workbooks

Hybrid View

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    3

    ScreenUpdate does not work with a macro that activates different workbooks

    Hi,
    I have a code that matches hidden columns in 2 different workbooks. It is a part of a UserForm that activates different processes and I couldn't avoid activating the workbooks.
    Eventhough I use Application.ScreeUpdating = False the screen still flickers. Is there any trick I might use to avoid it? Thanks in advance!

    Private Sub MatchColumns()
    Dim sFilePath As String
    Dim tFilePath As String
    Dim SourceFile As String
    Dim TargetFile As String
    Dim SourceActivetab As String
    Dim TargetActivetab As String
    Dim i As Integer
    Dim x As Integer
        
    Application.ScreenUpdating = False
        SourceActivetab = ComboBox1.Value
        TargetActivetab = ComboBox2.Value
      
        sFilePath = Left(TextBox1.Value, InStrRev(TextBox1.Value, ".", -1) - 1)
        SourceFile = Right(sFilePath, Len(sFilePath) - InStrRev(sFilePath, "\"))
        tFilePath = Left(TextBox2.Value, InStrRev(TextBox2.Value, ".", -1) - 1)
        TargetFile = Right(tFilePath, Len(tFilePath) - InStrRev(tFilePath, "\"))
        
        Application.Workbooks(TargetFile).Activate
        ActiveWorkbook.Worksheets(TargetActivetab).Activate
        Cells.Select
        Selection.EntireColumn.Hidden = False
        
        Application.Workbooks(SourceFile).Activate
        ActiveWorkbook.Worksheets(SourceActivetab).Activate
        
    For i = 1 To ActiveWorkbook.ActiveSheet.UsedRange.Columns.Count
    
        If ActiveWorkbook.ActiveSheet.Columns(i).Hidden = True Then
           Application.Workbooks(TargetFile).Activate
           ActiveWorkbook.Worksheets(TargetActivetab).Activate
           ActiveWorkbook.ActiveSheet.Columns(i).Hidden = True
           Else: ActiveWorkbook.ActiveSheet.Columns(i).Hidden = False
        End If
        Application.Workbooks(SourceFile).Activate
        ActiveWorkbook.Worksheets(SourceActivetab).Activate
    Next i
    Application.ScreenUpdating = True
    End Sub
    Last edited by VikaB; 08-08-2013 at 07:19 PM. Reason: As per Forum Rule #3, please use code tags…Thanks.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: ScreenUpdate does not work with a macro that activates different workbooks

    try something like this (not tested).

    Private Sub MatchColumns()
        
        Dim SourceFile As String
        Dim TargetFile As String
        Dim wsSource   As Worksheet
        Dim wsTarget   As Worksheet
        Dim i          As Long
        
        SourceFile = Left(TextBox1.Value, InStrRev(TextBox1.Value, ".", -1) - 1)
        SourceFile = Right(SourceFile, Len(SourceFile) - InStrRev(SourceFile, "\"))
        
        TargetFile = Left(TextBox2.Value, InStrRev(TextBox2.Value, ".", -1) - 1)
        TargetFile = Right(TargetFile, Len(TargetFile) - InStrRev(TargetFile, "\"))
        
        Set wsSource = Application.Workbooks(SourceFile).Worksheets(ComboBox1.Value)
        Set wsTarget = Application.Workbooks(TargetFile).Worksheets(ComboBox2.Value)
        
        Application.ScreenUpdating = False
        
        wsTarget.Columns.Hidden = False
        
        For i = 1 To wsSource.UsedRange.Columns.Count
            wsTarget.Columns(i).Hidden = wsSource.Columns(i).Hidden
        Next i
        
        Application.ScreenUpdating = True
        
    End Sub
    Last edited by AlphaFrog; 08-08-2013 at 08:23 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-27-2013
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: ScreenUpdate does not work with a macro that activates different workbooks

    Thanks! I will check it out.
    Last edited by VikaB; 08-08-2013 at 08:56 PM.

  4. #4
    Registered User
    Join Date
    02-27-2013
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: ScreenUpdate does not work with a macro that activates different workbooks

    Great, it works! It solved my problem, though a bit differently that I had expected

+ 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. Button who activates a macro
    By RoMarius1981 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-20-2012, 07:27 AM
  2. Avoid Application.ScreenUpdate=false/true in every Macro
    By dvb_24 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2011, 01:06 PM
  3. Using drawing object in code when it activates the macro
    By TheBirkmeister in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2010, 04:53 PM
  4. Cell select activates macro
    By pauluk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-28-2005, 09:55 AM
  5. Rectangle that activates a macro
    By in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2005, 03:06 PM

Tags for this Thread

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