Memo field, RTF, paste only the text

Experts,

I have a memo field formatted as RTF.
I have this memo field on a form that opens as modal.
I want to paste the text as plain text (and then format it)
I hit paste special<Text and it pastes as RTF.  

How can I paste the text as plain?  
I do not want to paste to notepad and then paste into the memo field (too many steps).  

thank you.
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Use the PlainText function:
=PlainText(NameOfMemoField)
pdvsaProject financeAuthor Commented:
OK but how do I use this?  I mean I want to be able to use it possibly as a rt click or some other simple way.
GrahamMandenoCommented:
As far as I know, there is no way in the UI to "Copy as Plain Text".  However, it can be done through code with a command button.  Copy the following code and paste it into a new standard module:
Option Compare Database
Option Explicit

Private Declare Function GlobalUnlock _
  Lib "kernel32" ( _
    ByVal hMem As Long _
  ) As Long

Private Declare Function GlobalLock _
  Lib "kernel32" ( _
    ByVal hMem As Long _
  ) As Long

Private Declare Function GlobalAlloc _
  Lib "kernel32" ( _
    ByVal wFlags As Long, _
    ByVal dwBytes As Long _
  ) As Long

Private Declare Function GlobalSize _
  Lib "kernel32" ( _
    ByVal hMem As Long _
  ) As Long

Private Declare Function lstrcpy _
  Lib "kernel32" ( _
    ByVal lpString1 As Any, _
    ByVal lpString2 As Any _
  ) As Long

Private Declare Function CloseClipboard _
  Lib "user32" ( _
  ) As Long

Private Declare Function OpenClipboard _
  Lib "user32" ( _
    ByVal hWnd As Long _
  ) As Long

Private Declare Function EmptyClipboard _
  Lib "user32" ( _
  ) As Long

Private Declare Function GetClipboardData _
  Lib "user32" ( _
    ByVal wFormat As Long _
  ) As Long

Private Declare Function SetClipboardData _
  Lib "user32" ( _
    ByVal wFormat As Long, _
    ByVal hMem As Long _
  ) As Long

Private Const GHND = &H42
Private Const CF_TEXT = 1

Public Function ClipBoard_SetText(strCopyString As String) As Boolean
  Dim hGlobalMemory As Long
  Dim lpGlobalMemory As Long
  Dim hClipMemory As Long

  ' Allocate moveable global memory.
  hGlobalMemory = GlobalAlloc(GHND, Len(strCopyString) + 1)

  ' Lock the block to get a far pointer to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)

  ' Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, strCopyString)

  ' Unlock the memory and then copy to the clipboard
  If GlobalUnlock(hGlobalMemory) = 0 Then
    If OpenClipboard(0&) <> 0 Then
      Call EmptyClipboard
      hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
      ClipBoard_SetText = CBool(CloseClipboard)
    End If
  End If
End Function

Public Function ClipBoard_GetText() As String
  Dim hClipMemory As Long
  Dim lpClipMemory As Long
  Dim strCBText As String
  Dim lngSize As Long
  If OpenClipboard(0&) <> 0 Then
    ' Obtain the handle to the global memory
    ' block that is referencing the text.
    hClipMemory = GetClipboardData(CF_TEXT)
    If hClipMemory <> 0 Then
      ' Lock Clipboard memory so we can reference
      ' the actual data string.
      lpClipMemory = GlobalLock(hClipMemory)
      If lpClipMemory <> 0 Then
        lngSize = GlobalSize(lpClipMemory)
        strCBText = SPACE$(lngSize)
        Call lstrcpy(strCBText, lpClipMemory)
        Call GlobalUnlock(hClipMemory)
        ' Peel off the null terminating character.
        strCBText = Left(strCBText, InStr(strCBText, vbNullChar) - 1)
      End If
    End If
    Call CloseClipboard
  End If
  ClipBoard_GetText = strCBText
End Function

Open in new window

Save the module as "mdlClipBoard"

Now, add a command button to your form with the caption "Copy as Plain Text" (or whatever else you like).  Name the button cmdCopyPlainText and assign it this event procedure:
Private Sub cmdCopyPlainText_Click()
On Error Resume Next
    ClipBoard_SetText Application.PlainText(Screen.PreviousControl.Value)
    If Err Then
        MsgBox "Cannot copy text" & vbCrLf & Err.Description
        Err.Clear
    End If
End Sub

Open in new window

Clicking this button will set the contents of the clipboard to the plain text version of the current control (which has just become the PreviousControl, because you have clicked the command button).

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pdvsaProject financeAuthor Commented:
Graham:  thank you for the response.  

I have to ask a follow up as I am not certain if I am using it as I should.
I have made the module and saved it.
I put a button on the form and named it as instructed and pasted the open form code as well.

to use it I open the form, go to ms word and copy something, go back to the form, hit the command button and the text is not pasted.  

I see the command button is named Copy text and I am under the assumption the button is used to Paste the text.    

I am not sure if I am suppose to initially paste the RTF text into the field with control V and then select it again, hit the command button to copy and and then use control V to repaste as plain text.
Please let me know what to do next.
==>this method seems to remove the RTF though.  

thank you once again....
GrahamMandenoCommented:
I understood that you wanted to copy from the memo textbox as plain text so that you could paste the plain text contents somewhere else.  I now see that you want to copy some formatted text from Word, and paste the plain text into your memo textbox.  My apologies for the misunderstanding.

Assign the following code to your command button's Click event:

Me![name of your memo control] = Application.PlainText(ClipBoard_GetText)

The event procedure should contain only this single line.

Good luck :-)
Graham
pdvsaProject financeAuthor Commented:
Ok that worked perfectly.   I suppose IrogSinta had essentially the same response but I am not an expert and not sure if they are one in the same.  Not certain if Irog's suggestion was to side step any module.  

Using Irog's method I sorta combined what you had mentioned but used the plaintext:
    Me!txtComments = PlainText(txtComments)
==>however, text was not pasted after copying from word.
GrahamMandenoCommented:
Me!txtComments = PlainText(txtComments)

This will simply take the rich text contents of the textbox and replace it with the plain text version.  To get it there from Word, you will need to paste in the textbox and then click the button to remove the formatting.

If you want a one-step method, then use the line I gave you:
Me!txtComments = Application.PlainText(ClipBoard_GetText)

Open in new window


This will take whatever is already in the clipboard (copied from Word), strip the formatting, and place the plain text version in your textbox.

-- Graham

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
I awarded points to both because while IrogSinta had a quick solution that worked great, I did not know how to implement it and Graham taught me how to do just that and by using a module I have one less step.  

thank you both for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.