Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: Macro only works in VBA Editor

    When using the keyboard shortcut or going to Tools|Macros the following script does not work. When in the VBA editor, the macro runs properly. Any ideas on this? I shortened some of the filenames/locations to make things easier.

    Thanks!

    Code:
    Sub Filming()   
        Workbooks.Open Filename:="...Medco Filming.xls"
        Range("A1:E76").Select
        Selection.ClearContents
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Windows("Medco Groups.xls").Activate
        ActiveWindow.WindowState = xlNormal
        Selection.AutoFilter Field:=1, Criteria1:="<>"
        Range("C1:E53").Select
        Selection.Copy
        Windows("Medco Filming.xls").Activate
        Range("A1").Select
        ActiveSheet.Paste
        Columns("A:A").EntireColumn.AutoFit
        Columns("B:B").EntireColumn.AutoFit
        Columns("C:C").ColumnWidth = 36
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Could you indicate the erro that occurs and on which line ?


    MTB

  3. #3
    Join Date
    May 2004
    Posts
    9
    No errors are generated it simply does not finish completing the tasks. It stops when the "Medco Groups.xls" file is opened (Line 12).

    It's VERY strange that it works fine from the VBA editor and not through a shortcut or the Macro dialog.

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by akamp
    No errors are generated it simply does not finish completing the tasks. It stops when the "Medco Groups.xls" file is opened (Line 12).

    It's VERY strange that it works fine from the VBA editor and not through a shortcut or the Macro dialog.
    How are you activating Sub Filming()?

    I'd change: 'Windows("Medco Filming.xls").Activate' and 'Windows("Medco Groups.xls").Activate'

    To: 'Workbooks("Medco Filming.xls").Activate' and 'Workbooks("Medco Groups.xls").Activate'

    You are trying to act on a selection but have not made a selection.
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    This Line Should be preceeded by a Selection call, Range("A1").Select
    ~

    Bill

  5. #5
    Join Date
    May 2004
    Posts
    9
    Quote Originally Posted by savbill
    How are you activating Sub Filming()?

    I'd change: 'Windows("Medco Filming.xls").Activate' and 'Windows("Medco Groups.xls").Activate'

    To: 'Workbooks("Medco Filming.xls").Activate' and 'Workbooks("Medco Groups.xls").Activate'

    You are trying to act on a selection but have not made a selection.
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    This Line Should be preceeded by a Selection call, Range("A1").Select
    I'm activating the macro with Shift+Ctrl+F, and that is the only time it does not work.

    Going to Tools | Macros and highlighting 'Filming' and then selecting Run works flawlessly. I made your suggested changes and it still does not function correctly using the keyboard shortcut.

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by akamp
    I'm activating the macro with Shift+Ctrl+F, and that is the only time it does not work.

    Going to Tools | Macros and highlighting 'Filming' and then selecting Run works flawlessly. I made your suggested changes and it still does not function correctly using the keyboard shortcut.
    "Shift+Ctrl+F" is the default combination to set focus on the Font Tool control. I'm not sure if setting a defined shortcut for your macro takes precedence over a default setting??? It would be easy to test just try setting a different shortcut combination. Check it before setting to make sure it's not a default first. Check Excel Help 'F1' for Keyboard Shortcuts to see a list.
    ~

    Bill

  7. #7
    Join Date
    May 2004
    Posts
    9
    Thanks for the reply Bill. I changed the keyboard shortcut and it did the trick.

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Just as a note, it is more efficient for Excel not to "select"; and you can combine some of the lines. Here is an abbreviated improvement

    Code:
    Sub Filming()   
        Workbooks.Open Filename:="...Medco Filming.xls"
     Sub test()
        With Range("A1:E76")
            .ClearContents
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With
        '    Combine the two Autofit into one
        Columns("A:B").EntireColumn.AutoFit
        '    Columns("A:A").EntireColumn.AutoFit
        '    Columns("B:B").EntireColumn.AutoFit
        Columns("C:C").ColumnWidth = 36
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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