+ Reply to Thread
Results 1 to 3 of 3

VBA unable to move to a different active cell - while running subroutine

  1. #1
    Registered User
    Join Date
    05-24-2011
    Location
    Edmonton Alberta
    MS-Off Ver
    Excel 2003
    Posts
    6

    VBA unable to move to a different active cell - while running subroutine

    Attached is a file that is intended to add a few lines to the PO Line Item Section of a excell worksheet - based upon the Users selection of CHECK Boxes (and other data that they have entered).

    The Majority of this program is geared to "initiate//activate" as the user closes the application - at which time the application looks at check boxes - in order to add/delete information for the Documentation and Material Inspection requirements in the PO Line Item Section the worksheet

    The closing of the application triggers the "Workbook_BeforeClose" which intern jumps to another subroutine "Update_Before_Close" in Module 2

    What is happening - is as it passes through (the FIRST TIME) the Subroutine "Update_Before_Close" in module 2 ----> VBA is able to move around a collect information appropriately (ie -works fine) .....

    HOWEVER

    IF the user has not filled out the Document Matrix Form (for who it the documents will be issued to) - it Exits the subroutine "Update_Before_Close" - Unhides a different worksheet (Matrix) and collects additional information ---> prior to completing the rest of the CLOSE AND SAVE routine ( "Update_Before_Close")


    When Writing in Documents and MTI requiests ---> it SHOULD write to the next available free line.... (and it USED to before I added MATRIX form to fill out)....

    For SOME REASON(?????) it is overwritting existing data starting at row 16....

    I have troubleshot the application and know that ONCE it resumes running "Update_Before_Close" the second time --- the "cursor" is STUCK at C:15
    It is VBA is UNABLE to control the "activecell". Hence all writing is done relative to this location (ideally it should go to the next available free line - the iTemp2 variable)
    ie

    If you put code such as
    Activecell.offset(0,1).activate
    Range("A1").activate
    Range("A:A").select etc
    it does Nothing.... the curser remains at the same C:15 location.

    If you tab over to the form ---> you can use up/down arrows to move the current active location.. you can also "read" which is the active workbook, active worksheet, active cell - so it is not like I can not communicate with the worksheet

    The issue is that I wanted to "find" the bottom of the file - to append appropriately.
    I HAVE created a work around - but capturing the "lastrow" during the first pass of the subroutine "Update_Before_Close" - and storing it in a Public variable so that access this information during the second pass......
    But it still does not address the fact that for all intents and purposes - I have no ability to control curser location during the "second pass" of this subroutine....

    To illustrate.... check a couple "checkboxes" - and close the application....
    you need to "step" through the application (jumping loops to save time) ---->
    on the Second Pass of "Update_Before_Close" - attempt to move the cursur/mouse/activecell (whatever you want to call it).... Nothing! Never experienced anything this before


    I have "limited" error handling - but no errors are being "detected"...

    I have NO idea what I am doing wrong
    (my workaround is Weak - but does keep me "functional"

    Thanks for any assistance // insight on what I have done wrong...
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-24-2011
    Location
    Edmonton Alberta
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA unable to move to a different active cell - while running subroutine (???) HE

    I am still guessing for what is going on....

    The ONLY thing that comes to mind - is possibly due to the fact that the "Update_Before_Close" is a Function (rather than a sub routine ) - and I am exiting prior to returning the specified value.

    I will attempt to rewrite this as a Sub (using a Public variable to share the specified value). This is probably a complete long shot - but have run out of ideas on why excel is behaving the way it is

    Any insight would be appreciated
    Thanks

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA unable to move to a different active cell - while running subroutine

    Hello Mykal,

    This may help or confound you further. Have a look here to learn more about the Workbook_BeforeClose() event and some of the problems it can cause.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1