Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete check boxes automatically
Currently I'm facing the following difficulty:
I have added several checkboxes in Column A. But if I want to delete them all, I have to select them one by one, right mouse click and press <delete Does someone know a simple way of deleting those checkboxes all in one action? Look forward to your reply, thanks in advance, Johan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete check boxes automatically
If they're from the forms toolbar:
activesheet.checkboxes.delete If they're from the control toolbox toolbar: Dim myCBX As OLEObject For Each myCBX In Activesheet.OLEObjects If TypeOf myCBX.Object Is MSForms.CheckBox Then myCBX.Delete End If Next myCBX wrote: Currently I'm facing the following difficulty: I have added several checkboxes in Column A. But if I want to delete them all, I have to select them one by one, right mouse click and press <delete Does someone know a simple way of deleting those checkboxes all in one action? Look forward to your reply, thanks in advance, Johan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete check boxes automatically
Tnx Dave, but it doesn't work yet.
I use the following Macro to add the checkboxes to a selected area: Sub chckbxmkr() On Error Resume Next Dim c As Range, myRange As Range Set myRange = Selection For Each c In myRange.Cells ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select With Selection ..LinkedCell = c.Address ..Characters.Text = "" ..Name = c.Address End With c.Select With Selection ..FormatConditions.Delete ..FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & c.Address & "=TRUE" ..FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked ..FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked ..Font.ColorIndex = 2 'cell background color = White End With Next myRange.Select End Sub But if I run your code I get an error on this: "TypeOf myCBX.Object Is MSForms.CheckBox" Can you help out? Tnx again, Johan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete check boxes automatically
You added checkboxes from the Forms toolbar.
Don't use that second suggestion. Use the first. Johan wrote: Tnx Dave, but it doesn't work yet. I use the following Macro to add the checkboxes to a selected area: Sub chckbxmkr() On Error Resume Next Dim c As Range, myRange As Range Set myRange = Selection For Each c In myRange.Cells ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select With Selection .LinkedCell = c.Address .Characters.Text = "" .Name = c.Address End With c.Select With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & c.Address & "=TRUE" .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked .Font.ColorIndex = 2 'cell background color = White End With Next myRange.Select End Sub But if I run your code I get an error on this: "TypeOf myCBX.Object Is MSForms.CheckBox" Can you help out? Tnx again, Johan -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete check boxes automatically
Aaah...I was reading too quickly. It works really good. Thanks for your
nice and quick help! Johan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete caption on multiple check boxes | Excel Discussion (Misc queries) | |||
Delete Check Boxes | Excel Discussion (Misc queries) | |||
delete check boxes | Excel Discussion (Misc queries) | |||
How do I delete an already created series of check boxes? | Excel Discussion (Misc queries) | |||
Macros to delete check boxes | Excel Discussion (Misc queries) |