Combobox
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 |
Combobox
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 |
Combobox
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 . |
All times are GMT +1. The time now is 07:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com