Solved

Option to time-delay auto open and open worksheets (not the VBA code) for editing

Posted on 2006-06-26
16
4,068 Views
Last Modified: 2013-11-13
I have a fully automated macro running under This Workbook so it runs when the Workbook is opened.  The workbook is opened by call from the XP Task Scheduler and runs at 3:00 am weekdays.  It runs perfectly, does its stuff, then closes and quits Excel.  I want to give the clerk the opportunity to open the workbook, make edits to the static data in it, then save and close - without running the VBA code or having to hold the Shift key down when opening the file to prevent the auto run from firing.  She should be able to click on the workbook's shortcut on her desktop, have the auto run on timed delay, give her 10 seconds to click on a message box and open the sheet for edit, or, when it runs at 3:00 am, it will execute automatically after 10 seconds.  Ideally, I'd like the edit option, if she takes it, to follow with menus asking what part number, etc., she wants to add/delete/change, and then do it and gracefully close.

Is it possible and/or straight-forward to add this functionality in my VBA code?  Getting the auto run code to do the multitude of gymnastics it does was phase one.  Now that it's running, the client wants to have this edit option available on demand, and like all clients, they're in a hurry.  Thanks for the EXPERT help, folks.  EE is awesome!
0
Comment
Question by:glennes
  • 10
  • 6
16 Comments
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Hi glennes,

The easiest way I can see to do this would be to create a UserForm that has the delay on it (as regular message boxes cant disappear without user intervention).

I've created an example for you, you can find it at http://ee.hastalavidas.com/Q_21899590/MacroTimerExample.xls

However, in case that file does not exist at a later date, I'll explain what I did.

The following is in the ThisWorkbook object of the timer example file:

Private Sub WorkbookOpen() 'Change existing Workbook_Open sub to this WorkbookOpen
 'your code
 MsgBox "This is your WorkbookOpen subroutine"
End Sub

Private Sub Workbook_Open() 'after changing existing to WorkbookOpen, create this
 MacroTimer.Show
End Sub

Change your existing Workbook_Open event to WorkbookOpen, then paste in the Workbook_Open event there (to call the userform).


Next, insert a userform into the project, and change the (name) to "MacroTimer" (no quotes).  I changed the caption property to "Disable Auto Macros" as a title for our new message box, feel free to change this as you see fit.
Next, I added two command buttons to the userform.
 The first has a (Name) of   vEditButton    and the second has a (Name) of   vAutoRunButton
The caption for vEditButton is "EDIT WORKBOOK", and the caption of vAutoRunButton is "Auto run macros".  If you decide to change the wording for vAutoRunButton, you'll need to make another change later (in the UserForm_Initialize event in the userform, and in the UpdateCounter sub in the module).

In the codepane for this userform, paste in the following:

Private Sub UserForm_Initialize()
 MacroTimerModule.YesProceed = True
 MacroTimerModule.TimeCounter = MacroTimerModule.TimeDelay
 vAutoRunButton.Caption = "Auto run macros" & vbLf & _
  "(" & MacroTimerModule.TimeCounter & ")"
 NextSched = SetTimer(0, 0, 1000, AddressOf MacroTimerModule.UpdateCounter)
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 MacroTimerModule.YesProceed = False
 MacroTimerModule.TimeIsUp
End Sub

Private Sub vAutoRunButton_Click()
 MacroTimerModule.TimeIsUp
End Sub

Private Sub vEditButton_Click()
 MacroTimerModule.YesProceed = False
 MacroTimerModule.TimeIsUp
End Sub


Finally, add a new standard module, and change the (Name) to   MacroTimerModule

Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent _
 As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long, ByVal nIDEvent _
 As Long) As Long
Public NextSched As Long
Public YesProceed As Boolean
Public TimeCounter As Long
Public Const TimeDelay As Long = 10 'Seconds to wait

Sub TimeIsUp()
 KillTimer 0&, NextSched
 Unload MacroTimer
 If YesProceed Then Application.Run "ThisWorkbook.WorkbookOpen"
End Sub

Sub UpdateCounter(ByVal hWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, _
  ByVal dwTimer As Long)
 TimeCounter = TimeCounter - 1
 If TimeCounter > 0 Then
  MacroTimer.vAutoRunButton.Caption = "Auto run macros" & vbLf & "(" & TimeCounter & ")"
 Else
  TimeIsUp
 End If
End Sub


