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...
Bookmarks