Advice: Using Sendkeys to delete Styles

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..............

Code:

---

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

---

BUT

Code:

---

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

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.

Code:

---

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.

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.

Code:

---

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

---

Hi Colo,

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

Code:

---

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

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.

Thanks Colo,

I'll pass the link on to the questioner

Cheers

Dave

Quote:

---

Originally Posted by brettdj

I'll pass the link on to the questioner

---

Didn't you initiate this thread?

Maybe he asked for someone else? :confused:

Quote:

---

Originally Posted by Mark O'Brien

Didn't you initiate this thread?

---

Did he do something wrong Mark?

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. :)

Quote:

---

Originally Posted by firefytr

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".

Quote:

---

Originally Posted by 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

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

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

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