Help Needed On Combo Boxes

[David Lukes]

I am building a spread sheet which requires several
hundred comb boxes. The combo boxes all link to the same
Listfillrange but the linked cell has to be updated to a
cell in the same row as the box is located (column
remains unchanged).

The problem I have is I am having to manually edit each
boxes properties to update the Linked Cell which because
of the volume of boxes required is very time consuming.
When I copy and paste the linked cell value does not
update.

Is there a way to get the linked cell to update
automatically ?
Thanks

[Dave Peterson]

You could adjust the linkedcell when you create the comboboxes:

Option Explicit
Sub testme01()

Dim myOLEObj As OLEObject
Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("c1:c10")
For Each myCell In myRng.Cells
With myCell
Set myOLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, _
Height:=.Height)
End With

With myOLEObj
.LinkedCell = .TopLeftCell.Offset(0, -1) _
.Address(external:=True)
.ListFillRange = Worksheets("sheet2").Range("a1:a10") _
.Address(external:=True)
.Placement = xlMoveAndSize
End With
Next myCell
End With

End Sub

Or maybe if you already have them on the worksheet:

Option Explicit
Sub testme02()

Dim oleObj As OLEObject
With ActiveSheet
For Each oleObj In .OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
oleObj.LinkedCell = oleObj.TopLeftCell _
.Offset(0,-1).Address(external:=True)
End If
Next oleObj
End With

End Sub

--

Dave Peterson
ec3...@msn.com