ExcelBanter - Make a combobox equal to part of a cell value

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Make a combobox equal to part of a cell value (https://www.excelbanter.com/excel-programming/392824-make-combobox-equal-part-cell-value.html)

Make a combobox equal to part of a cell value

I have a cell in Sheets.(Sheet1).Range ("A1") where the content has several
digits a "/" several more digits and a "/" and then several more digits.

I want a VBA that will make usftest.cmbx1.value = equal to what ever value
is to the left of the first "/".

I want a VBA also that will make usftest.cmbx2 = to whatever value is
between the two "/"s.

A final VBA to make usftest.cmbx3 = to what ever value is to the right of
the second "/".

I'm not 100% sure what you mean by "make a combobox equal to..." (do you
want to find an item in ComboBox or add an item to it?), but you can process
the contents of the A1 cell like this...

Dim Fields() As String
Fields = Split(Sheets("Sheet1").Range("A1").Value, "/")
BeforeFirstSlash = Fields(0)
BetweenTwoSlashes = Fields(1)
AfterSecondSlash = Fields(2)

Rick

Make a combobox equal to part of a cell value

I am wanting to add to it. I basically have a combo box that I want
preloaded with data when it opens. It will tie back to data in a
spreadsheet. It has been simple enough to accomplish up until the scenerio
listed below.
--
Thanks
Shawn

"Rick Rothstein (MVP - VB)" wrote:

I have a cell in Sheets.(Sheet1).Range ("A1") where the content has several
digits a "/" several more digits and a "/" and then several more digits.

I want a VBA that will make usftest.cmbx1.value = equal to what ever value
is to the left of the first "/".

I want a VBA also that will make usftest.cmbx2 = to whatever value is
between the two "/"s.

A final VBA to make usftest.cmbx3 = to what ever value is to the right of
the second "/".

I'm not 100% sure what you mean by "make a combobox equal to..." (do you
want to find an item in ComboBox or add an item to it?), but you can process
the contents of the A1 cell like this...

Dim Fields() As String
Fields = Split(Sheets("Sheet1").Range("A1").Value, "/")
BeforeFirstSlash = Fields(0)
BetweenTwoSlashes = Fields(1)
AfterSecondSlash = Fields(2)

Rick

Make a combobox equal to part of a cell value

You can load the ComboBoxes up using code something like this...

Dim R As Range
Dim Fields() As String
For Each R In Range("A1:A6")
Fields = Split(R.Value, "/")
cmbx1.AddItem Fields(0)
cmbx2.AddItem Fields(1)
cmbx3.AddItem Fields(2)
Next

where I assume the original slash-delimited values are in A1:A6 for this
example (change the range to that where your data is).

Rick

"Shawn" wrote in message
...
I am wanting to add to it. I basically have a combo box that I want
preloaded with data when it opens. It will tie back to data in a
spreadsheet. It has been simple enough to accomplish up until the
scenerio
listed below.
--
Thanks
Shawn

"Rick Rothstein (MVP - VB)" wrote:

I have a cell in Sheets.(Sheet1).Range ("A1") where the content has
several
digits a "/" several more digits and a "/" and then several more
digits.

I want a VBA that will make usftest.cmbx1.value = equal to what ever
value
is to the left of the first "/".

I want a VBA also that will make usftest.cmbx2 = to whatever value is
between the two "/"s.

A final VBA to make usftest.cmbx3 = to what ever value is to the right
of
the second "/".

I'm not 100% sure what you mean by "make a combobox equal to..." (do you
want to find an item in ComboBox or add an item to it?), but you can
process
the contents of the A1 cell like this...

Dim Fields() As String
Fields = Split(Sheets("Sheet1").Range("A1").Value, "/")
BeforeFirstSlash = Fields(0)
BetweenTwoSlashes = Fields(1)
AfterSecondSlash = Fields(2)

Rick