Search code examples
excelvbahideuserform

How do I hide partial text in a UserForm text box while keeping the full text pastable?

I pull cells from a spreadsheet to a textbox in Excel. They are numbers with spaces in between them, and I want to format that text to only show the last four digits while having the whole value still be able to be pulled from that textbox. Is this possible?

Solution

  • A TextBox has a Tag property where you can store the original value before you format the value as needed. For example:

    Option Explicit

Private Sub UserForm_Activate()
   Dim CreditCard As String

   CreditCard = "1234 5678 1234 5678" 'or a cell value

   TextBox1.Tag = CreditCard
   TextBox1.Text = MaskCreditCard(CreditCard)
End Sub

Private Function MaskCreditCard(ByVal CreditCard As String) As String
   'set a Reference to "Microsoft VBScript Regular Expressions 5.5"
   Dim regEx As RegExp
   
   'there are many patterns you can choose
   Set regEx = New RegExp
   regEx.Global = True
   regEx.Pattern = "[0-9](?=.*.{4})"
   
   MaskCreditCard = regEx.Replace(CreditCard, "*")
End Function

    This produces:

    enter image description here

    You could choose any display format but I would opt for what I showed above. The original value is available in the Tag property.