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

--
thank you mac

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

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


"Carim" wrote:

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

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

Hi Carim
It does not do anything.
--
thank you mac


"Carim" wrote:

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

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

Vaya con Dios
Chuck, CABGx3



"mac" wrote:

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

--
thank you mac

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.
--
thank you mac


"CLR" wrote:

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

Vaya con Dios
Chuck, CABGx3



"mac" wrote:

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

--
thank you mac

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

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

--
thank you mac


"Carim" wrote:

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

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

--

Dave Peterson

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


On Thu, 04 Jan 2007 12:42:24 -0600, Dave Peterson
wrote:

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

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 wrote:

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

On Thu, 04 Jan 2007 12:42:24 -0600, Dave Peterson
wrote:

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

--

Dave Peterson

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

On Thu, 04 Jan 2007 17:23:34 -0600, Dave Peterson
wrote:

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 wrote:

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

On Thu, 04 Jan 2007 12:42:24 -0600, Dave Peterson
wrote:

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

Ahhh. You remember a previous post!

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

Gord Dibben wrote:

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

On Thu, 04 Jan 2007 17:23:34 -0600, Dave Peterson
wrote:

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 wrote:

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

On Thu, 04 Jan 2007 12:42:24 -0600, Dave Peterson
wrote:

This only looks at the activesheet:
With ActiveSheet.PageSetup

Change it to:
With ws.PageSetup

(in the second routine)

Le Jurassien wrote:

Hello,
I m trying to set up a macro that will set the print out format to landscape
legal within the entire workbook. However, I am still having trouble with my
code. It works only on active worksheet instead of all. below are my code,
please help!

Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

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

Thanks,

--

Dave Peterson