Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Macro only works in VBA Editor

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-23-04, 14:19
akamp akamp is offline
Registered User
  
Join Date: May 2004
Posts: 9
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
Reply With Quote
  #2 (permalink)  
Old 07-26-04, 07:47
MikeTheBike MikeTheBike is offline
Registered User
  
Join Date: Apr 2004
Location: Derbyshire, UK
Posts: 714
Hi

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


MTB
Reply With Quote
  #3 (permalink)  
Old 08-17-04, 20:02
akamp akamp is offline
Registered User
  
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.
Reply With Quote
  #4 (permalink)  
Old 08-17-04, 21:43
savbill savbill is offline
Registered User
  
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
Reply With Quote
  #5 (permalink)  
Old 08-25-04, 19:44
akamp akamp is offline
Registered User
  
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.
Reply With Quote
  #6 (permalink)  
Old 08-25-04, 20:58
savbill savbill is offline
Registered User
  
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
Reply With Quote
  #7 (permalink)  
Old 10-07-04, 20:14
akamp akamp is offline
Registered User
  
Join Date: May 2004
Posts: 9
Thanks for the reply Bill. I changed the keyboard shortcut and it did the trick.
Reply With Quote
  #8 (permalink)  
Old 10-08-04, 09:42
shades shades is offline
Registered User
  
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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.