Split excel cell value into 2 user form text boxes

Denny57

New Member
Joined
Nov 23, 2015
Messages
24
I have two scenarios which I would appreciate some help with concerning a unique detail which is used to identify a record

I need to input 2 sets of 3 characters into a single cell and that recall the same into the userform at a later date.

The format needs to be 7 character in "nnn nnn" format and I can current do this by using concatenating two separate text boxes.

Ideally, I would like to format that textbox so that the space after the third character is automatically inserted as the detail is keyed into the textbox (and not when the information is moved into the worksheet so I only need a single textbox. Is this possible?

If not, I have a tried and tested matching process which populates the correct textboxes when I perform a search.
Unfortunately, in this scenario, whilst I can correctly identify the unique item, I cannot seem to find the correct syntax to populate the excel cell value into the two specific user form textboxes.

Dim lastrow
Dim myfind As String

lastrow = Sheets("Passenger").Range("A" & Rows.Count).End(xlUp).Row
myfind = txtUnitNumber1.Value & " " & txtUnitNumber2.Value
For currentrow = 2 To lastrow
If Cells(currentrow, 1).Text = myfind Then

txtUnitNumber1.Value = Cells(Left(currentrow, 1, 3))
txtUnitNumber2.Value = Cells(Right(currentrow, 1, 3))

cboUnitStockType.Value = Cells(currentrow, 2).Value
txtUnitClass.Value = Cells(currentrow, 3).Value

I know the highlighted cells are incorrect and that the code works for all remaining cells (over 20)
txtUnitNumber1 & txtUnitNumber2 are the two textboxes to be populated with the first 3 and last 3 characters of (currentrow, 1)

Can anyone please help?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
Ideally, I would like to format that textbox so that the space after the third character is automatically inserted as the detail is keyed into the textbox (and not when the information is moved into the worksheet so I only need a single textbox. Is this possible?

How about this
- inserts the space after user enters 3rd character and prevents user adding any characters after the 7th character

VBA Code:
Private Sub TextBox1_Change()
Dim T As String
With TextBox1
    T = .Text
        Select Case Len(T)
            Case 3: .Text = T & " "
            Case 8: .Text = Left(.Text, 7)
        End Select
End With
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,171
Office Version
  1. 2019
Platform
  1. Windows
Hi
untested but try

VBA Code:
With Cells(currentrow, 3)
    txtUnitNumber1.Value = Split(.Value, " ")(0)
    txtUnitNumber2.Value = Split(.Value, " ")(1)
End With

and see if does what you want when returning values to your textboxes

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,113,780
Messages
5,544,190
Members
410,597
Latest member
Jasen79
Top