Control format disappears when clicked (1 Viewer)

[andymartin3186]

Hi,
I have a textbox formatted to put a prefix letter PUR and make it 5 digits total so ID 7 for example would be PUR00007
The textbox displays fine, but when clicked to copy this, the "PUR" formatting disappears leaving just 00007
This is a nuisance when I need to copy and paste from the box, is there a way to stop it doing this? Or some code that will copy the contents of the control to the clipboard without removing the prefix that I could assign to an adjacent button?

 

[arnelgp]

the "Format" is for "display" purpose only, what is stored is Numeric (your field is Numeric).
if you need to make copy of the "formatted" textbox. create another textbox (dummy textbox)
and assign

=Format$([ID], "PUR00000")

as the Control Source.

 

[arnelgp]

or you can create a "button" on your form (btnCopyID) and on the click event of the form
it willl copy the "formatted" id:

Private sub YourBtn_Click()
Dim sID As String
If IsNumeric(Me.ID) Then
    sID =Format$(Me.[ID], "PUR00000")
    'copy sID to clipboard
   Clipboard sID
End If
End Sub

' https://stackoverflow.com/questions/14219455/excel-vba-code-to-copy-a-specific-string-to-clipboard/60896244#60896244
Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
                Case Len(s): .setData "text", v
                Case Else:   Clipboard = .getData("text") & ""
            End Select
        End With
    End With
End Function

 

[andymartin3186]

Many thanks for your help. Both worked great for me.