Consulting

Results 1 to 14 of 14

Thread: Using Sendkeys to delete Styles

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

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

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

    BUT

    [vba]
    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
    [/vba]

    Any ideas?

    Cheers

    Dave
    Last edited by brettdj; 06-14-2004 at 09:03 PM.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.

    [vba]
    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
    [/vba]

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

  3. #3
    MS Excel MVP VBAX Regular Colo's Avatar
    Joined
    May 2004
    Location
    Kobe, Japan
    Posts
    23
    Location
    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.

    [vba]
    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
    [/vba]

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Hi Colo,

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

    [vba]
    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
    [/vba]

    Apparently it still left too many styles for manual deletion

    Cheers

    Dave

  5. #5
    MS Excel MVP VBAX Regular Colo's Avatar
    Joined
    May 2004
    Location
    Kobe, Japan
    Posts
    23
    Location
    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.

  6. #6
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Thanks Colo,

    I'll pass the link on to the questioner

    Cheers

    Dave

  7. #7
    Quote Originally Posted by brettdj
    I'll pass the link on to the questioner
    Didn't you initiate this thread?

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Maybe he asked for someone else?

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Quote Originally Posted by Mark O'Brien
    Didn't you initiate this thread?
    Did he do something wrong Mark?

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

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

  12. #12
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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
    Last edited by brettdj; 06-11-2004 at 06:48 PM.

  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Colo: Thanks for your link here. It was helpful over here:

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

  14. #14
    MS Excel MVP VBAX Regular Colo's Avatar
    Joined
    May 2004
    Location
    Kobe, Japan
    Posts
    23
    Location
    All Excel users in the community always welcome! :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •