maxis
11-07-2017, 02:20 PM
We have an existing copy of an Excel spreadsheet with 10 tabs. One tab is the INPUT tab. On the workbook OPEN event we have a vba subroutine that hides or unhide's the 10 workbook tabs bases on a single value on the INPUT tab (range("year")). It works great as a stand alone spreadsheet. You change the value of year on the INPUT tab, save, close and get back into the spreadsheet and the tabs that should show are visible based on the value of year saved on the INPUT tab.
Here's the issue:
We OPEN this same spreadsheet from MS ACCESS (2003 format running with Office 2016) and populate fields on the INPUT tab from MS ACCESS.
vba code in MS ACCESS that opens Excel WB and writes to INPUT tab of Worksheet
strCurrentField = myExcelWrap.WriteToCell(myExcelWrap.Cell_DateOfDeath, DoD)
strCurrentField = myExcelWrap.WriteToCell(myExcelWrap.Cell_YearOfDeath, year(DoD))
At this point, MS ACCESS is opened to the form whose event opened the spreadsheet, and the spreadsheet is opened with Excel with the INPUT tab populated.
But on the workbook OPEN event, the year value on the INPUT tab is always EMPTY. So the spreadsheet opens with all the tabs visible even though when you look on the INPUT tab, there is a year value in the correct cell. Here's the kicker, once the workbook fully OPENS and displays the workbook, you can run the same vba subroutine (use a macro or a button to run it), and the tabs will hide/unhide based on the value of year on the INPUT tab.
We want the spreadsheet to open from MS ACCESS (populated) with the tabs already hidden/unhidden like it works when the spreadsheet is a stand alone spreadsheet. We've tried saving the spreadsheet thinking MS Access opened the spreadsheet and renamed it, but doesn't really save it until you try to close out. We've tried to activate the INPUT tab, select it, etc. all variations that we can think of. Even vba to copy the data to a new worksheet doesn't bring over the CELL VALUES that were populated by MS ACCESS on the INPUT tab. It's like MS ACCESS is retaining control of the cell values on the INPUT tab until the workbook fully OPENS. Then from there, it acts like the stand alone worksheet and the hide/unhide works great.
Any clues on what EVENT or what "state" the spreadsheet is in when MS ACCESS opened it and knows it has it open? Is there some steps to take in the vba that would make this unsaved Excel spreadsheet INPUT tab values accessible to the Workbook OPEN event?
Does having the INPUT tab HIDDEN create a problem? Tried to unhide it with the same negative results unfortunately. Still won't read the values until after the workbook fully opens.
Just tried this in the WB Open Event
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:03"), "GotoCorSheet"
Basically the workbook fully opened (tabs all displaying - what we don't want) and 3 seconds later runs the subroutine GotoCorSheet and all the tabs that should be hidden are then hidden.
IS THIS A GOOD CLUE? Why does it have to fully open before the values on the INPUT tab are "truly" there for the subroutine to react to them? Does MS ACCESS which opened the spreadsheet have a lock on the INPUT tab?
Any help would be greatly appreciated. Thank you.
Here's the issue:
We OPEN this same spreadsheet from MS ACCESS (2003 format running with Office 2016) and populate fields on the INPUT tab from MS ACCESS.
vba code in MS ACCESS that opens Excel WB and writes to INPUT tab of Worksheet
strCurrentField = myExcelWrap.WriteToCell(myExcelWrap.Cell_DateOfDeath, DoD)
strCurrentField = myExcelWrap.WriteToCell(myExcelWrap.Cell_YearOfDeath, year(DoD))
At this point, MS ACCESS is opened to the form whose event opened the spreadsheet, and the spreadsheet is opened with Excel with the INPUT tab populated.
But on the workbook OPEN event, the year value on the INPUT tab is always EMPTY. So the spreadsheet opens with all the tabs visible even though when you look on the INPUT tab, there is a year value in the correct cell. Here's the kicker, once the workbook fully OPENS and displays the workbook, you can run the same vba subroutine (use a macro or a button to run it), and the tabs will hide/unhide based on the value of year on the INPUT tab.
We want the spreadsheet to open from MS ACCESS (populated) with the tabs already hidden/unhidden like it works when the spreadsheet is a stand alone spreadsheet. We've tried saving the spreadsheet thinking MS Access opened the spreadsheet and renamed it, but doesn't really save it until you try to close out. We've tried to activate the INPUT tab, select it, etc. all variations that we can think of. Even vba to copy the data to a new worksheet doesn't bring over the CELL VALUES that were populated by MS ACCESS on the INPUT tab. It's like MS ACCESS is retaining control of the cell values on the INPUT tab until the workbook fully OPENS. Then from there, it acts like the stand alone worksheet and the hide/unhide works great.
Any clues on what EVENT or what "state" the spreadsheet is in when MS ACCESS opened it and knows it has it open? Is there some steps to take in the vba that would make this unsaved Excel spreadsheet INPUT tab values accessible to the Workbook OPEN event?
Does having the INPUT tab HIDDEN create a problem? Tried to unhide it with the same negative results unfortunately. Still won't read the values until after the workbook fully opens.
Just tried this in the WB Open Event
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:03"), "GotoCorSheet"
Basically the workbook fully opened (tabs all displaying - what we don't want) and 3 seconds later runs the subroutine GotoCorSheet and all the tabs that should be hidden are then hidden.
IS THIS A GOOD CLUE? Why does it have to fully open before the values on the INPUT tab are "truly" there for the subroutine to react to them? Does MS ACCESS which opened the spreadsheet have a lock on the INPUT tab?
Any help would be greatly appreciated. Thank you.