text string separated by comma

[Atishoo]

I am using a user form to enter data on a sheet this includes a series of 4
combo boxes, a cell on the worksheet combines the values of all these
comboboxes into a string of 4 text values separated by comma and a space: eg

meds, visit, blood, home (there may sometimes only be two or three such
values)

When using the user form to edit this entr how do I get it to reverse the
process:

UserForm1.ComboBox2.Value = ActiveCell.Offset(0, 4).Value

will return the entire string! What I need is:

UserForm1.ComboBox2.Value = ActiveCell.Offset(0, 4).{first item of text}
UserForm1.ComboBox6.Value = ActiveCell.Offset(0, 4).{second item of text}
UserForm1.ComboBox7.Value = ActiveCell.Offset(0, 4).{third item of text}
UserForm1.ComboBox10.Value = ActiveCell.Offset(0, 4).{fourth itme of text}

 

[Bob Phillips]

UserForm1.ComboBox2.Value = Split(ActiveCell.Offset(0, 4,",")(0)
UserForm1.ComboBox6.Value = Split(ActiveCell.Offset(0, 4)(1)
UserForm1.ComboBox7.Value = Split(ActiveCell.Offset(0, 4)(2)
UserForm1.ComboBox10.Value = Split(ActiveCell.Offset(0, 4)(4)

 

[Stefi]

If ActiveCell contains the combined string:
stringitems = Split(ActiveCell, ",")

UserForm1.ComboBox2.Value =stringitems(0)
UserForm6.ComboBox2.Value =stringitems(1)
UserForm7.ComboBox2.Value =stringitems(2)
UserForm10.ComboBox2.Value =stringitems(3)

Regards,
Stefi

„Atishoo†ezt írta:

 

[Atishoo]

That is brilliant thankyou vey much!

The only prob i have left is when there are less than 4 values as then it
returns an out of range error any ideas on how to avoid this?

 

[Bob Phillips]

Type

UserForm1.ComboBox2.Value = Split(ActiveCell.Offset(0, 4),",")(0)
UserForm1.ComboBox6.Value = Split(ActiveCell.Offset(0, 4),",")(1)
UserForm1.ComboBox7.Value = Split(ActiveCell.Offset(0, 4),",")(2)
UserForm1.ComboBox10.Value = Split(ActiveCell.Offset(0, 4),",")(4)

 

[Dave Peterson]

You could check to see how many items were split and then populate the
comboboxes, but it may be easier to just append some commas to the string:

StringItems = Split(ActiveCell.Value & String(3, ","), ",")

 

[Shane Devenshire]

Hi,

There is a more profound issue here. It is probably bad practice to store
multiple data items in one field (column). Maybe, looking to the future, you
could consider storing the results in four columns and then using a formula
in the spreadheet to display a concatenated version of the data. Overall it
make sorting, grouping, pivoting, subtotalling, and filtering much easier if
each element is in its own column.

These are just some thought on this general area.