checkbox trouble?

[mitster]

Hey guys, im working on an xls in excel 2003 and having some trouble with checkboxes, i found the following code on the net to add checkboxes via the sheet macro, it works kinda however as i get further down the page the checks arent lining up with the rows, doesnt seem to matter if i start down there either? and the ones i just created (10 in a row starting at row 78) wont delete now by code or by hand!? if i try by code it throws an error at the deleting sub "Runtime error '9' subscript out of range", heres the code i modified:

Sub AddCheckBox()
Dim cell As Range

DelCheckBox  'Do the delete macro
'or delete all checkboxes in the worksheet
' ActiveSheet.CheckBoxes.Delete

For Each cell In Range("c78:c90")
  With ActiveSheet.CHECKBOXES.Add(cell.Left, _
     cell.Top, cell.Width, cell.Height)
     .LinkedCell = cell.Offset(, 0).Address(External:=True)
     '.Interior.ColorIndex = 1   'or  xlNone or xlAutomatic
     .Caption = ""
     '.Border.Weight = xlThin
  End With
  
Next

End Sub


Sub DelCheckBox()
For Each cell In Range("c78:c90")
Worksheets("Sheet1").CHECKBOXES.Delete
Next
End Sub

 

[Andrew Poulsom]

The code worked perfectly for me in Excel 2003. There is no need for a loop in DelCheckBox:

Sub DelCheckBox()
    ActiveSheet.CheckBoxes.Delete
End Sub

 

[mitster]

The code worked perfectly for me in Excel 2003. There is no need for a loop in DelCheckBox:

Sub DelCheckBox()
    ActiveSheet.CheckBoxes.Delete
End Sub

tried taking out the for loop, still throws exception '9'? tried just running the adding sub threw an exception "script out of range"? tried using "activesheet" instead, it removed all the boxes but now it wont create any either?

 

[Andrew Poulsom]

Sorry, I can't reproduce that error.

 

[mitster]

ok, ive now figured out that it does work intermittently? what code would i use if:

1. i just want to delete certain ones.
2. adjust or set the height equal to corresponding cell height.
3. fill the cells with a color based on RGB.
4. turn on the option for 3D shading.

thank you.

 

[Andrew Poulsom]

I amended your code for colour and 3D shading (it already sets the height to that of the cell):

Sub AddCheckBox()
    Dim cell As Range
    DelCheckBox  'Do the delete macro
'   or delete all checkboxes in the worksheet
'   ActiveSheet.CheckBoxes.Delete
    For Each cell In Range("c78:c90")
        With ActiveSheet.CheckBoxes.Add(cell.Left, _
            cell.Top, cell.Width, cell.Height)
            .LinkedCell = cell.Offset(, 0).Address(External:=True)
            .Interior.Color = RGB(0, 255, 0)
            .Display3DShading = True
            .Caption = ""
        End With
    Next cell
End Sub

You can delete a CheckBox by name, eg:

ActiveSheet.CheckBoxes("Check Box 1").Delete

You can also loop around the CheckBoxes collection and delete them one by one.

 

[mitster]

hey thanks for the reply, however that seems to be my biggest problem, the checkboxes not matching the height of the cells, its making them overlap and go out of sync the further down the page you go. is there a way to adjust the height of the boxes directly with code? also your modified deleting sub isnt working for me? and how would my loop look for deleting? thank you.

 

[mitster]

ok, got the height adjustment figured out. still having the problem with deleting, and also having a problem with color, i have another series of checkboxes that i made a custom rgb color for which is a lighter blue grey color (129,166,197), however when i try to code this color it comes out medium gray? thank you.

 

[Andrew Poulsom]

For an RGB colour Excel will use the closest match from the colour palette.

 

[mitster]

i see, why then was i able to choose any custom colour from the format controls window after i had created a checkbox by code? do you have to specify that the colour comes from the custom option?