I keep getting intermittent error when trying to assign
the LinkedCell value to a combo box. I want the linked
value to be in the current row so I put the code in the
Worksheet_Selection Change event.

Select Case obj.Name
Case Is = "ComboBox7"


Case Else
obj.LinkedCell = Cells(theRow, theCol).Address
End Select

This works sometimes but when I select from the drop down
#N/A appears in the linked cell. Other times I get this
error.

Method 'LinkedCell' of object '_OLEObject' failed

I need to put the text value of the drop down in one cell
and the second column value in the next colum. I
populated the drop down with a pre-written list of values
on another sheet.

Would you please include code with your help?

Thank you!

Scott

Scott,

Use the change or click event of the combobox to assign the value to a cell. The following code assigns the selected value to a cell
on the same row as the active cell in the D column.

Private Sub ComboBox1_Change()
Cells(ActiveCell.Row, "D").Value = ComboBox1.Text
End Sub


--

John Green - Excel MVP
Sydney
Australia


"Scott" wrote in message ...
I keep getting intermittent error when trying to assign
the LinkedCell value to a combo box. I want the linked
value to be in the current row so I put the code in the
Worksheet_Selection Change event.

Select Case obj.Name
Case Is = "ComboBox7"


Case Else
obj.LinkedCell = Cells(theRow, theCol).Address
End Select

This works sometimes but when I select from the drop down
#N/A appears in the linked cell. Other times I get this
error.

Method 'LinkedCell' of object '_OLEObject' failed

I need to put the text value of the drop down in one cell
and the second column value in the next colum. I
populated the drop down with a pre-written list of values
on another sheet.

Would you please include code with your help?

Thank you!

Scott

Thanks for your response.

Why can't the comboBoxes be the same as any other VBA, you
know with listindex, ListCount, ListItem, etc. I'll use
your syntax. The problem is I have to run through an
array, match the text to an ordinal position in the array
and insert that value into the desired cell. Other wise I
would just insert into one cell the Text value of the
dropdown and in the next cell the listindex. It seems
wrong for VBA, but thos properties don't seem to exist.

In my searching for info on this subject I keep seeing
references to these very properties. I don't know what
version those were using, but my version (Excel-97 SR2)
doesn't provide them.

Thanks again,

Scott
-----Original Message-----
Scott,

Use the change or click event of the combobox to assign
the value to a cell. The following code assigns the
selected value to a cell
on the same row as the active cell in the D column.

Private Sub ComboBox1_Change()
Cells(ActiveCell.Row, "D").Value = ComboBox1.Text
End Sub


--

John Green - Excel MVP
Sydney
Australia


"Scott" wrote in message
...
I keep getting intermittent error when trying to assign
the LinkedCell value to a combo box. I want the linked
value to be in the current row so I put the code in the
Worksheet_Selection Change event.

Select Case obj.Name
Case Is = "ComboBox7"


Case Else
obj.LinkedCell = Cells(theRow, theCol).Address
End Select

This works sometimes but when I select from the drop
down
#N/A appears in the linked cell. Other times I get this
error.

Method 'LinkedCell' of object '_OLEObject' failed

I need to put the text value of the drop down in one
cell
and the second column value in the next colum. I
populated the drop down with a pre-written list of
values
on another sheet.

Would you please include code with your help?

Thank you!

Scott


.