What I worked out years ago was although Access doesn't use MSForms directly there references to bits of it which it does use so as soon as you open any form those are in memory 'in use'.
I obviously didn't explain myself properly! The VBA IDE is common across all Office applications and it's the references dialog itself (which uses the underlying MSForms technology) that is causing the problem. The MSForms.dll is just the entry points for the technology and the dialog can't tell the difference.
I may be late to this thread, but I got this simple code from the spreadsheet guru blog which works well.
' Copy StoreText if not emplty to clipboard and return vbnullstring
' Return clipboard text if StoreText is empty
' Early bind using ref to Microsoft HTML Object Library
Public Function Clipboard(Optional ByVal ClipboardText As String = vbNullString) As String
'PURPOSE: Read/Write to Clipboard
'Source: ExcelHero.com (Daniel Ferry)
'blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard
On Error GoTo errpClipboard
Dim ReturnValue As String
Dim X As Variant 'Store as variant for 64-bit VBA support
X = ClipboardText
' With New HTMLDocument ' Early binding
With CreateObject("htmlfile")
With .parentWindow.clipboardData
Select Case True
Case Len(ClipboardText)
'Write to the clipboard
.SetData "text", X
Case Else
'Read from the clipboard (no variable passed through)
ReturnValue = .GetData("text")
End Select
End With
End With
donepClipboard:
On Error Resume Next
Clipboard = ReturnValue
Exit Function
errClipboard:
debug.print "Clipboard error: " & Description
Resume doneClipboard
End Function
Private Sub cmdCopyCode_Click()
'=========================================================
' Requires: Windows API declarations for clipboard control
' Compatible: 32-bit and 64-bit Office
'=========================================================
#If VBA7 Then
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As LongPtr, ByVal lpString2 As String) As LongPtr
#Else
' For 32-bit Office
' (Use non-PtrSafe declarations with Long instead of LongPtr)
#End If
Private Const CF_TEXT As Long = 1
Private Const GHND As Long = &H42
'---------------------------------------------------------
' Copy text to clipboard
'---------------------------------------------------------
Public Sub CopyToClipboard(ByVal Text As String)
Dim hGlobal As LongPtr, lpGlobal As LongPtr
OpenClipboard 0
EmptyClipboard
hGlobal = GlobalAlloc(GHND, Len(Text) + 1)
lpGlobal = GlobalLock(hGlobal)
lstrcpy lpGlobal, Text
GlobalUnlock hGlobal
SetClipboardData CF_TEXT, hGlobal
CloseClipboard
End Sub
'---------------------------------------------------------
' Read text from clipboard
'---------------------------------------------------------
Public Function GetClipboardText() As String
Dim lpGlobal As LongPtr
Dim Buffer As String
If IsClipboardFormatAvailable(CF_TEXT) = 0 Then Exit Function
OpenClipboard 0
lpGlobal = GetClipboardData(CF_TEXT)
If lpGlobal Then
Buffer = Space(1024) ' Arbitrary buffer size
lstrcpy StrPtr(Buffer), lpGlobal
GetClipboardText = Left(Buffer, InStr(Buffer, vbNullChar) - 1)
End If
CloseClipboard
End Function
Sub TestCopy()
Call CopyToClipboard("Hello from VBA!")
End Sub
Sub TestPaste()
MsgBox GetClipboardText()
End Sub
Option Compare Database
Option Explicit
Private Const FORMS_DATAOBJECT As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"
Private m_cb As Object
Private Function getDataObj() As Object
' Use singleton
If m_cb Is Nothing Then
Set m_cb = CreateObject(FORMS_DATAOBJECT)
End If
Set getDataObj = m_cb
End Function
Function Copy(strinput As String) As Boolean
On Error GoTo Result
With getDataObj
.SetText strinput
.PutInClipboard
End With
Result:
Copy = Err = 0
End Function
Function Paste() As String
With getDataObj
.GetFromClipboard
Paste = .GetText
End With
End FunctionHaha, it looks like I probably found it from Daniel Pineault's site linked by Tom in post#2!
