Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 11th, 2007, 09:32 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to run Macro on entry to new sheet?

Good day all,

     I have been struggling to make a macro work latley and wanted to know if any of you could help...

The macro is named macro1, its short key is q....
At the end of the macro it goes to the next sheet (by sendkeys "^PGDN")
So, using sendkeys i have writen the following to have the macro run 10 times:

Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True

......but it runs fine the first time and then when it goes to the next sheet it just stops, no error msg or nothing. Just sits there like as if it did what i wanted it to.

Does anyone know of somekind of delay mechanism or method that can get this to work?

(Im running out of hair here pulling it out!!)






Reply With Quote
  #2 (permalink)  
Old May 11th, 2007, 10:08 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Are you trying to process something outside of Excel? Is the macro trying to rerun itself?
A macro won't rerun itself.

If you're calling a subroutine several times from within excel you should make it a separate sub routine:
-----------------------------------------------
Sub Macro1()

  Dim iCnt As Integer
  For iCnt = 1 to 10
    Call MyRoutine
  Next

End Sub

Private Sub MyRoutine()

  'Desired procedure to repeat here

End Sub
-----------------------------------------------


If you are just going through every sheet in the current workbook:
-----------------------------------------------
Sub Macro1()

  Dim oSheet As Worksheet
   For Each oSheet In ActiveWorkbook.Worksheets
     MsgBox oSheet.Name 'Displays name of each worksheet, your actual code goes here.
   Next

End Sub
-----------------------------------------------

Hope this helps
Reply With Quote
  #3 (permalink)  
Old May 11th, 2007, 12:31 PM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the hand budy...

Yes I am processing something out of excel. I want to extract a ceartain set of files form this external program and insert that data in the worksheets 1-10 by copying the export to clipord and pasting into in the worksheet.

I tried your help and again the same result. It is as if it finishes after the fierst tab?!?!

Any more ideas?

Thanks again.

Reply With Quote
  #4 (permalink)  
Old May 13th, 2007, 04:26 PM
Registered User
 
Join Date: Oct 2006
Location: Leigh, Lancashire, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi there,
I have been looking through and thibnk you are missing little bit of code. From what I can see you are trying to loop through each worksheet then run your macro. The code above is correcty except it is missing the following

Sheets(oSheet.Name).Select

the code should look like

Sub Macro1()

  Dim oSheet As Worksheet
   For Each oSheet In ActiveWorkbook.Worksheets
    Sheets(oSheet.Name).Select

    'Your code goes here.

   Next

End Sub

'============

Has that helped?

Martin
Reply With Quote
  #5 (permalink)  
Old May 16th, 2007, 08:35 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good day...

You guys rock!!!!

Worked just fine. Thanks for takeing the time to help me out, both of you!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible to run macro from another workbook? rmilward Excel VBA 2 September 26th, 2009 08:00 PM
How do i run Macro on Web virall23@yahoo.com Excel VBA 2 April 17th, 2008 01:04 AM
variable sheet for Macro stealthdevil Excel VBA 11 May 11th, 2007 02:30 PM
Macro Doesn't Run? CoryKutchera Excel VBA 3 January 5th, 2005 10:30 AM
Show Sheet after running macro sridevi Excel VBA 2 November 5th, 2004 08:18 AM



All times are GMT -4. The time now is 08:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.