Saving a variable into clipboard

[Lavina]

Hello guys,

i have a file that i only want to be editable by user forms. For that result i protect the sheet and unprotect it only when running the macro.

But users are still able to browse through the data, copy it. Most of the time they will edit specific places by copying and searching via the macro.

The problem i have is, when unprotecting, the copy made from the file disappears from the clipboard.

So i decided to manually push it into memory:

Sub Start()    Dim clipboard As MSForms.DataObject
    Dim strSample As String
    Set clipboard = New MSForms.DataObject
    clipboard.GetFromClipboard
    If Application.ClipboardFormats(1) = 0 Then
        strSample = clipboard.GetText
        clipboard.SetText strSample
    Else:
        clipboard.SetText ActiveCell.Value
    End If
    Call Unprotect
    clipboard.PutInClipboard
End Sub

But the problem is if my clipboard is empty, so i have the if there, but it does not seem to work, same value if it's empty or if there is a copy.
Application.ClipboardFormats(1) always = 0
I tried another approach:
https://stackoverflow.com/questions/35595258/how-to-check-if-clipboard-is-empty-of-text
but the same result, empty or not it goes to true.

 

[offthelip]

If I was designing a system like this I wouldn't use the clipboard for doing the copying at all, I would get the users to copy by clicking on a button that copied what they selected to a variant array which you can declare as public so that is persists until the workbook is closed. This can then be pasted to wherever you want. You could even run the "copy" macro when the user types Ctrl C. This would save all the problems of using a clipboard for copying

 

[Lavina]

That makes a lot of sense and is easier to control, thanks for the idea!

 

[offthelip]

you are welcome; avoiding a problem is always better than solving a problem