Thread: 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...

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