Advice: Using Sendkeys to delete Styles

[brettdj]

I wrote a simple loop to delete all styles besides "Normal" from a users workbook. But certain rogue styles caused the loop to crash although the user reported that he could delete them manually.

So I thought simple, I'll use Sendkeys....

The weird thing is that I can't get the delete to work once I raise the style dialog box.

The first style in the dropdown box is "Normal" and all the other styles are above so I use {UP 2} to grab the next style. Then it doesn't work as expected..............

Application.SendKeys ("%OS{UP 2}%M")
'correctly opens up modify for the desired style as does
Application.SendKeys ("%OS{UP 2}{TAB}~")

BUT

Application.SendKeys ("%OS{UP 2}%D")
'does not delete the selected style, nor does
Application.SendKeys ("%OS{UP 2}{TAB 2}~")
' This one opens up merge, even though merge is 3 manual tabs not 2

Any ideas?

Cheers

Dave

[Jacob Hilderbrand]

Send keys are very unreliable. It looks like Delete will not be highlighted with them. You can try to use the D accellerator for Delete.

Application.SendKeys ("%OS{UP 2}%D")
 'does not delete the selected style, nor does
Application.SendKeys ("%OS{UP 2}"), True
Application.SendKeys ("%D"), True
 ' This one opens up merge, even though merge is 3 manual tabs not 2

But it is better to not use send keys at all imo.

[Colo]

Hi Dave, :-)
Now I only have Excel Japanese version, so my code doesn't work on English version.
As for me, I tend to hate sendkeys solution because it sometimes doesn't work as intended. I don't know about the loop code that you wrote...Is it something like this?
I have no idea why it crashed. Give me more details. Thanks.

Sub DeleteStyles()
    Dim wkbStyles As Styles
    Dim s As Style
    Set wkbStyles = ActiveWorkbook.Styles
    For Each s In wkbStyles
    If s.Name <> "Normal" Then s.Delete
    Next
End Sub

[brettdj]

Hi Colo,

Thanks for the reply
Yes, something very similar with error handling for the rogue styles

Sub Kstyle()
Dim sty As Style
On Error Resume Next
    For Each sty In ActiveWorkbook.Styles
    If sty.Name <> "Normal" Then sty.Delete
    Next
End Sub

Apparently it still left too many styles for manual deletion

Cheers

Dave

[Colo]

Dave, ok I got your situation. I just found this at MS web site, but it is very similar way of our code.

How to Programmatically Reset a Workbook to Default Styles
http://support.microsoft.com/default...b;EN-US;247980

So far, I have no idea and I could not appear again your problem, but I'll hanging around on the net and will let you know if I found something wonderful.

[brettdj]

Thanks Colo,

I'll pass the link on to the questioner

Cheers

Dave

[Mark O'Brien]

I'll pass the link on to the questioner

Didn't you initiate this thread?

[Jacob Hilderbrand]

Maybe he asked for someone else?

[Zack Barresse]

Didn't you initiate this thread?

Did he do something wrong Mark?

[Anne Troy]

Dave's (brettdj) in Australia and probably still sleeping. That's all they do over there anyway. Mark O'B: Dave is probably asking for another user where he works is all.

[Mark O'Brien]

Did he do something wrong Mark?

I don't think so:

http://www.vbaexpress.com/forum/faq.php?

It just seemed like a strange comment to make on a thread that you initiate, especially when you start out by stating "I wrote a simple loop".

[brettdj]

I wrote a simple loop to delete all styles besides "Normal" from a users workbook. But certain rogue styles caused the loop to crash although the user reported that he could delete them manually.

a little ambigious maybe......

I was answerering a problem for a third party - which makes it tough as I don't know what is happening with the so called "rogue" styles.

That lead to a question of my own as I've never had a problem with Sendkeys before

The link from Colo is useful although it also loops through styles to delete them so I think the problem will remain

Cheers

Dave

[Anne Troy]

Colo: Thanks for your link here. It was helpful over here:

http://www.vbaexpress.com/forum/showthread.php?t=2379

[Colo]

All Excel users in the community always welcome! :-)