Delete vale reference number before user form is populated

[ipbr21054]

I populate a user form by copying values from worksheet cells to the user form text boxes.

On my worksheet in column A is the customers name & its eference number.

When I currently populate the user form I see the example shown here entered in text box 1
TOM JONES 002

My goal is that when the value is entered in text box 1 the 002 has been removed.

All the customers on the worksheet will have a space then 3 digits after there name,
Example.
PAUL SMITH 352
JOHN JONES 001

It’s the 352 / 001 along with the space I’m looking to remote when the form is populated so there name is only present.

 

[Micron]

I don't know if you want to trim A1 or whatever the cell is, or trim the userform textbox. Assuming the latter, this could work (use your textbox name)
Me.textbox1 = Left(Me.textbox1,Len(Me.textbox1) - 4)

That is basically saying the same as
left("PAUL SMITH 352",len("PAUL SMITH 352") - 4)

That assumes your code is behind the userform, otherwise you can't use Me.

 

[ipbr21054]

Hi,
Having an issue getting that to work.

My userform has TextBox8 of which is where the customers name will be imported to.
On my worksheet the customers name will be in column A

I import values from worksheet to userform like this.

Me.TextBox1.Value = Cells(ActiveCell.Row, 18).Value
    Me.TextBox2.Value = Cells(ActiveCell.Row, 19).Value
    Me.TextBox3.Value = Cells(ActiveCell.Row, 20).Value

If i then add the line of code shown below with or wothout .Me to the above then i see Invalid procedure or arguemnt error

Me.TextBox8 = Left(Me.TextBox8, Len(Me.TextBox8) - 4)

Where should i put the code you advise thanks

 

[Micron]

I tested and what I posted worked but obviously I had to improvise with textbox names. Can you post the code that shows where/how this line was inserted? Best if you post the whole procedure from start to end as it will provide other insights. Best tag to use for code is the vba tags not rich (bb code) tags (please).

 

[ipbr21054]

I think that is my issue so please advise where you put it as im on my way home now

 

[Micron]

Not sure I understand what you're asking. I used what I posted in the immediate window but used a fully qualified reference like "userform1.Textbox1"
This is why I want to see the entire procedure; to ensure Me (or not using Me) is relevant. Come to think of it, it would be important to know where the code is. If it's not behind the form you must use a fully qualified reference and I pointed that out in my first post at the end.

 

[ipbr21054]

I am not sure where to put it

Please explain where it goes, show me the code you have used also please as opposed to the 1 line only

 

[ipbr21054]

You have advised 1 line of code but what is the event called, where do i then place it ?

This i dont know so need some help

 

[ipbr21054]

So it goes in the userform Initialize event.
Now working thanks

Private Sub UserForm_Initialize()
    
    '-------------------------------
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 70  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 200 ' LEFT / RIGHT OF SCREEN
   
    '-------------------------------
    CommandButton1.Visible = False
    
    Me.TextBox1.Value = Cells(ActiveCell.Row, 18).Value
    Me.TextBox2.Value = Cells(ActiveCell.Row, 19).Value
    Me.TextBox3.Value = Cells(ActiveCell.Row, 20).Value
    Me.TextBox4.Value = Cells(ActiveCell.Row, 21).Value
    Me.TextBox5.Value = Cells(ActiveCell.Row, 22).Value
    Me.TextBox6.Value = Cells(ActiveCell.Row, 23).Value
    Me.TextBox8.Value = Cells(ActiveCell.Row, 1).Value
    Me.TextBox8 = Left(Me.TextBox8, Len(Me.TextBox8) - 4)
    Me.ComboBox1.Value = Cells(ActiveCell.Row, 2).Value
    Me.ComboBox2.Value = Cells(ActiveCell.Row, 3).Value
    Me.ComboBox3.Value = Cells(ActiveCell.Row, 4).Value
    Me.ComboBox4.Value = Cells(ActiveCell.Row, 5).Value
    Me.ComboBox5.Value = Cells(ActiveCell.Row, 6).Value
    Me.ComboBox6.Value = Cells(ActiveCell.Row, 7).Value
    Me.ComboBox7.Value = Cells(ActiveCell.Row, 8).Value
    Me.ComboBox8.Value = Cells(ActiveCell.Row, 9).Value
    Me.ComboBox9.Value = Cells(ActiveCell.Row, 10).Value
    Me.ComboBox10.Value = Cells(ActiveCell.Row, 11).Value
    Me.ComboBox11.Value = Cells(ActiveCell.Row, 12).Value
    Me.ComboBox12.Value = Cells(ActiveCell.Row, 14).Value
    
End Sub

 

[Micron]

I could not advise where to put it until you revealed where your code was. Good that you figured it out. I see that you seem determined to use the Rich tags for code.
How to avoid tons of repetition, including .Value which you rarely need:

Private Sub UserForm_Initialize()
Dim i As Integer

Me.StartUpPosition = 0
Me.Top = Application.Top + 70  ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 200 ' LEFT / RIGHT OF SCREEN
CommandButton1.Visible = False

For i = 1 To 6
   Me("TextBox" & i) = Cells(ActiveCell.Row,1+17)
Next
Me.TextBox8 = Cells(ActiveCell.Row, 1)
Me.TextBox8 = Left(Me.TextBox8, Len(Me.TextBox8) - 4)

For i = 1 To 11
    Me("TextBox" & i) = Cells(ActiveCell.Row,i+1)
Next
Me.ComboBox12 = Cells(ActiveCell.Row, 14)
   
End Sub