trying to put a check box in each shell with vba

[rob merritt]

any ideas


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/30/2004 by me
'

'
On Error Resume Next
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=218.25, Top:=12, Width:=21, Height:=13.5) _
.Select
End Sub
Sub FormatRange()
With Worksheets("29-Dec-1900").Range("A1:G1000")
Module2.Macro1
End With
End Sub

 

[Bob Phillips]

Rob,

It's gonna be slow!

Sub Macro1(cell As Range)
Dim myObj
On Error Resume Next
Set myObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=cell.Left, _
Top:=cell.Top, _
Width:=cell.Width, _
Height:=13.5)
End Sub

Sub FormatRange()
Dim cell As Range
Application.ScreenUpdating = False
With Worksheets("29-Dec-1900")
For Each cell In Range("A1:G100")
Macro1 cell
Next cell
End With
Application.ScreenUpdating = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rob merritt]

Thanks Bob

that works great, I also used it to insert a combo box in the cells too
(this is a one shot thinn to build a static ss)

 

[Bob Phillips]

You are welcome Rob. Thanks for the update.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)