Macro help

[Guest]

Hello,
I have a macro (below) that removes all tickmarks, but it also removes text
boxes with data in them. Is there a way to remove all tickmarks but leave
the text boxes alone.
Any help will be appreciated.


Sub RemoveShapes()
Dim wks As Worksheet
Dim shp As Shape

For Each wks In Worksheets
For Each shp In wks.Shapes
shp.Delete
Next shp
Next wks
End Sub

 

[Carim]

Hi,

If they are worksheet control toolbox text boxes then
Dim i As Long
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) <> "TextBox" Then

ActiveSheet.OLEObjects(i).Delete
End If
Next i

HTH
Carim

 

[Guest]

Hi Carim,
Thank you. How do I incorporate it in the macro I have? I am new to macros.

 

[Carim]

Hi mac,

Test it as a macro on its own :

Sub Delete()
Dim i As Long
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) <> "TextBox" Then
ActiveSheet.OLEObjects(i).Delete
End If
Next i
End Sub

HTH
Carim

 

[Guest]

Hi Carim
It does not do anything.

 

[Guest]

Please describe the procedure you used to originally create the "tickmarks".

Vaya con Dios
Chuck, CABGx3

 

[Guest]

Hi Chuck,
The tickmarks are a toolbar from a program I use . The macro I used works
great except it takes out the text boxes. I am new to macros and haven't a
clue what to do to make the tickmarks disappear and the text boxes to stay.
Any help will be greatly appreciated.

 

[Carim]

Hi mac,

I thought you meant checkboxes, when you were talking about tickmarks
....
I now understand there are not Excel objects ...
Are your textboxes Excel TextBoxes, or objects also produced by this
other program ???
Which program is it ?

Carim

 

[Guest]

Hi Carim,
They are excel text boxes. I really appreciate you help with this.

 

[Dave Peterson]

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

 

[Gord Dibben]

Thanks Dave

My bad again, although I didn't post the Public Sub PageSet()

It still needs some modiification to run on all sheets.

Public Sub PageSet()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Private Sub Worbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
With ws.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Better???


Gord

 

[Dave Peterson]

I thought that the procedures were used to do different things--I didn't think
each was supposed to run against all the worksheets.

Thanks for the other interpretation <vbg>.

 

[Gord Dibben]

As written, they weren't the same.

OP originally posted the PageSet and wanted to know how to do all sheets at once
and automatically without having to click a button when he opened the workbook.

I posted the Workbook_Open code with the error you picked out.

Now he has to make a choice on how to run the code.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Ahhh. You remember a previous post!

There's the difference. Each post is brand new to me!