+ Reply to Thread
Results 1 to 6 of 6

Event Trigger

  1. #1
    lobo
    Guest

    Event Trigger

    I have a macro, that works, that I want to run on a certain day at a
    certain time, but can't find a way to trigger it.
    Tried
    Application.OnTime
    I have a Class Module with

    Public WithEvents xlApp As Application

    Private Sub Class_Initialize

    Set XL.xlApp = Application

    Module

    Application.OnTime(9:00 AM),"Date_Lock", , = True

    Sub Date_Lock()
    ActiveSheet.Select
    Range("C3:W384").Select
    Range("C349").Activate
    Selection.Locked = True
    Selection.FormulaHidden = True
    ActiveWorkbook.Save
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
    Scenarios:=True
    ActiveSheet.EnableSelection = xlNoSelection
    End Sub

    Date _Lock works when manually run, but won't trigger when I want?


  2. #2
    Tom Ogilvy
    Guest

    Re: Event Trigger

    I don't see any reason to have the Class Module.

    You have to run a macro with the Applicaton.OnTime command to schedule an
    event. See Chip Pearson's page on application.OnTime

    http://www.cpearson.com/excel/ontime.htm

    When excel is closed, any ontime events are lost - your sample code doesn't
    specify a date so it is unclear what a specific date and time means.

    --
    Regards,
    Tom Ogilvy


    "lobo" <mbentler@gmail.com> wrote in message
    news:1134746452.762117.102140@g49g2000cwa.googlegroups.com...
    > I have a macro, that works, that I want to run on a certain day at a
    > certain time, but can't find a way to trigger it.
    > Tried
    > Application.OnTime
    > I have a Class Module with
    >
    > Public WithEvents xlApp As Application
    >
    > Private Sub Class_Initialize
    >
    > Set XL.xlApp = Application
    >
    > Module
    >
    > Application.OnTime(9:00 AM),"Date_Lock", , = True
    >
    > Sub Date_Lock()
    > ActiveSheet.Select
    > Range("C3:W384").Select
    > Range("C349").Activate
    > Selection.Locked = True
    > Selection.FormulaHidden = True
    > ActiveWorkbook.Save
    > ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
    > Scenarios:=True
    > ActiveSheet.EnableSelection = xlNoSelection
    > End Sub
    >
    > Date _Lock works when manually run, but won't trigger when I want?
    >




  3. #3
    Martin Bentler
    Guest

    Re: Event Trigger



    I have a date in mind, but haven't worried about that.
    I am not sure why I have the class module either, a book I have
    suggested that I try having the trigger be the opening of the workbook.

    *** Sent via Developersdex http://www.developersdex.com ***

  4. #4
    Tom Ogilvy
    Guest

    Re: Event Trigger

    If you want to schedule it whenever any workbook is opened, then you would
    make it an application level event and you would need a class module to
    "instantiate" workbook level events. If it is for a specific workbook when
    it is opened, then you would only need to use the workbook_Open event for
    that workbook.

    If you want the event to occur if Excel is open on Jan 15, 2006 at 9 AM
    regardless of what workbook is open, then you would probably use application
    level events. http://www.cpearson.com/excel/appevent.htm
    However, this would depend on Excel being open.

    If you want to be sure it runs, then you might put it in the workbook open
    event for a single workbook and then use Windows scheduler to open excel and
    that workbook on the date and time in question.

    http://www.cpearson.com/excel/events.htm

    --
    Regards,
    Tom Ogilvy


    "Martin Bentler" <mbentler@sympatico.ca> wrote in message
    news:OyF6%23elAGHA.3456@TK2MSFTNGP11.phx.gbl...
    >
    >
    > I have a date in mind, but haven't worried about that.
    > I am not sure why I have the class module either, a book I have
    > suggested that I try having the trigger be the opening of the workbook.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***




  5. #5
    Martin Bentler
    Guest

    Re: Event Trigger



    Ok, I only one a particular workbook to stop working completely, never
    to work again, on January 9, 2006 at 9:00, so I don't need the class
    module.
    OR it could stop working as soon as it is opened on Jan 9, 06. Either is
    good for me.

    *** Sent via Developersdex http://www.developersdex.com ***

  6. #6
    Tom Ogilvy
    Guest

    Re: Event Trigger

    In the workbook_open event of the workbook you could have code like

    In the ThisWorkbook Code Module:

    Private Sub Workbook_Open()
    if date > DateValue("Jan 9, 2006") then
    thisworkbook.close
    end if
    End sub

    However, just disabling macros upon opening or changing the system clock
    will defeat this protection mechanism - besides the fact that the user can
    break into your code and remove it.

    --
    Regards,
    Tom Ogilvy


    "Martin Bentler" <mbentler@sympatico.ca> wrote in message
    news:O0QvT1mAGHA.2156@TK2MSFTNGP11.phx.gbl...
    >
    >
    > Ok, I only one a particular workbook to stop working completely, never
    > to work again, on January 9, 2006 at 9:00, so I don't need the class
    > module.
    > OR it could stop working as soon as it is opened on Jan 9, 06. Either is
    > good for me.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05903 seconds
  • Memory Usage 8,934KB
  • Queries Executed 16 (?)
More Information
Template Usage (30):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_legacy
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./includes/functions_prefix.php 

Hooks Called (44):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1