Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Checkboxes Anders J. Excel Programming 3 September 7th 09 02:12 PM
delete checkboxes PK Excel Discussion (Misc queries) 11 March 18th 09 01:45 PM
How many checkboxes can you have? Solution4U Excel Programming 1 October 14th 08 01:55 AM
How do I delete checkboxes from rows I deleted in a macro? Jazzi-D Excel Discussion (Misc queries) 1 January 18th 06 01:49 AM
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below Annette[_4_] Excel Programming 2 September 21st 04 02:40 PM


All times are GMT +1. The time now is 03:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"