Using VBA to answer Excel pop up questions

[maclachlan]

I need some help automatically answering a couple of Excel questions when they pop up with the following code.
I get 2 messages:
1. There is a large amout of information on the clipboard. Do you want to be able to paste this information into another program later?.... The answer to this questions has to be yes or the rest of the code won't work.
2. Data on the clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway? - I need the user to select yes, I'm not sure why this warning is coming up as the copy and paste ranges are the same. I really want the entire sheet to be copied but I can't get the code right.
Thanks to everybody that has helped me this far.

Private Sub CommandButton1_Click()
        ChDrive "h:\"
           ChDir "h:\"
          Filt = "Excel Files (*.csv), *.csv"
           FilterIndex = 5
           Title = "Please select a different File"
           Filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
          If Filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
          Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
           Workbooks.Open Filename


ActiveWorkbook.Sheets(1).Range("a1:bm1000").Copy
 ActiveWorkbook.Close SaveChanges:=False

 ActiveSheet.Paste Destination:=Worksheets("GMAC").Range("a1:bm1000")


Sheets("GMAC").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=12, Criteria1:="<>0", Operator:=xlAnd
    Sheets("Instructions").Select
End Sub

[/code]

 

[VoG]

Try using

Application.DisplayAlerts = False

before the line that generates the pop-up. Set it to True on the line after.

 

[erik.van.geit]

Hi,

for the first part of your question

Application.DisplayAlerts = False will work, but this will still leave you with a great amount of data on the clipboard ...
If you want to avoid this you can clear the clipboard easily with a few more lines.
see http://www.mrexcel.com/board2/viewtopic.php?t=231700&highlight=clearclipboard

kind regards,
Erik

 

[maclachlan]

Thanks for the 2 replies. The code worked great to turn off the messages. I would like to clear the clipboard but I'm not exactly sure where to enter the code. I tried a few different spots but I kept getting errors. Thanks for your time.

Private Sub CommandButton1_Click()
         ChDrive "h:\"
          ChDir "h:\"
          Filt = "Excel Files (*.csv), *.csv"
           FilterIndex = 5
          Title = "Please select a different File"
           Filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
           If Filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
         Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
         Workbooks.Open Filename


ActiveWorkbook.Sheets(1).Range("a1:bm1000").Copy
 Application.DisplayAlerts = False
  
 ActiveWorkbook.Close SaveChanges:=False

 
 ActiveSheet.Paste Destination:=Worksheets("GMAC").Range("a1:bm1000")

Application.DisplayAlerts = True
 
Sheets("GMAC").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=12, Criteria1:="<>0", Operator:=xlAnd
    Sheets("Instructions").Select
End Sub

[/code]

 

[erik.van.geit]

the "public declarations" must be on top in a module
the functioncode can be put (almost) anywhere

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long 
Public Declare Function EmptyClipboard Lib "user32" () As Long 
Public Declare Function CloseClipboard Lib "user32" () As Long 
  
Public Function ClearClipboard() 
    OpenClipboard (0&) 
    EmptyClipboard 
    CloseClipboard 
End Function

call the"clear the clipboard"-function just before closing the workbook - as you can see in the example

Call ClearClipboard 
ActiveWorkbook.Close False

getting closer ?

 

[maclachlan]

Thanks Erik, that works, I didn't add the ActiveWorkbook.Close False as that seemed to close the spreadsheet I was working on. Without it it seems to work fine. Just curious what does the "user32" do in the code?
Thanks again

 

[erik.van.geit]

Hi,

we're talking here about Application Programming Interface (API'calls)
http://msdn.microsoft.com/library/d...us/winprog/winprog/windows_api_start_page.asp

kind regards,
Erik

 

[VoG]

Hi Erik - does Application.CutCopyMode = False

do the same thing or just remove the dotted lines?

Sorry to hijack your thread maclachlan

 

[erik.van.geit]

VoG II,

For this subject, I really forgot the reasons ...
There will be a difference, else this code wouldn't exist: I'll call some other guys: see if they know.

best regards,
Erik

 

[Tom Urtis]

Hi Erik - does Application.CutCopyMode = False

do the same thing or just remove the dotted lines?

Good question.

There is quite a significant difference between clearing the clipboard to truly make it empty, and just executing the codeline
Application.CutCopyMode = False
to assume (as some people incorrectly do) that said codeline empties the clipboard, when really it does not.

That codeline simply does what it says, which is, if you are in CutCopyMode, such as by selecting a cell and hitting Edit > Copy or Edit > Cut, then executing that codeline will exit that mode.

There might be one or more of over 30 data formats supported by Windows and Excel which may be on the clipboard having nothing to do with CutCopyMode.

Recall that you can clear the clipboard with the ClearClipboard macro Erik posted.

Now then, the below macro evaluates what format(s) if any are on the clipboard.

The code looks lengthy but that is to look for each data type. I might have left out 2 or 3 so if someone sees one I missed please post it, but this is the general idea:



