Forum Discussion
Solved
Need to keep the first sheet tab visible
Hello Excellers,
We have a workbook with 55 worksheets each does several things.
Now obviously there is no screen in the world big enough to display all 55 worksheet tabs. Or at least I am not aware of such a large screen. So...
Best next thing is that we can display 16 worksheet tabs in a row on our current screen size.
What I need is some VBA code to that would keep the first (the first sheet tab from the left) sheet tab visible at least any time these 16 sheets are activated, the sheet tabs are scrolled so that sheet(1) is displayed. After the 16th sheet tab... oh well can't do a thing about that...
I have shortened the worksheet names so much that now they are so cryptic one needs a list to know the actual name.
I have tried some Worksheet_change event code to select the first sheet then select the target sheet but that did not work at all.
Any help will be appreciated.
GiGi
Thank You mathetes
This sounds like it could well be that we/you should first consider a re-design of the whole workbook.
Believe me, I am working with a hugely modified workbook, down from about 199 original worksheets that were doing incremental steps one step per worksheet. The workbook was prone to corruption and took several minutes to load, even with all the turning off of calculations, and refresh and all that. It was massive. We cut it down to a someone manageable size.
Is there any way you could share the workbook itself?
This workbook gets data from manufacturing machinery log files so it would contain many proprietary information. Oh by the way, the earliest version of this daily workbook was designed an run in MS-Excel 5.0c back in the 1990s, and it had received many upgrades but now... its still a mess.
could you at the very least give some descriptions of what these 55 sheets/tabs do that you or your organization have thought "requires" they be individual sheets?
The first sheet, which is the target sheet is a dashboard with several buttons that would run the code to do the work. About 35 of these sheets are data logs from machines, things like task start time, task function, task end time,. and any error codes along the way. These 35 sheets were supposed to be saved as CSV files and the VBA code will open the csv file search for the data point needed, copy it and "marry" it to other data points from other machines. For example if Machine A started a task at 00:00:00 and finished that task at 00:02:00 (two minutes after) then we need Machine B task that was started at 00:02:00 to see the full continuation of the task being manufactured. So Task on machine A now goes to machine B at 00:02:00 and machine B completes the task at say 00:07:00 and sends the task to machine C then we need the log of machine C at 00:07:00 and this goes on until one condition is met, A) task completed on time, B) task failed on one of the machines, or C) task interrupted and that also has several codes to go with it. The purpose of this workbook is to manage quality control and productivity.
I split the two QA and productivity into two two workbooks, and that cut down on many what turned out to be redundant data.
We are still trying to convince management that the raw data should NOT be in the workbook engine, but read as needed from the CSV files. We are still hopeful they will agree.
Other sheets contain data about personnel, shift start time, and what machine they are working on for what time and break times and things like that. This is needed to manage floor activities.
a well designed database could handle all those actions/transactions for all the departments (etc) in a single table....
Yes we brought in a consultant to help us transition into a SQL Server database, with some MS-Excel code but the biggest hurdle is that the workbook was designed by the Big Boss and its his legacy so to speak. We made many arguments that Excel is not meant to handle such large data sets and yet all he sees is his "baby" being decommissioned!!! In fact the CFO showed by the numbers that it is hurting the organization, but... its his "Baby"
So before we turn to VBA for more systematic symptomatic relief, let's consider a more complete diagnosis first.
I will show your reply as an outside impartial observer and hope for the best!
GiGi!
5 Replies
Best wishes. That history explains a lot. And yes, a SQL server database may indeed end up being the surgery needed..... first, though, the patient (AKA the Big Boxx) needs to provide his consent
This sounds like it could well be that we/you should first consider a re-design of the whole workbook. Shortening names and changing views is just dealing with symptomatic relief.
Is there any way you could share the workbook itself? If it's all confidential data and can't be shared (which is understandable), could you at the very least give some descriptions of what these 55 sheets/tabs do that you or your organization have thought "requires" they be individual sheets?
It's not unheard of for people to create individual sheets to represent, say, comparable activities in different locations or departments or different whatevers....when in fact a well designed database could handle all those actions/transactions for all the departments (etc) in a single table....
So before we turn to VBA for more systematic symptomatic relief, let's consider a more complete diagnosis first.
Thank You mathetes
This sounds like it could well be that we/you should first consider a re-design of the whole workbook.
Believe me, I am working with a hugely modified workbook, down from about 199 original worksheets that were doing incremental steps one step per worksheet. The workbook was prone to corruption and took several minutes to load, even with all the turning off of calculations, and refresh and all that. It was massive. We cut it down to a someone manageable size.
Is there any way you could share the workbook itself?
This workbook gets data from manufacturing machinery log files so it would contain many proprietary information. Oh by the way, the earliest version of this daily workbook was designed an run in MS-Excel 5.0c back in the 1990s, and it had received many upgrades but now... its still a mess.
could you at the very least give some descriptions of what these 55 sheets/tabs do that you or your organization have thought "requires" they be individual sheets?
The first sheet, which is the target sheet is a dashboard with several buttons that would run the code to do the work. About 35 of these sheets are data logs from machines, things like task start time, task function, task end time,. and any error codes along the way. These 35 sheets were supposed to be saved as CSV files and the VBA code will open the csv file search for the data point needed, copy it and "marry" it to other data points from other machines. For example if Machine A started a task at 00:00:00 and finished that task at 00:02:00 (two minutes after) then we need Machine B task that was started at 00:02:00 to see the full continuation of the task being manufactured. So Task on machine A now goes to machine B at 00:02:00 and machine B completes the task at say 00:07:00 and sends the task to machine C then we need the log of machine C at 00:07:00 and this goes on until one condition is met, A) task completed on time, B) task failed on one of the machines, or C) task interrupted and that also has several codes to go with it. The purpose of this workbook is to manage quality control and productivity.
I split the two QA and productivity into two two workbooks, and that cut down on many what turned out to be redundant data.
We are still trying to convince management that the raw data should NOT be in the workbook engine, but read as needed from the CSV files. We are still hopeful they will agree.
Other sheets contain data about personnel, shift start time, and what machine they are working on for what time and break times and things like that. This is needed to manage floor activities.
a well designed database could handle all those actions/transactions for all the departments (etc) in a single table....
Yes we brought in a consultant to help us transition into a SQL Server database, with some MS-Excel code but the biggest hurdle is that the workbook was designed by the Big Boss and its his legacy so to speak. We made many arguments that Excel is not meant to handle such large data sets and yet all he sees is his "baby" being decommissioned!!! In fact the CFO showed by the numbers that it is hurting the organization, but... its his "Baby"
So before we turn to VBA for more systematic symptomatic relief, let's consider a more complete diagnosis first.
I will show your reply as an outside impartial observer and hope for the best!
GiGi!
I agree with mathetes suggestion above to consider if there is a better way to structure the workbook. For Example: i have seen many people set workbooks up with 12 sheets for the months or 52 sheets for the weeks of the year but instead many of those cases would be better off with 1 sheet with ongoing data and then a 'report' template that would query the data for a particular range and show it in a particular desired format.
That said another option is to put a 'HOME' button on each sheet. You can paste any clipart and then Insert -> Link and then you can set it to 'Sheet1'!A1 or whatever you need. Then you don't care if that Tab is visible since you can easily link there.
Lastly a little tip, if you right click on the left/right arrows used to scroll through the tabs will bring up a menu navigation box to quickly select and goto whichever sheet you need.
