ExcelBanter - Delete CheckBoxes

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Delete CheckBoxes (https://www.excelbanter.com/excel-programming/453368-delete-checkboxes.html)

Delete CheckBoxes

Hi Howard,

Am Mon, 29 May 2017 22:23:20 -0700 (PDT) schrieb L. Howard:

Sub Delete_Checkboxes2()
ActiveSheet.Range("A2:B10").CheckBoxes.Delete
End Sub

try:

Sub DeleteChB()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Left < Columns(3).Left And shp.Top < Rows(11).Top Then
shp.Delete
End If
Next
End Sub

Regards
Claus B.
--
Windows10
Office 2016

Delete CheckBoxes

On Monday, May 29, 2017 at 10:56:06 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Mon, 29 May 2017 22:23:20 -0700 (PDT) schrieb L. Howard:

Sub Delete_Checkboxes2()
ActiveSheet.Range("A2:B10").CheckBoxes.Delete
End Sub

try:

Sub DeleteChB()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Left < Columns(3).Left And shp.Top < Rows(11).Top Then
shp.Delete
End If
Next
End Sub

Regards
Claus B.
--

Hi Claus,

That sure does the trick.

Strange way to set the range, I must say.

Is there a way to have the range limited to say columns D, E, F between rows 10 and 20.

Where you have to define all four sides of the range, top, bottom, left and right?

I tried to set a selected range to a variable and act on the variable to delete the boxes but could not get that to work.

Thanks.
Howard

Delete CheckBoxes

Hi Howard,

Am Tue, 30 May 2017 02:29:11 -0700 (PDT) schrieb L. Howard:

Strange way to set the range, I must say.

Is there a way to have the range limited to say columns D, E, F between rows 10 and 20.

Where you have to define all four sides of the range, top, bottom, left and right?

I tried to set a selected range to a variable and act on the variable to delete the boxes but could not get that to work.

controls are elements of a sheet (children of a sheet) and not elements
of a range.
If you want to delete controls in a specific range you have to call them
as shapes and check the top and the left property of the shapes.

Try:
Sub DeleteChB()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Left Columns(3).Left And shp.Left < Columns(7).Left _
And shp.Top Rows(10).Top And shp.Top < Rows(21).Top Then
shp.Delete
End If
Next
End Sub

Regards
Claus B.
--
Windows10
Office 2016

Delete CheckBoxes

Macro 1 deletes ALL checkboxes on the sheet.
Macro 1 errors out.

How do I delete checkboxes in a given range on the sheet, leaving the others
intact?

Thanks,
Howard

Sub Delete_Checkboxes1()
ActiveSheet.CheckBoxes.Delete
End Sub

Sub Delete_Checkboxes2()
ActiveSheet.Range("A2:B10").CheckBoxes.Delete
End Sub

This is probably more info than you want but...

FWIW:
Not being a fan of putting controls on a sheet *below* the frozen rows, I'd go
a different way...

If your sheet is a form, set the grid up to resemble graph paper so the cells
are square and turn off gridlines. Layout your form using borders for checkbox
cells (I prefer light-weight lines), and set the font to 'Wingdings 2' bold.

Put the following code in the sheet's SelectionCange event...

If Target.Font.Name = "Wingdings 2" Then
Target = IIf(Len(Target) 0, "", "P")
End If

Assuming your form has user instructions for filling it out.., using arrow keys
to move on/off the cell toggles the checkmark. Now all you need to do to remove
checkbox cells is toggle the borders off or hide the row!

FYI:
I use Arial 8 as my default font; -setting column width to 2 for B:AE and row
height to 12 sets up my print area width for using .5 margins for left/right. I
don't like using merged cells when not necessary, but input cells are unlocked
so sheet protection provides navigation flow via the Tab key (or arrow keys).
The cell immediately right of checkbox cells is also unlocked to facilitte the
toggle; -this cell is also left empty for this purpose. Using Shift+Tab returns
to the previous cell; -if it's a checkbox cell the checkmark gets toggled.

More FWIW:
I also use a variation on this approach to expand/collapse options that relate
to the checkbox being selected, and move selection to the 1st option. Grouped
options, in this case, get toggled if any one of the group is selected...

Option with 2 checkbox cells:
chk1 is selected; if user selects chk2 then chk1 is cleared and vice versa.

I find that not using controls below frozen rows makes for a more stable
project when it involves hidden rows. Button cells look/feel/behave like
command buttons as well via formatting.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Delete CheckBoxes

On Tuesday, May 30, 2017 at 3:18:27 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 30 May 2017 02:29:11 -0700 (PDT) schrieb L. Howard:

Strange way to set the range, I must say.

Is there a way to have the range limited to say columns D, E, F between rows 10 and 20.

Where you have to define all four sides of the range, top, bottom, left and right?

I tried to set a selected range to a variable and act on the variable to delete the boxes but could not get that to work.

controls are elements of a sheet (children of a sheet) and not elements
of a range.
If you want to delete controls in a specific range you have to call them
as shapes and check the top and the left property of the shapes.

Try:
Sub DeleteChB()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Left Columns(3).Left And shp.Left < Columns(7).Left _
And shp.Top Rows(10).Top And shp.Top < Rows(21).Top Then
shp.Delete
End If
Next
End Sub

Regards
Claus B.
--

Hi Claus,

Pretty logical now that I see it.

Thanks much

Howard

Delete CheckBoxes

On Tuesday, May 30, 2017 at 8:41:41 AM UTC-7, GS wrote:
Macro 1 deletes ALL checkboxes on the sheet.
Macro 1 errors out.

How do I delete checkboxes in a given range on the sheet, leaving the others
intact?

Thanks,
Howard

Sub Delete_Checkboxes1()
ActiveSheet.CheckBoxes.Delete
End Sub

Sub Delete_Checkboxes2()
ActiveSheet.Range("A2:B10").CheckBoxes.Delete
End Sub

This is probably more info than you want but...

FWIW:
Not being a fan of putting controls on a sheet *below* the frozen rows, I'd go
a different way...

If your sheet is a form, set the grid up to resemble graph paper so the cells
are square and turn off gridlines. Layout your form using borders for checkbox
cells (I prefer light-weight lines), and set the font to 'Wingdings 2' bold.

Put the following code in the sheet's SelectionCange event...

If Target.Font.Name = "Wingdings 2" Then
Target = IIf(Len(Target) 0, "", "P")
End If

Assuming your form has user instructions for filling it out.., using arrow keys
to move on/off the cell toggles the checkmark. Now all you need to do to remove
checkbox cells is toggle the borders off or hide the row!

FYI:
I use Arial 8 as my default font; -setting column width to 2 for B:AE and row
height to 12 sets up my print area width for using .5 margins for left/right. I
don't like using merged cells when not necessary, but input cells are unlocked
so sheet protection provides navigation flow via the Tab key (or arrow keys).
The cell immediately right of checkbox cells is also unlocked to facilitte the
toggle; -this cell is also left empty for this purpose. Using Shift+Tab returns
to the previous cell; -if it's a checkbox cell the checkmark gets toggled.

More FWIW:
I also use a variation on this approach to expand/collapse options that relate
to the checkbox being selected, and move selection to the 1st option. Grouped
options, in this case, get toggled if any one of the group is selected...

Option with 2 checkbox cells:
chk1 is selected; if user selects chk2 then chk1 is cleared and vice versa.

I find that not using controls below frozen rows makes for a more stable
project when it involves hidden rows. Button cells look/feel/behave like
command buttons as well via formatting.

--
Garry

Hi Garry,

That is more info than I need, but good to know.

I was working on some code that produces chkboxes on the sheet and assigns macros to them.

Some of them I wanted to keep and others I needed to delete to do a different way. Got tired of having to re-do the keeper boxes along with the throw outs.

Thanks.

Howard

Delete CheckBoxes

Hi Garry,

That is more info than I need, but good to know.

I was working on some code that produces chkboxes on the sheet and assigns
macros to them.

Some of them I wanted to keep and others I needed to delete to do a different
way. Got tired of having to re-do the keeper boxes along with the throw
outs.

Thanks.

Howard

If you'd like a sample of your project done the way I explained then email me
the file. Not sure how soon I can return it as there's lots going on here...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Delete CheckBoxes

On Tuesday, May 30, 2017 at 9:33:42 AM UTC-7, GS wrote:

Hi Garry,

That is more info than I need, but good to know.

I was working on some code that produces chkboxes on the sheet and assigns
macros to them.

Some of them I wanted to keep and others I needed to delete to do a different
way. Got tired of having to re-do the keeper boxes along with the throw
outs.

Thanks.

Howard

If you'd like a sample of your project done the way I explained then email me
the file. Not sure how soon I can return it as there's lots going on here...

--
Garry

No need to set up a sheet that way, I'm was on a test sheet and wanted a way to delete some, not all, chkboxes.

Thanks for the offer.

Howard