Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
I am trying to write a program to add checkboxes and delete certain
checkboxes in a row. How do I delete certain checkboxes. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
"sebastienm" wrote: 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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
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" wrote: 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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
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" wrote: 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" wrote: 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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
That work great.
Thank You "sebastienm" wrote: 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" wrote: 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" wrote: 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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
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 "ranswert" wrote: That work great. Thank You "sebastienm" wrote: 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" wrote: 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" wrote: 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" wrote: I am trying to write a program to add checkboxes and delete certain checkboxes in a row. How do I delete certain checkboxes. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete checkboxs
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 "ranswert" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checkboxs | Excel Discussion (Misc queries) | |||
checkboxs. | Excel Discussion (Misc queries) | |||
Excel Checkboxs, Optionbox | Excel Discussion (Misc queries) | |||
Checkboxs Move in Document | Excel Discussion (Misc queries) | |||
using Sub's with checkboxs | Excel Programming |