View Full Version : Advice: 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.
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
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
All Excel users in the community always welcome! :-)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.