Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Application.ScreenUpdating still seeing activated workbooks 2

Status
Not open for further replies.
alwayslrN

alwayslrN

IS-IT--Management
Jun 20, 2006
159
US
I know my questions are probably pretty elementary, but this VBA thing is OTJ for me.

My macro opens files in a folder and takes various actions of the activated sheets. I thought using Application.ScreenUpdating = False and Application.ScreenUpdating = True would keep the opened workbooks from being visible as the macro does it thing, but such is not the case. In my case there is a macro that opens the files and depending on data in the file calls the appropriate macro to run stuff. Within those macros I have

Code: 
Application.ScreenUpdating = False
   Do stuff..
Application.ScreenUpdating = True

I found Application.WindowState=xlMinimize, but then my macro did not behave correctly (as far as the results).

I even tried Windows(LoanName).Visible = xlVeryHidden, but that did not do the trick either. LoanName is the name of the workbook.

Once again, I am afraid my problem is the lack of me fully understanding or I have the placement of information in the wrong place. :(
 
Sort by date Sort by votes
Application.ScreenUpdating = false must be set before you open the fils
 
Upvote 0 Downvote
Thanks - but I do have it set there at the beginning of the macro
 
Upvote 0 Downvote
Could you post the rest of your code? Maybe the issue is on some other line
 
Upvote 0 Downvote
Perhaps this ?
Windows("LoanName.xls").Visible = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks PH, but that cause the same problem as the xlMinimize

Here is the code

(1) Open files
Code: 
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim processCnt As Long
Dim msg As String

Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim RemitFileName As Workbook

[b]Application.ScreenUpdating = False[/b]
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

processCnt = 0

ServicerFolder = wsMainMenu.Range("F8").Value
RemittanceMonth = wsMainMenu.Range("F7").Value

Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = ServicerFolder & RemittanceMonth
        .FileType = msoFileTypeAllFiles 'msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 
                 Set RemitFileName = wbResults
                 LoadName = RemitFileName.Name
                                  
                 'DO YOUR CODE HERE
                 Call SelectMacro
                 Call SelectRemitRange
                 
                 wbResults.Close SaveChanges:=False
                 
                 processCnt = (lCount + 1) - 1

                 Next lCount
            End If
    End With
    
     msg = "Total files processed: " & vbTab & processCnt
     msg = msg & vbCrLf & "Processed successfully: " & vbTab & processCnt - ErrorsU
     msg = msg & vbCrLf & "Number of errors: " & vbTab & vbTab & ErrorsU
     MsgBox msg
    
    On Error GoTo 0
    [b]Application.ScreenUpdating = True[/b]
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
    
    wsMainMenu.Activate
    
End Sub

(2) Select the appropriate macro
Code: 
Sub SelectMacro()
    'purpose is to search through the open workbook and run the appropriate macro based
    'on the Loan Field and Ending Schedule Value Field
    
    [b]Application.ScreenUpdating = False[/b]
    
    If Worksheets(1).Name = "Remittance" Or Worksheets(1).Name = "remiuttance summary" Then
        Worksheets(2).Activate
    Else
        Worksheets(1).Activate
    End If
    
    If Range("A6").Value = "InvLnNo" And Range("S6").Value = "END SCH BAL" Then
        Call macAS
    ElseIf Range("F1").Value = "LoanNumber" And Range("S1").Value = "EndSchPB" Then
        Call macFS
    ElseIf Range("F1").Value = "LoanNumber" And Range("U1").Value = "EndSchPB" Then
        Call macFU
    ElseIf (Range("C1").Value = "LoanNo" And Range("K1").Value = "EndSchBal") Or (Range("C1").Value = "LOANNO" And Range("K1").Value = "ENDSCHBAL") Then
        Call macCK
    ElseIf Range("B1").Value = "RFC LOAN NUMBER" And Range("T1").Value = "ENDING BAL" Then
        Call macBT
    ElseIf Range("C1").Value = "ACCT" And Range("N1").Value = "ENDSCH" Then
        Call macCN
    ElseIf Range("C1").Value = "Inv Loan No" And Range("L1").Value = "End Sch Bal" Then
        Call macCL
    ElseIf Range("C1").Value = "Inv Loan No" And Range("M1").Value = "End Sch Bal" Then
        Call macCM
    ElseIf Range("C1").Value = "Loan #" And Range("V1").Value = "End Sch Balance" Then
        Call macCV
    ElseIf Range("A1").Value = "INVESTOR LOAN NUMBER" And Range("U1").Value = "ENDING SCHEDULED BALANCE" Then
        Call macAU
    ElseIf Range("D1").Value = "LOAN NUMBER" And Range("X1").Value = "SCHEDULED PART PRINCIPAL" Then
        Call macDX
    ElseIf Range("C1").Value = "Loan Number" And Range("T1").Value = "Ending Scheduled Principal Balance" Then
        Call macCT
    ElseIf Range("C1").Value = "Loan No" And Range("T1").Value = "Ending Sch Bal" Then
        Call macCT2
    ElseIf Range("E1").Value = "Inv Loan Num" And Range("T1").Value = "Ending Sch Bal" Then
        Call macET
    ElseIf Range("C1").Value = "Loan" And Range("O1").Value = "Endg Sched" Then
        Call macCO
    ElseIf Range("D4").Value = "INV LOAN" And Range("O4").Value = "END SCHED P-BAL" Then
        Call macDO
    ElseIf Range("D1").Value = "Loan Number" And Range("Q1").Value = "Ending Sched Bal" Then
        Call macDQ
    Else
        'MsgBox "No procedure exists for this file."
        
        Application.DisplayAlerts = False
        Workbooks(LoadName).Close
        Application.DisplayAlerts = True
        Exit Sub
    End If
    
    [b]Application.ScreenUpdating = True[/b]
End Sub

(3) Example of one of the macros based on selection above
Code: 
Sub macAS()
'
' macExtractRemittance Macro
' Macro recorded 11/16/2006 by Administrator
'

'
Dim NetCellRng As Range
Dim GrossCellRng As Range
Dim PayoffOthrCellRng As Range

    [b]Application.ScreenUpdating = False[/b]
   
    Range("A8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("B1").Select
    ActiveSheet.Paste
    
    Call FileNamePaste
    
    Windows(LoadName).Activate
    Range("D8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("E1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

  
    Windows(LoadName).Activate
    Range("S8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("F1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

       
    Windows(LoadName).Activate
    Range("O8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        

    Windows(LoadName).Activate
    Range("M8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I1").Select
    
    Windows(LoadName).Activate
    Range("F8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows(LoadName).Activate
    Range("G8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
     
    Windows(LoadName).Activate
    Range("J8").Select
    
    For Each PayoffOthrCellRng In Range(Range("J8"), Range("J8").End(xlDown))
       If PayoffOthrCellRng.Value <> 0 Then
             PayoffOthrCellRng.Value = PayoffOthrCellRng.Value + Cells(PayoffOthrCellRng.Row, "I").Value + Cells(PayoffOthrCellRng.Row, "H").Value
             Cells(PayoffOthrCellRng.Row, "I").Value = 0
             Cells(PayoffOthrCellRng.Row, "H").Value = 0
       Else
             Cells(PayoffOthrCellRng.Row, "I").Value = Cells(PayoffOthrCellRng.Row, "I").Value + Cells(PayoffOthrCellRng.Row, "H").Value
       End If
    Next PayoffOthrCellRng
    
    Range("J8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
     
    Windows(LoadName).Activate
    Range("I8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        

    Range("N1").Select
    Windows(LoadName).Activate
    Range("X8").Select
    
    'mulitiplies records by 100
    For Each NetCellRng In Range(Selection, Selection.End(xlDown))
       NetCellRng.Value = NetCellRng.Value * 100
    Next NetCellRng
    
    Range("X8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows(LoadName).Activate
    Range("V8").Select
    
    'mulitiplies records by 100
    For Each GrossCellRng In Range(Selection, Selection.End(xlDown))
       GrossCellRng.Value = GrossCellRng.Value * 100
    Next GrossCellRng
    
    Range("V8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ThisWorkbook.Worksheets("REMITTANCE").Activate
    Range("O1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Call PoolFormula
    Call FormatValues
    Call RemoveLeadingZero
   
    Range("A1").Select
    Windows(LoadName).Activate
    Application.DisplayAlerts = False

    Windows(LoadName).Close
    Application.DisplayAlerts = True
    
    [b]Application.ScreenUpdating = True[/b]
    
    Workbooks("remittance.xls").Worksheets("sheet2").Activate
    
End Sub
 
Upvote 0 Downvote
The setting of Screen updating within each macro is reduntant but I can't see anything that would make it not work properly. When it doesn't work are you actually running the macro or stepping through it? Application.ScreenUpdating can't be set to false when your debugging an macro.
 
Upvote 0 Downvote




Trun the screen updating FALSE ....

ONLY in the CALLING procedure, NOT in the CALLED procedures.

Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Upvote 0 Downvote
HI Skip - I commented them out in the macAS procedure and still could see the activated workbook so I commented them out in SelectMacro as well and that just made matters worse (flickering was annoying) so I put those back as they were.

I hope I understood you correctly. sigh
 
Upvote 0 Downvote



"... matters worse (flickering was annoying) so I put those back as they were...."

ALL of your macXX procredures have to ALSO NOT set ScreenUpdating TRUE.

ALL of the CALLED procedures!!!!!!!!

Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Upvote 0 Downvote
I went back through ALL procedures except for the original calling procedure and not just the ones mentioned above - I am good now.

Thanks for the help.
 
Upvote 0 Downvote


Did that solve the initial problem?

Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Upvote 0 Downvote
Keep your code as is. Comment out ONLY this line in the SelectMacro procedure: "Application.ScreenUpdating = True".

As Skip pointed out. You are seeing the workbook because at the end of each macro you run you are reseting the screen. You only want screenupdating set to true at the end of ALL you need completed.
 
Upvote 0 Downvote
Yes Skip it did - thanks

Also thank you bdmagum
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

feipezi
  • Locked
  • Question
How to combine the tabs from multi workbooks in a single workbook with renaming tabs? 1
Replies
14
Views
295
feipezi
feipezi
Eitel13
  • Locked
  • Question
Merge 3 workbooks into new workbook
Replies
18
Views
523
SkipVought
S
remeng
  • Locked
  • Question
When File Print is activated run macro Word VBA 2010 1
Replies
3
Views
155
remeng
remeng
pendle666
  • Locked
  • Question
Copy Worksheets from existing Workbooks to new Workbook
Replies
8
Views
168
pendle666
pendle666
EliseFreedman
  • Locked
  • Question
Combining Multiple Workbooks
Replies
12
Views
174
simian336
simian336

Part and Inventory Search

Sponsor

Back
Top