Sub TestClipboard()
Dim vaFormats As Variant, dFormat As Variant
vaFormats = Application.ClipboardFormats
For Each dFormat In vaFormats
Select Case dFormat
Case xlClipboardFormatBIFF
MsgBox "xlClipboardFormatBIFF (" & dFormat & ")"
Case xlClipboardFormatBIFF2
MsgBox "xlClipboardFormatBIFF2 (" & dFormat & ")"
Case xlClipboardFormatBIFF3
MsgBox "xlClipboardFormatBIFF3 (" & dFormat & ")"
Case xlClipboardFormatBIFF4
MsgBox "xlClipboardFormatBIFF4 (" & dFormat & ")"
Case xlClipboardFormatBinary
MsgBox "xlClipboardFormatBinary (" & dFormat & ")"
Case xlClipboardFormatBitmap
MsgBox "xlClipboardFormatBitmap (" & dFormat & ")"
Case xlClipboardFormatCGM
MsgBox "xlClipboardFormatCGM (" & dFormat & ")"
Case xlClipboardFormatCSV
MsgBox "xlClipboardFormatCSV (" & dFormat & ")"
Case xlClipboardFormatDIF
MsgBox "xlClipboardFormatDIF (" & dFormat & ")"
Case xlClipboardFormatDspText
MsgBox "xlClipboardFormatDspText (" & dFormat & ")"
Case xlClipboardFormatEmbeddedObject
MsgBox "xlClipboardFormatEmbeddedObject (" & dFormat & ")"
Case xlClipboardFormatEmbedSource
MsgBox "xlClipboardFormatEmbedSource (" & dFormat & ")"
Case xlClipboardFormatLink
MsgBox "xlClipboardFormatLink (" & dFormat & ")"
Case xlClipboardFormatLinkSource
MsgBox "xlClipboardFormatLinkSource (" & dFormat & ")"
Case xlClipboardFormatLinkSourceDesc
MsgBox "xlClipboardFormatLinkSourceDesc (" & dFormat & ")"
Case xlClipboardFormatMovie
MsgBox "xlClipboardFormatMovie (" & dFormat & ")"
Case xlClipboardFormatNative
MsgBox "xlClipboardFormatNative (" & dFormat & ")"
Case xlClipboardFormatObjectDesc
MsgBox "xlClipboardFormatObjectDesc (" & dFormat & ")"
Case xlClipboardFormatObjectLink
MsgBox "xlClipboardFormatObjectLink (" & dFormat & ")"
Case xlClipboardFormatOwnerLink
MsgBox "xlClipboardFormatOwnerLink (" & dFormat & ")"
Case xlClipboardFormatPICT
MsgBox "xlClipboardFormatPICT (" & dFormat & ")"
Case xlClipboardFormatPrintPICT
MsgBox "xlClipboardFormatPrintPICT (" & dFormat & ")"
Case xlClipboardFormatRTF
MsgBox "xlClipboardFormatRTF (" & dFormat & ")"
Case xlClipboardFormatScreenPICT
MsgBox "xlClipboardFormatScreenPICT (" & dFormat & ")"
Case xlClipboardFormatStandardFont
MsgBox "xlClipboardFormatStandardFont (" & dFormat & ")"
Case xlClipboardFormatStandardScale
MsgBox "xlClipboardFormatStandardScale (" & dFormat & ")"
Case xlClipboardFormatSYLK
MsgBox "xlClipboardFormatSYLK (" & dFormat & ")"
Case xlClipboardFormatTable
MsgBox "xlClipboardFormatTable (" & dFormat & ")"
Case xlClipboardFormatText
MsgBox "xlClipboardFormatText (" & dFormat & ")"
Case xlClipboardFormatToolFace
MsgBox "xlClipboardFormatToolFace (" & dFormat & ")"
Case xlClipboardFormatToolFacePICT
MsgBox "xlClipboardFormatToolFacePICT (" & dFormat & ")"
Case xlClipboardFormatVALU
MsgBox "xlClipboardFormatVALU (" & dFormat & ")"
Case xlClipboardFormatWK1
MsgBox "xlClipboardFormatWK1 (" & dFormat & ")"
Case dFormat = -1
MsgBox "Nothing is on the clipboard"
Case Else
MsgBox "Clipboard has data on it of the format number (" & dFormat & ")"
End Select
Next
End Sub



You can easily test the fact that
Application.CutCopyMode = False
does not empty the clipboard, by copying a drawing object, or a line of text from the formula bar, or an embedded combobox...whatever the case may be, and execute the codeline
Application.CutCopyMode = False
but you will still be able to paste that object or text elsewhere in Excel, as many times as your system memory allows, unless you run a macro like ClearClipboard that Erik posted (or you reboot the computer).

Notice also, the dynamic of "Paste" being enabled in the Edit or right-click menu is not a reliable way to assume that the clipboard is cleared. It only means that data which can be pasted onto an Excel worksheet might or might not be on the clipboard.

An example is to start with an empty clipboard and copy a file name from Windows Explorer. Now that copied file will be paste-able into another folder despite the Paste option not being enabled if you attempted to paste onto an Excel worksheet, meaning, the clipboard contains that file data even if you execute the codeline
Application.CutCopyMode = False
despite an Excel worksheet not being a compatible destination interface.

When you run the ClearClipboard macro Erik posted, that copied file will be cleared, hence demonstrating further the difference between
Application.CutCopyMode = False
and that macro, which I think answers the crux of your question.