delete checkboxs

[ranswert]

I am trying to write a program to add checkboxes and delete certain
checkboxes in a row. How do I delete certain checkboxes.
Thanks

 

[sebastienm]

Hi,
It depends on the type of checkboxes. Are they controls from the Forms
toolbar or from the Control Toolbox toolbar.
In the first case:
Dim wsh As Worksheet
Set wsh = ActiveSheet
wsh.CheckBoxes("Check Box 3").Delete ''' by name
wsh.CheckBoxes(1).Delete ''' by index
In the second case
Dim wsh As Worksheet
Set wsh = ActiveSheet
wsh.OLEObjects("CheckBox1").Delete ''' by name
wsh.OLEObjects(1).Delete ''' by index

 

[ranswert]

Hi,
It depends on the type of checkboxes. Are they controls from the Forms
toolbar or from the Control Toolbox toolbar.
In the first case:
Dim wsh As Worksheet
Set wsh = ActiveSheet
wsh.CheckBoxes("Check Box 3").Delete ''' by name
wsh.CheckBoxes(1).Delete ''' by index
In the second case
Dim wsh As Worksheet
Set wsh = ActiveSheet
wsh.OLEObjects("CheckBox1").Delete ''' by name
wsh.OLEObjects(1).Delete ''' by index



--
Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>

 

[ranswert]

This is the code that I used to add the checkbox

ActiveSheet.CheckBoxes.Add(40, 138 + (ActiveCell.Value * 12.75), 93,
17.25).Select
Selection.Characters.Text = "Add to Estimate"
ActiveCell.Offset(0, 2).Select

When I write a code to delete a certain checkbox that has been added how do
I select that checkbox to delete.
thanks

 

[sebastienm]

You need to know its name or index.

In your code, it seems like a chekbox is associated to a specific row (1
checkbox per row), am i correct?
In that case, you could give it the name "CheckboxRow" & Activecell.Row
i.e.

ActiveSheet.CheckBoxes.Add(40, 138 + (ActiveCell.Value * 12.75), 93,
17.25).Select
Selection.Characters.Text = "Add to Estimate"
selection.name = "CheckboxRow" & activecell.row ''' <<<<<<<<<
ActiveCell.Offset(0, 2).Select

Now when you want to delete the check box in row 14, you can write:

ActiveSheet.CheckBoxes("CheckboxRow" & activecell.row).Delete

Would that work?

 

[ranswert]

That work great.
Thank You

You need to know its name or index.

In your code, it seems like a chekbox is associated to a specific row (1
checkbox per row), am i correct?
In that case, you could give it the name "CheckboxRow" & Activecell.Row
i.e.

ActiveSheet.CheckBoxes.Add(40, 138 + (ActiveCell.Value * 12.75), 93,
17.25).Select
Selection.Characters.Text = "Add to Estimate"
selection.name = "CheckboxRow" & activecell.row ''' <<<<<<<<<
ActiveCell.Offset(0, 2).Select

Now when you want to delete the check box in row 14, you can write:

ActiveSheet.CheckBoxes("CheckboxRow" & activecell.row).Delete

Would that work?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>

 

[ranswert]

This worked until I deleted a row in the center of my sheet and the checkboxs
below the deleted row kept the same row number and it didn't match the row
that the checkbox was in.
Thanks

 

[sebastienm]

That will definitely create an issue.


You could do the following when adding a checkbox: in a hidden column , say
column , when you add a checkbox, set the cell on the same row in that column
to the name of the checkbox.

''' Carefull, this will write in column A:
'------------------------------------------------------------
ActiveSheet.CheckBoxes.Add(40, 138 + (ActiveCell.Value * 12.75), 93,
17.25).Select
Selection.Characters.Text = "Add to Estimate"
selection.name = "CheckboxRow" & activecell.row ''' <<<<<<<<<
ActiveCell.EntireRow.Cells(1) = "CheckboxRow" & activecell.row ''' <<<<<<<
ActiveCell.Offset(0, 2).Select
'----------------------------------------------------------


Now when deleting the checkbox, use the name in column A of that row instead
of the activecell's row number:
'-----------------------------------------------------------
ActiveSheet.CheckBoxes("CheckboxRow" & activecell.EntireRow.cells(1)).Delete
'-----------------------------------------------------------


One thing you have to do also, is make sure the checkbox 'follows' the row
when resized or new rows are inserted. Set the Placement property of the
checkbox properly when creating it in the above code:
'-----------------------------------------------------------
''' not sure which one of xlMove, xlFreeFloating , xlMoveAndSize
''' try it out
selection.Placement = xlMoveAndSize
'-----------------------------------------------------------


However, there is no way to track a Delete row or an Insert row, so you may
end up with some orphan chekboxes at some point.
To have everything working perfectly, you will need more work. Something to
try is to Protect the sheet and add buttons linked to macros to Insert a row
and Delete a row. This way you know when it happens and you can re-arrange
your checkboxes.


Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>