checkboxes adjusted

[mort1703]

Hi

How do I adjust this code

All I require is 6 checkboxes with No LABELS, if possible I would like the checkbox in the centre of the appropriate cell

    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 2).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 2).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 2).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 2).Height
    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 3).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 3).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 3).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 3).Height
    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 4).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 4).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 4).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 4).Height
    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 5).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 5).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 5).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 5).Height
    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 6).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 6).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 6).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 6).Height
    ActiveSheet.OLEObjects.Add "FORMS.CHECKBOX.1", Left:=ActiveCell.Offset(EMPLOYEECOUNT, 7).Left, Top:=ActiveCell.Offset(EMPLOYEECOUNT, 7).Top, Width:=ActiveCell.Offset(EMPLOYEECOUNT, 7).Width, Height:=ActiveCell.Offset(EMPLOYEECOUNT, 7).Height

 

[Domenic]

Assuming that you've already declared and initialized "EMPLOYEECOUNT", try...

[COLOR=darkblue]Dim[/COLOR] oOleObj [COLOR=darkblue]As[/COLOR] OLEObject
[COLOR=darkblue]Dim[/COLOR] rTarget [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]

[COLOR=darkblue]Const[/COLOR] myWidth [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR] = 75 [COLOR=green]'change the width for the checkboxes as desired[/COLOR]
[COLOR=darkblue]Const[/COLOR] myHeight [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR] = 21 [COLOR=green]'change the height for the checkboxes as desired[/COLOR]

[COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] 7
    [COLOR=darkblue]Set[/COLOR] rTarget = ActiveCell.Offset(EMPLOYEECOUNT, i)
    [COLOR=darkblue]Set[/COLOR] oOleObj = ActiveSheet.OLEObjects.Add("FORMS.CHECKBOX.1", Width:=myWidth, Height:=myHeight)
    [COLOR=darkblue]With[/COLOR] oOleObj
        .Left = rTarget.Left + (rTarget.Width - oOleObj.Width) / 2
        .Top = rTarget.Top + (rTarget.Height - oOleObj.Height) / 2
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]Next[/COLOR] i

Hope this helps!

 

[Domenic]

When you say "no labels", if you mean no captions, try...

Dim oOleObj As OLEObject
Dim rTarget As Range
Dim i As Integer

Const myWidth As Double = [COLOR=#ff0000]12 [/COLOR]'change the width for the checkboxes as desired
Const myHeight As Double = 21 'change the height for the checkboxes as desired

For i = 2 To 7
    Set rTarget = ActiveCell.Offset(EMPLOYEECOUNT, i)
    Set oOleObj = ActiveSheet.OLEObjects.Add("FORMS.CHECKBOX.1", Width:=myWidth, Height:=myHeight)
    With oOleObj
        [COLOR=#ff0000].Object.Caption = ""[/COLOR]
        .Left = rTarget.Left + (rTarget.Width - oOleObj.Width) / 2
        .Top = rTarget.Top + (rTarget.Height - oOleObj.Height) / 2
    End With
Next i