If you want to change the delay on this from 10 seconds to something else, modify this line at the top of the module:
Public Const TimeDelay As Long = 10 'Seconds to wait


SAVE THE WORKBOOK!
Then, either manually start the userform (either by running workbook_open or pressing F5 when the userform has focus) or close/reopen the workbook to test this out.
The Timer APIs being used can be a little touchy, so if you want to make changes to anything, make sure ALL open workbooks are saved before testing anything, as if the API doesn't like something it will crash all of excel.  However once it is working fine there are no issues with the API.  Just a word of caution.


Let me know if it isn't perfect!
Matt
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
By the way, this can be used on multiple workbooks, providing you copy over the Form and the Module.  If you want the code to call something besides "WorkbookOpen", change this line in TimeIsUp

 If YesProceed Then Application.Run "ThisWorkbook.WorkbookOpen"

To call whatever subroutine you wish.
0
 

Author Comment

by:glennes
Comment Utility
Hey Matt...
Thanks so much for your quick reply!  I will be working on this program some tonight and tomorrow, and will put your code through the paces.  It looks as though you've addressed my question very well.  I haven't done any UserForm work yet (I'm still pretty new at VBA!), but I've got EE and a few reference books, so I've at least got enough to give it a start.  Your logic makes sense.  I guess since I'm fairly unaware of the uses of UserForm I didn't give that angle consideration, but it does make good sense to approach it that way.  I'll let you know tomorrow how it goes.  I'll give you 250 as a deposit! Thanks again, I'll be back to you.
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
I'll be here if/when you need any help adding this to your project.  Take your time, I'm not going anywhere :)
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Ok, I did find one error in what I added at the last moment (of course)

In the UserForm code, the UserForm_QueryClose event should be:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = 0 Then
  MacroTimerModule.YesProceed = False
  MacroTimerModule.TimeIsUp
 End If
End Sub

What I added was the If block.  I added this in case your user hit the X in the top right of the userform (to cancel the automatically run macros), what I forgot about was that this event is also called when the userform is unloaded from memory (with a different CloseMode value).  Testing it as I have here will make sure the autorun actually works.  
Sorry about that!
Matt
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
I have updated the sample file with the new code.
0
 

Author Comment

