setting LinkedCell using VBA
 

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

setting LinkedCell using VBA
 

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

--
Regards,
Tom Ogilvy


"michael.beckinsale" wrote in message
ups.com...
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

setting LinkedCell using VBA
 

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