Select Only Part of a Cell to Autofill Userform Textbox

[fun4all]

Hello,

I current have code that fills in textboxes in a userform with values in cells:

Private Sub CODE_Change()

Dim ws As Worksheet
Dim fm As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
Set fm = Worksheets("Form")

Dim range0 As Range, res As Variant
Set range0 = Worksheets(ActiveSheet.Name).Range("A1:P1000").Columns(1)
res = Application.Match(CODE.Text, range0, 0)

If Not IsError(res) Then

Set range1 = range0.Cells(res, 1)
AddData.Enabled = False
EditData.Enabled = True
CheckBox1.Enabled = False

PROJECT.Text = range1.Offset(0, 1)
COMPONENT.Text = range1.Offset(0, 3)
CRITERIA.Text = range1.Offset(0, 4)
ITEM.Text = range1.Offset(0, 2)
DEVELOPERNAME.Text = range1.Offset(0, 9)
NUMBOARDS.Text = range1.Offset(0, 5)
VENDOR.Text = range1.Offset(0, 7)
PLANT.Text = range1.Offset(0, 8)

Else

AddData.Enabled = True
EditData.Enabled = False
CheckBox1.Enabled = True

End If

SendEmail.Enabled = False
SaveForm.Enabled = False

End Sub

How to I modify the lines under Checkbox1.Enabled = False so that they only take a section (of varying size) of the text in a cell and put it in textboxes in a userform?

ie. The text in a cell is "Outer: A123 ; Inner: B456." I would like A123 to appear in a textbox called OuterColor and B456 to appear in textbox called InnerColor


Thank You

 

[jaybee3]

Hey looks like you could do with using split:

InnerColor.value = trim(split(trim(split(range1.offset(0,9).value,";")(1)),":"(1))
OuterColor.value = trim(split(trim(split(range1.offset(0,9).value,";")(0)),":"(1))

Should do it. Providing the cell you are taking it from is the next one along in the offset.

 

[fun4all]

Hi,

Thanks for the quick reply. When I type this up on vba I get a "subscript out of range" error. I can't seem to find the problem. Help is much appreciated

I am very new to vba and still am unfamiliar with many of vba's functions. Does this work if I were to split a cell into 3 sections of text?

Thank You.

 

[fun4all]

Ok I got past the subscript out of range error.

How do I split up 3 sections of text inside a cell? The only ranges that work are 0 to 1, and 1 to 1.

 

[fun4all]

Nvm. I got it.

Thanks again for the help!