+ Reply to Thread
Results 1 to 5 of 5

Disable Message box during VBA Execution

Hybrid View

alexxgalaxy Disable Message box during... 01-29-2013, 08:58 AM
alexxgalaxy Re: Disable Message box... 01-29-2013, 06:46 PM
VBA FTW Re: Disable Message box... 01-29-2013, 06:52 PM
alexxgalaxy Re: Disable Message box... 01-29-2013, 08:14 PM
alexxgalaxy Re: Disable Message box... 01-30-2013, 12:10 AM
  1. #1
    Registered User
    Join Date
    09-16-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    52

    Question Disable Message box during VBA Execution

    Good day!

    I searched from the net for the VBA that helps to copy data from a number of files (xls, xlsx, htm, txt, etc) saved in the same folder to another workbook and found the following one. It worked well except a message says "There is a large of amount of information on the Clipboard. Do you want to be able to paste this information into another program later?" kept popping up and I needed to click "Yes", "No" or "Cancel" multiple times so that the macro could go on. I found out from the net that the second codes below could serve that purpose. How does it fit in the first code code to turn off the pop-up message? If the second code is not applicable, how do I code to bypass the pop up?

    
    Sub ProcessAllFiles()
    
        Dim sPath As String
        Dim Wb As Workbook
        Dim sFile As String
        
    
        
        sPath = "C:\Temp\"
         
        sFile = Dir(sPath & "*.xlsx")
        
        Application.ScreenUpdating = False
        
            Do While sFile <> ""
            
    
            Set Wb = Workbooks.Open(sPath & sFile)
            Sheets(1).Activate
            ActiveSheet.UsedRange.Copy
              
            Wb.Close SaveChanges:=False
            
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
            
          Application.CutCopyMode = False
          
            
        sFile = Dir
            
        Loop
        
       Rows("1:1").Delete Shift:=xlUp
       
       Application.ScreenUpdating = True
        
    End Sub
    
    application.displayalerts = false
    ActiveWorkbook.Close savechanges:=false 'or true
    application.displayalerts = true
    Last edited by alexxgalaxy; 01-30-2013 at 12:10 AM.

  2. #2
    Registered User
    Join Date
    09-16-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Disable Message box during VBA Execution

    Can anybody help?

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: Disable Message box during VBA Execution

    When do you get the message? When closing the workbook you've opened via code, or when trying to save the workbook your macro is in? Have you tried putting the 2nd bit of code right before the Application.ScreenUpdating = True line?

  4. #4
    Registered User
    Join Date
    09-16-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Disable Message box during VBA Execution

    After I ran the code, the message came up (over and over again, I think it amounted to the number of files in the designated folder) and that's why I can't tell where in the 1st code I shall place the 2nd code. I'll try as you advised. Thanks!

  5. #5
    Registered User
    Join Date
    09-16-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Disable Message box during VBA Execution

    It didn't work after I put 2nd code right before the Application.ScreenUpdating = True line. The 2nd code closed my file and it's not my intention. I ended up placing them as follows and it worked this time.

           ActiveSheet.UsedRange.Copy
              
            Application.DisplayAlerts = False          
            Wb.Close savechanges:=False
            
            Application.DisplayAlerts = True        
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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