PDA

View Full Version : Using Sendkeys to delete Styles



brettdj
06-10-2004, 08:31 PM
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
06-10-2004, 08:54 PM
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
06-11-2004, 12:48 AM
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
06-11-2004, 01:45 AM
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
06-11-2004, 02:01 AM
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.aspx?scid=kb;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
06-11-2004, 02:15 AM
Thanks Colo,

I'll pass the link on to the questioner

Cheers

Dave

Mark O'Brien
06-11-2004, 01:58 PM
I'll pass the link on to the questioner

Didn't you initiate this thread?

Jacob Hilderbrand
06-11-2004, 02:13 PM
Maybe he asked for someone else? :confused:

Zack Barresse
06-11-2004, 02:48 PM
Didn't you initiate this thread?

Did he do something wrong Mark?

Anne Troy
06-11-2004, 03:06 PM
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
06-11-2004, 03:36 PM
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
06-11-2004, 06:38 PM
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
03-18-2005, 04:17 PM
Colo: Thanks for your link here. It was helpful over here:

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

Colo
03-18-2005, 07:11 PM
All Excel users in the community always welcome! :-)