VBA clipboard going wrong when File Explorer open

[Johnny C]

I've got this code attached to a button on my QAT. it copies the file path, filename, sheetname and selection range to the clipboard so that it can be quickly pasted into the sheet or emails.

It mostly works fine.

However when File Explorer is open, it usually just puts 2 ascii 10's into the clipboard. if I close File explorer it works fine again.

I think they are ascii 10's because they are the question mark in a box (calibri font), but if I do a code() on the characters sometimes it says 63 which is a question mark, but clearly they aren't standard question marks.

Microsoft Forms 2.0 Object Library is enabled in VBA Tools>References

Sub CopyDocPathName()

Dim DataObj As New MSForms.DataObject
Dim SheetNarr$, TempPlural$
Dim SelectedObject As Variant

If ActiveSheet.Type = xlWorksheet Then
    If TypeName(Selection) = "Range" Then
        If Selection.Cells.Count = 1 Then TempPlural = "" Else TempPlural = "s"
        SheetNarr = "Data source: " & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Tab [" & ActiveSheet.Name & "] Cell" & TempPlural & " " & Selection.Address
    Else
       SheetNarr = "Source: " & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Tab [" & ActiveSheet.Name & "] " & TypeName(Selection)
    End If
Else
    SheetNarr = "Chart source: " & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & " Chart tab [" & ActiveSheet.Name & "]"
End If

DataObj.SetText SheetNarr
DataObj.PutInClipboard

End Sub

What's going wrong?

 

[RoryA]

It's a bug that started in Windows 8 and has persisted in Win10. You should use API calls instead.

 

[Johnny C]

Cheers Rory. Done and it works fine now.

Finally - LongPtr data type used.

Pointers bring back bad memories of long hours scratching our heads when writing compilers in Pascal. Excel pointers aren't those though, they are just numbers.