by:glennes
Comment Utility
Thanks, Matt...I'm just now sitting down to work with your code.  I'll update you in a little while!
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Take your time, I'm having a lot of fun with this one. I'm making a couple more minor changes for greater compatability with other things, and easier use (you won't need to change your workbook_open macro much, not like I describe above).  The above will still work though, just trying to make it easier.  I'll let you know as well
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Ok, I have created it as an add-in as well.  In your case you'll probably still want to adapt your workbook to include this (so your end-user wont have to have the add-in installed), but others can install the add-in.  As long as the add-in is referenced from within the VBAProject you want to use it with, it will work great (the module in the add-in has an example sub as well).

I have made some other changes as well to make it a little easier, so I'll include fully what I've done here, as it is better than the above instructions.

The userform setup is still the same, however the text on the AutoRun button is now set from within the module, so as long as the button has the correct (name) you can set it to whatever you want initially, it will change.  Here is all the code:

'***** BEGIN MacroTimer UserForm CODE *****
Option Explicit
Private Sub UserForm_Initialize()
    MacroTimerModule.YesProceed = True
    MacroTimerModule.TimeCounter = MacroTimerModule.TimeDelay
    vAutoRunButton.Caption = AutoRunButtonCaption & vbLf & _
        "(" & MacroTimerModule.TimeCounter & ")"
    NextSched = SetTimer(0, 0, 1000, AddressOf MacroTimerModule.UpdateCounter)
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        MacroTimerModule.YesProceed = False
        MacroTimerModule.TimeIsUp
    End If
End Sub
Private Sub vAutoRunButton_Click()
    MacroTimerModule.TimeIsUp
End Sub
Private Sub vEditButton_Click()
    MacroTimerModule.YesProceed = False
    MacroTimerModule.TimeIsUp
End Sub
'***** END MacroTimer UserForm CODE *****

'***** BEGIN MacroTimerModule CODE *****
Option Explicit
Public Declare Function SetTimer Lib "user32" (ByVal hWnd As Long, _
  ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc _
  As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hWnd As Long _
  , ByVal nIDEvent As Long) As Long
Public NextSched As Long
Public YesProceed As Boolean
Public TimeCounter As Long
Public Const TimeDelay As Long = 10 'Seconds to wait
Public Const AutoRunButtonCaption As String = "Auto run macros"
Sub TimeIsUp(Optional ByVal PointlessVariableToHideThisSub As Boolean)
    KillTimer 0&, NextSched
    Unload MacroTimer
'** Use the next line if you don't want to just return to calling sub **
'    If YesProceed Then Application.Run "ModuleName.MacroName"
End Sub
Sub UpdateCounter(ByVal hWnd As Long, ByVal uMsg As Long, ByVal _
  nIDEvent As Long, ByVal dwTimer As Long)
    TimeCounter = TimeCounter - 1
    If TimeCounter > 0 Then
        MacroTimer.vAutoRunButton.Caption = AutoRunButtonCaption & vbLf _
            & "(" & TimeCounter & ")"
    Else
        TimeIsUp
    End If
End Sub
Sub Timer_Example()
'To use this with another workbook, set a reference in VBA to
' this add-in, and paste the first two lines at the top of the
' subroutine you want the delay in
    MacroTimer.Show
    If Not MacroTimerModule.YesProceed Then Exit Sub
'** CODE AFTER THIS LINE WILL NOT RUN IF USER CHOOSES TO EDIT **
    MsgBox "This will not be seen if user wants to edit the workbook"
End Sub
'***** END MacroTimerModule CODE *****

'***** BEGIN ThisWorkbook CODE *****
Option Explicit
Private Sub Workbook_Open()
'To use this with another workbook, set a reference in VBA to
' this add-in, and paste the first two lines at the top of the
' subroutine you want the delay in
    MacroTimer.Show
    If Not MacroTimerModule.YesProceed Then Exit Sub
'** CODE AFTER THIS LINE WILL NOT RUN IF USER CHOOSES TO EDIT **
    MsgBox "This will not be seen if user wants to edit the workbook"
End Sub
'***** END ThisWorkbook CODE *****

If you look at the code for ThisWorkbook, you no longer have to rename your existing workbook_open, just add those two lines at the top to call the delay userform.  Should make things much easier that way.

You can see both the workbook example and the addin at:
http://ee.hastalavidas.com/Q_21899590/Q_21899590.asp

Matt
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
(not that it matters) I have incorrect comments at the top of that Workbook_open code.. the initial 3 lines of comments should in fact be:
'To use the timer, paste the first two lines at the
' top of the subroutine you want the delay in
0
 

Author Comment

by:glennes
Comment Utility
Hey Matt...
The code works fine when I click the EDIT WORKSHEET button on the UserForm.  When I either let the timer run out or click on Auto Run Macros, the code stops at the line
If YesProceed Then Application.Run "ThisWorkbook.WorkbookOpen"
in your sub
Sub TimeIsUp()

I copied and pasted your code, but checked it to be sure it is exactly like yours, and it is.

What might be causing this?

Thanks!
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
I'm not sure which version of my code you're using.. in my most recent one I commented that out (but left it there to show how it can be called, and changed it to ModuleName.MacroName as an example).

If you're using the older version, do you in fact have a subroutine called "WorkbookOpen" in your ThisWorkbook object?
0
 

Author Comment

by:glennes
Comment Utility
I had not seen your latest code when I sent that message.  I'm now working with your revision...will let you know soon!
I'm glad you're having fun with this one.  (I sure am!) You've already earned your 500.  I wish there were a way to add 500 more...
0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
Comment Utility
Points are unimportant (if you look at my profile you'll notice I have plenty), I'd have done the same for 20 points here.  Fun stuff!
0
 

Author Comment

by:glennes
Comment Utility
Your code now works perfectly!  It's my code that follows it that doesn't work right now (because I'm a novice.  I'm sure I've mis-coded something!)
Here is my ThisWorkbook code:
***********************************
Private Sub Workbook_Open()
MacroTimer.Show
If Not MacroTimerModule.YesProceed Then Exit Sub

'Call the macro to import daily data
ThisWorkbook.Worksheets("TME").Activate
Call Sheet6.sub1                                    MATT:  My code is stopping on this line

'Call the macro to import 7-day data
ThisWorkbook.Worksheets("7Day").Activate
Call Sheet8.sub2

'Call the macro to create the shared copy of the Top 20
Call Module2.Sub5

Application.EnableEvents = True
Application.ScreenUpdating = True

'Save a backup copy of the master workbook.
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "c:\ALL CELLS TOP TWENTY BACKUP"
Sheets("TOPMOVERS").Select
    Range("A1").Select
Application.DisplayAlerts = True

'Clear all open instances of Excel
Set xlApp = Nothing

'Exit the Excel application
Application.Quit

End Sub
***********************************
In my Module 2 Sub 5, I'm creating a New Workbook, naming tabs, and pasting values only to it.  This is to allow users (production planners) to see the daily numbers without opening the master workbook with the formulas and macros in it.  The new workbook has no macros.  This has been executing just right, except that when my code saves it as a new sheet (Top 20 Daily.xls) it makes it Read Only and Shared.  I only want it to be Shared.  They have to be able to write on it as they add daily columns for their own use.  Here is the relevant part of the code from Module 2 Sub 5:
***********************************
Sub Sub5()
Application.EnableEvents = False
Application.DisplayAlerts = False
'Erase yesterday's shared copy of the Top 20
Kill "d:\All Cells Top Twenty\Top 20 Daily.xls"

'Create a new blank sheet for today's Top 20 data
Set NewBook = Workbooks.Add
 
 With NewBook
     .Title = "Top 20 Daily"
     .Subject = "Top20"
     Worksheets.Add
     ActiveSheet.Name = "TOPMOVERS"
     Worksheets.Add after:=Worksheets("TOPMOVERS")
     ActiveSheet.Name = "1279"
     Worksheets.Add after:=Worksheets("1279")
     ActiveSheet.Name = "Line 6"
     Worksheets.Add after:=Worksheets("Line 6")
     ActiveSheet.Name = "Lines 2,3 & 7"
     Worksheets.Add after:=Worksheets("Lines 2,3 & 7")
     ActiveSheet.Name = "Line 1"
     Worksheets.Add after:=Worksheets("Line 1")
     ActiveSheet.Name = "Job Shop"
     Worksheets.Add after:=Worksheets("Job Shop")
     ActiveSheet.Name = "Electrics"
     Worksheets.Add after:=Worksheets("Electrics")
     ActiveSheet.Name = "Trim"
     Worksheets.Add after:=Worksheets("Trim")
     ActiveSheet.Name = "Purchased"
     Sheets("Sheet1").Delete
     Sheets("Sheet2").Delete
     Sheets("Sheet3").Delete
     
     .SaveAs Filename:="d:\All Cells Top Twenty\Top 20 Daily.xls"
 
 End With
**************************
At this point in the module, I do copying and pasting to each sheet, repeating the same stuff, just changing the sheet names.  Below is the code for the last sheet, followed by the closing lines of the module:
**************************
Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
    Sheets("Purchased").Select
    Sheets("Purchased").UsedRange.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Top 20 Daily.xls").Activate
    Sheets("Purchased").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Top 20 Daily.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("Top 20 All Cells Processing Program v1.0.xls").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Top 20 Daily.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$J$67"
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        Sheets("Purchased").PrintOut
    End With
    Range("A1").Select
    Sheets("TOPMOVERS").Select
    Range("A1").Select

'Save the daily copy of the shared Top 20 in the public directory
    ActiveWorkbook.SaveAs "a:\All Cells Top Twenty\Top 20 Daily.xls", , , False, , , xlShared
    ActiveWorkbook.Close
   
'Return the active printer to the default active printer
Application.ActivePrinter = defaultprinter
Application.Calculation = xlCalculationAutomatic

'Return control to ThisWorkbook sub
End Sub
***************************
Note I'm saving/opening, etc., the Excel files by their name using their path, which is consistent with the drive mapping on the clerk's PC.  Can I use, instead, a path reference with the UNC location, such as:
Instead of "a:\All Cells Top Twenty\Top 20 Daily.xls", write it as "\\hagerco\mcdex01\All Cells Top Twenty\Top 20 Daily.xls"?  Do I have to choose the mapped path or the UNC path and use the chosen method exclusively throughout the VBA code?  I'd like to be able to load the master program on whatever PC the client wants without having to go back into the code and manually change the mapped drive paths.

Thanks so very much for indulging a 'newbie' to all this!
0
 

Author Comment

by:glennes
Comment Utility
Matt, your help has been awesome on this!  You definitely earned your 500 points!  I'm so glad you were able to help me out so well!  Thanks again!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sumHeights  challenge 17 59
noX challenge 17 75
nestparen challenge 4 55
eclipse package explorer vs project explorer view 2 27
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now