Deleting text boxes

C

cart0250

Active Member
Joined
Jun 24, 2006
Messages
284
Office Version
  1. 2016
Platform
  1. Windows
The below partial code deletes some sheets of a workbook and then delete some text-boxes from the remaining sheets. I am just wondering if there is a more efficient way to delete the shapes? Tried to set up an array but didn't work and am thinking their may be a better solution anyway?

Sub summShot()
Application.DisplayAlerts = False
Sheets(Array("G SHEET", "S SHEET", "M SHEET", "ST SHEET", "E SHEET", "N SHEET", _
"H SHEET", "P SHEET", "TR SHEET", "B SHEET")).Delete

Sheets("ben").Select
ActiveSheet.Shapes("Text Box 407").Cut
ActiveSheet.Shapes("Text Box 415").Cut
ActiveSheet.Shapes("Text Box 408").Cut
ActiveSheet.Shapes("Text Box 410").Cut
ActiveSheet.Shapes("Text Box 411").Cut
ActiveSheet.Shapes("Text Box 412").Cut

Sheets("Glen").Select
ActiveSheet.Shapes("Text Box 124").Cut
ActiveSheet.Shapes("Text Box 125").Cut
ActiveSheet.Shapes("Text Box 128").Cut
ActiveSheet.Shapes("Text Box 126").Cut
ActiveSheet.Shapes("Text Box 127").Cut

Sheets("luve").Select
ActiveSheet.Shapes("Text Box 157").Cut
ActiveSheet.Shapes("Text Box 158").Cut
ActiveSheet.Shapes("Text Box 163").Cut
ActiveSheet.Shapes("Text Box 159").Cut
ActiveSheet.Shapes("Text Box 160").Cut

....
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Code: 
Dim sh as Shape
Sheets("Glen").Select 
for each sh in activesheet.shapes
if  sh.name like "Text Box 12[4-8]" then sh.cut
next

Best Regards
 
Upvote 0
Thanks. Is it possible to "select" the entire workbook, maybe through an array as used to delete the sheets, and then to use you code to delete the relevant shapes from all sheets at once rather than having to select each sheet individually?
 
Upvote 0
Of cause.
Such As:
Code: 
Sub del()
Dim sh As Shape, sht As Worksheet
For Each sht In Workbooks
If sht.Name Like "*en" Then
For Each sh In sht.Shapes
If sh.Name Like "Text Box 12[4-8]" Or sh.Name Like "Text Box 4[0-1][2-8]" Then sh.Cut
Next
End If
Next
End Sub
Best Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Running VBA Code from Excel to delete a Rich Text Format from a Word Document Dependent on A Dropdown Box in Excel
Replies
4
Views
1K
Dan_W
D
M
  • Solved
Return Value for Day of Week Interval based on Start / Stop time
Replies
2
Views
437
Marklarbear
M
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
502
fjns
F
S
  • Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
799
dmt32
D
S
  • Question
Easier Array to arrange columns VBA/Macro
Replies
4
Views
2K
Akuini
Akuini

Forum statistics

Threads
1,215,941
Messages
6,127,785
Members
449,407
Latest member
KLL_VA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top