User form textbox to split character currently present to show custom style appearance

[ipbr21054]

I am using the code shown below
In my worksheet in column B is a long value.
This long value is entered into my user form called MotorcycleDatbase at Text Box2

It is loaded as the shown format 1HFSC47N67A600508

I would like it to be shown as a custom format so its like this 1HF SC47N 6 7 A 600508 also see image supplied

So 1HF SPACE SC47N SPACE 6 SPACE 7 SPACE A SPACE 600508
Then all entered values will take on the same appearance

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Column = 2 Then
        If Target.Offset(, 1) = "HONDA" Then
            Sheets("MCVIN").Range("F7").Value = ActiveCell.Value
        Else
            MsgBox "YOU CAN ONLY SELECT HONDA", vbCritical, "HONDA ONLY MESSAGE"
            Exit Sub
        End If
    Else
        MsgBox "YOU MUST SELECT THE VIN IN COLUMN B", vbCritical, "SELECT VIN MESSAGE"
        Exit Sub
    End If
   
   Worksheets("MCVIN").Activate
   Worksheets("MCLIST").Activate
   MotorcycleDatabase.LoadData Me, Target.Row
End Sub

 

[6StringJazzer]

You are going to have to show us the code for the MotorcycleDatabase UserForm, because the formatting is going to happen at the point where the data is loaded into the TextBox.

 

[ipbr21054]

Do you mean this ?
As this is all the code when i right click the form in question

Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.TextBox1.Value = ws.Range("A" & rw).Value
    Me.TextBox2.Value = ws.Range("B" & rw).Value
    Me.TextBox3.Value = ws.Range("C" & rw).Value
    Me.TextBox4.Value = ws.Range("D" & rw).Value
    Me.TextBox5.Value = ws.Range("E" & rw).Value
    Me.TextBox6.Value = ws.Range("F" & rw).Value
    Me.TextBox7.Value = ws.Range("G" & rw).Value
    Me.TextBox8.Value = ws.Range("H" & rw).Value
    Me.TextBox9.Value = ws.Range("I" & rw).Value
    Me.TextBox10.Value = ws.Range("J" & rw).Value
    Me.TextBox11.Value = ws.Range("K" & rw).Value
    TextBox12.Value = Worksheets("MCVIN").Range("B9").Value
    TextBox13.Value = Worksheets("MCVIN").Range("C9").Value
    TextBox14.Value = Worksheets("MCVIN").Range("E9").Value
    TextBox15.Value = Worksheets("MCVIN").Range("F9").Value
    TextBox16.Value = Worksheets("MCVIN").Range("J9").Value
    TextBox17.Value = Worksheets("MCVIN").Range("L9").Value
    TextBox18.Value = Worksheets("MCVIN").Range("M9").Value
    TextBox19.Value = Worksheets("MCVIN").Range("O9").Value
    TextBox20.Value = Worksheets("MCVIN").Range("P9").Value
    TextBox21.Value = Worksheets("MCVIN").Range("R9").Value
    Me.Show
   
End Sub

 

[6StringJazzer]

To load the value into TextBox2

Dim VIN As String
VIN =  ws.Range("B" & rw).Value
Me.TextBox2.Value = Join(Array(Mid(VIN, 1, 3), _
                                                   Mid(VIN, 4, 5), _
                                                   Mid(VIN, 9, 1), _
                                                   Mid(VIN, 10, 1), _
                                                   Mid(VIN, 11, 1), _
                                                   Mid(VIN, 12, 6)), _
                                              " ")

 

[ipbr21054]

Do I just add this to the code above ?

 

[6StringJazzer]

It replaces

    Me.TextBox2.Value = ws.Range("B" & rw).Value

 

[ipbr21054]

Thanks.
I will do it tomorrow.

 

[ipbr21054]

I get a syntax error on this line in Red

Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
Dim VIN As String
VIN =  ws.Range("B" & rw).Value
Me.TextBox2.Value = Join(Array(Mid(VIN, 1, 3), _
                                                   Mid(VIN, 4, 5), _
                                                   Mid(VIN, 9, 1), _
                                                   Mid(VIN, 10, 1), _
                                                   Mid(VIN, 11, 1), _
                                                   Mid(VIN, 12, 6)), _
                                              " ")
    Me.TextBox1.Value = ws.Range("A" & rw).Value
    Me.TextBox3.Value = ws.Range("C" & rw).Value
    Me.TextBox4.Value = ws.Range("D" & rw).Value
    Me.TextBox5.Value = ws.Range("E" & rw).Value
    Me.TextBox6.Value = ws.Range("F" & rw).Value
    Me.TextBox7.Value = ws.Range("G" & rw).Value
    Me.TextBox8.Value = ws.Range("H" & rw).Value
    Me.TextBox9.Value = ws.Range("I" & rw).Value
    Me.TextBox10.Value = ws.Range("J" & rw).Value
    Me.TextBox11.Value = ws.Range("K" & rw).Value
    TextBox12.Value = Worksheets("MCVIN").Range("B9").Value
    TextBox13.Value = Worksheets("MCVIN").Range("C9").Value
    TextBox14.Value = Worksheets("MCVIN").Range("E9").Value
    TextBox15.Value = Worksheets("MCVIN").Range("F9").Value
    TextBox16.Value = Worksheets("MCVIN").Range("J9").Value
    TextBox17.Value = Worksheets("MCVIN").Range("L9").Value
    TextBox18.Value = Worksheets("MCVIN").Range("M9").Value
    TextBox19.Value = Worksheets("MCVIN").Range("O9").Value
    TextBox20.Value = Worksheets("MCVIN").Range("P9").Value
    TextBox21.Value = Worksheets("MCVIN").Range("R9").Value
    Me.Show
    
End Sub

 

[hungtbatman1]

I get a syntax error on this line in Red

Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
Dim VIN As String
VIN =  ws.Range("B" & rw).Value

Change

VIN = ws.Range("B" & rw).Value

on

VIN = ws.Range("B" & rw).Value