setting LinkedCell using VBA

[michael.beckinsale]

Hi All,

Can anybody tell me why this doesn't work? Excel 2003

Sub testcbx()
Dim cb As Shape
Dim RowNo As Integer
On Error Resume Next
RowNo = 12
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.LinkedCell = "Petty Cash Expenses!H" & RowNo
End If
RowNo = RowNo + 1
Next

TIA

Regards

 

[Tom Ogilvy]

Because a shape doesn't have a linkedcell property.

Assume it is a combobox from the control toolbox toolbar

Dim ole as OleObject
Dim RowNo as Long
RowNo = 12
for each ole in activesheet.OleObjects
if typeof ole.Object is MSForms.Combobox then
ole.LinkedCell = "Petty Cash Expenses!H" & RowNo
RowNo = RowNo + 1
end if
Next

 

[michael.beckinsale]

Tom,

Many thanks, your a diamond. Amended code slightly because l have other
ComboBoxes l dont want to include. There are approx 100 of these so you
can see why l didn't want to set each of them by hand.

Dim ole As OLEObject
Dim RowNo As Long
RowNo = 11
For Each ole In ActiveSheet.OLEObjects
If TypeOf ole.Object Is MSForms.ComboBox Then
If ole.Name Like "ComboBox*" Then
ole.LinkedCell = "H" & RowNo
RowNo = RowNo + 1
End If
End If
Next

Regards

Michael Beckinsale