VBA clipboard going wrong when File Explorer open

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
It's a bug that started in Windows 8 and has persisted in Win10. You should use API calls instead.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top