Find value in combobox by Target.Value

[drag1c]

Hi,

I am using event Worksheet Before Double Click, where I double click on cell to open form.
Form is filled by data from row, where the cell was double clicked.

In Form exists ComboBox with specific values, for example:
1) Low
2) Medium
3) High
4) None

I wish to create something like this but it will not work:

.Combobox1 = Target.Offset(0,-5)

How I can get value from cell in that combobox, but to not change list structure of the combobox?
If list structure is {Low, Medium, High, None}, this code below will create {Low, Medium, High, None, Medium} and set Medium as value inside combobox...

.cmbArea.AddItem Target.Offset(0, -3)
.cmbArea.Text = .cmbArea.List(.cmbArea.ListCount - 1)

So, Idea would be to somehow find value which already exists in combobox and to place it as predefined value.

Do you have an idea how it could be done?

 

[Micron]

I take it that if the userform control value is already in the combo list (and the combo is on a sheet) then you want to use that userform control value. If not, you want to add it or do something else? To find out if a value is in a sheet combo list, something like this will tell you:

For i = 0 To ComboBox2.ListCount - 1
     If ComboBox2.List(i) = "0" Then MsgBox "found it"
Next

Then you can simply use an If block to either set the combo to the existing value if found, or add the item if that's what you want to do.
You'd probably replace 0 with Target.Offset(0,-5)
Note that I'm not really suggesting you use a msgbox; it's just an example.