Split excel cell value into 2 user form text boxes

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top