The marco opens a new sheet and returns the name of the deleted styles
in the workbook of the new sheet. Or, it's suppose too. The problem is
that sometimes it will not return the name of the deleted styles to
the new sheet. The only reason for it doing this that I could come up
with is that the macro works in excel 2003 workbooks but not 2007
workbooks. This could be completely wrong, it is only a guess. The
code is as follows:

ActiveWorkbook.Sheets.Add Befo=Worksheets(1)
ActiveSheet.Name = "Deleted Styles"
DelCount = 0

For Each styT In ActiveWorkbook.Styles
If DelCount < 30000 Then
If styT.BuiltIn < True Then
styT.Delete
Range("A1").Offset(DelCount).Select
Range("A1").Offset(DelCount).Value = styT.Value
DelCount = DelCount + 1

Else
End If
Else
Exit Sub
End If
Next styT

Any solutions would be appreciated. I believe the problem has
something to do with the "Range("A1").Offset(DelCount).Value =
styT.Value" but again, I could be wrong.

Thanks - HT

I think you want the styT.Name not the styT.Value
Also, I would extract the name before deleting it.
A better bet for you, it appears, would be to use the free "Formats & Styles" add-in available
here...
http://excelusergroup.org/media/
It can generate a list of unused Styles or delete them.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)






"Harrison Tomlinson"
wrote in message
...
The marco opens a new sheet and returns the name of the deleted styles
in the workbook of the new sheet. Or, it's suppose too. The problem is
that sometimes it will not return the name of the deleted styles to
the new sheet. The only reason for it doing this that I could come up
with is that the macro works in excel 2003 workbooks but not 2007
workbooks. This could be completely wrong, it is only a guess. The
code is as follows:

ActiveWorkbook.Sheets.Add Befo=Worksheets(1)
ActiveSheet.Name = "Deleted Styles"
DelCount = 0

For Each styT In ActiveWorkbook.Styles
If DelCount < 30000 Then
If styT.BuiltIn < True Then
styT.Delete
Range("A1").Offset(DelCount).Select
Range("A1").Offset(DelCount).Value = styT.Value
DelCount = DelCount + 1

Else
End If
Else
Exit Sub
End If
Next styT

Any solutions would be appreciated. I believe the problem has
something to do with the "Range("A1").Offset(DelCount).Value =
styT.Value" but again, I could be wrong.

Thanks - HT

On Jul 20, 12:42*pm, "Jim Cone" wrote:
I think you want the styT.Name not the styT.Value
Also, I would extract the name before deleting it.
A better bet for you, it appears, would be to use the free "Formats & Styles" add-in available
here...
*http://excelusergroup.org/media/
It can generate a list of unused Styles or delete them.
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"Harrison Tomlinson"
wrote in ...



The marco opens a new sheet and returns the name of the deleted styles
in the workbook of the new sheet. Or, it's suppose too. The problem is
that sometimes it will not return the name of the deleted styles to
the new sheet. The only reason for it doing this that I could come up
with is that the macro works in excel 2003 workbooks but not 2007
workbooks. This could be completely wrong, it is only a guess. The
code is as follows:

ActiveWorkbook.Sheets.Add Befo=Worksheets(1)
* *ActiveSheet.Name = "Deleted Styles"
* *DelCount = 0

* *For Each styT In ActiveWorkbook.Styles
* * * *If DelCount < 30000 Then
* * * * * *If styT.BuiltIn < True Then
* * * * * * * *styT.Delete
* * * * * * * *Range("A1").Offset(DelCount).Select
* * * * * * * *Range("A1").Offset(DelCount).Value = styT.Value
* * * * * * * *DelCount = DelCount + 1

* * * * * * * *Else
* * * * * *End If
* * * *Else
* * * * * * Exit Sub
* * * *End If
* *Next styT

Any solutions would be appreciated. I believe the problem has
something to do with the "Range("A1").Offset(DelCount).Value =
styT.Value" but again, I could be wrong.

Thanks - HT- Hide quoted text -

- Show quoted text -

I changed the styT.Value to styT.Name and the same error was
occurring.

The Add-on would be my first choice but sadly, I can not add it. This
must be accomplished through an excel macro. Any other possible
solutions?

Thanks - HT

Harrison Tomlinson explained :
On Jul 20, 12:42*pm, "Jim Cone" wrote:
I think you want the styT.Name not the styT.Value
Also, I would extract the name before deleting it.
A better bet for you, it appears, would be to use the free "Formats &
Styles" add-in available here...
*http://excelusergroup.org/media/
It can generate a list of unused Styles or delete them.
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"Harrison Tomlinson"
wrote in
...



The marco opens a new sheet and returns the name of the deleted styles
in the workbook of the new sheet. Or, it's suppose too. The problem is
that sometimes it will not return the name of the deleted styles to
the new sheet. The only reason for it doing this that I could come up
with is that the macro works in excel 2003 workbooks but not 2007
workbooks. This could be completely wrong, it is only a guess. The
code is as follows:

ActiveWorkbook.Sheets.Add Befo=Worksheets(1)
* *ActiveSheet.Name = "Deleted Styles"
* *DelCount = 0

* *For Each styT In ActiveWorkbook.Styles
* * * *If DelCount < 30000 Then
* * * * * *If styT.BuiltIn < True Then
* * * * * * * *styT.Delete
* * * * * * * *Range("A1").Offset(DelCount).Select
* * * * * * * *Range("A1").Offset(DelCount).Value = styT.Value
* * * * * * * *DelCount = DelCount + 1
* * * * * * * *Else
* * * * * *End If
* * * *Else
* * * * * * Exit Sub
* * * *End If
* *Next styT

Any solutions would be appreciated. I believe the problem has
something to do with the "Range("A1").Offset(DelCount).Value =
styT.Value" but again, I could be wrong.
Thanks - HT- Hide quoted text -

- Show quoted text -

I changed the styT.Value to styT.Name and the same error was
occurring.

The Add-on would be my first choice but sadly, I can not add it. This
must be accomplished through an excel macro. Any other possible
solutions?

Thanks - HT

Since add-ins contain macros, AND the add-in Jim suggested for you has
macros to meet your needs, is there some reason why you can't copy the
relevent macros from the add-in directly into your project?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
The code modules are locked in the "Formats & Styles" add-in.
Regards,
Jim Cone




"GS"
wrote in message
...
Since add-ins contain macros, AND the add-in Jim suggested for you has macros to meet your needs,
is there some reason why you can't copy the relevent macros from the add-in directly into your
project?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Jim Cone brought next idea :
Garry,
The code modules are locked in the "Formats & Styles" add-in.
Regards,
Jim Cone




"GS"
wrote in message
...
Since add-ins contain macros, AND the add-in Jim suggested for you has
macros to meet your needs, is there some reason why you can't copy the
relevent macros from the add-in directly into your project?

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Oops! That can be a problem for some folks, I guess!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

What about the other suggestion of mine: "Also, I would extract the name before deleting it"
'---
Jim Cone



"Harrison Tomlinson"
wrote in ...

I changed the styT.Value to styT.Name and the same error was
occurring.

The Add-on would be my first choice but sadly, I can not add it. This
must be accomplished through an excel macro. Any other possible
solutions?

Thanks - HT