want all worksheet tabs visible, starting at left

  • Thread starter Thread starter L Mehl
  • Start date Start date
L

L Mehl

The order of the worksheets is:
MainMenu
Its Worksheet_Activate() event says
frmMenuMain.Show
(frmMenuMain contains only buttons to run modules in the application;
cmdCancel hides the menu and displays the Plots tab)
Plots
Data
Instructions
....
At the end of a VBA module which creates a plot, I set focus to the Plots
worksheet with
Sheets("Plots").Select

This also changes the leftmost tab to be the Plots worksheet tab and moves
the MainMenu tab off the screen to the left.

As the code above indicates, showing the MainMenu worksheet (to have its tab
appear as the leftmost tab) displays the form on that worksheet.

Is there some code that will display all tabs starting with MainMenu and
then show the Plots worksheet?

Thanks for any help.

Larry Mehl
 
Larry -

If there's enough room for the tabs to appear, run two lines:

Sheets("MainMenu").Select
Sheets("Plots").Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon --

Thank you for responding.

Your code suggestion would work if "MainMenu"'s Worksheet_Activate() event
did not load frmMainMenu. The form requires clicking one of its buttons,
the result being that another sheet can not be displayed. Only cmdCancel
hides the form.

I received a suggestion of the following code:

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array( _
"MainMenu", _
"Plots", _
"Data_Series", _
"UserConfig", _
"StreamTypes", _
"ColumnInfo", _
"SeriesNames", _
"Data_Dummy", _
"Import_Temp", _
"Documentation", _
"Colors", _
"Copyright")).Select
Sheets("Plots").Activate

Problem: this code, too, leaves MainMenu as the active sheet with
frmMainMenu showing, ready for user input.
If I click cmdCancel on that form,I get the result I want ... MainMenu tab
appearing at the left.

Is there a way in code to "click" a button on the form on the active sheet?

Larry
 
Jon --

If you mean running the procedure which begins with
"ActiveWindow. ..."

Yes, I can run it, and it causes Sheets("Plots"). to be the leftmost visible
tab; the MainMenu tab is further to the left, and is not visible. To see
it, user must cllck the
|< VCR button.

I discovered worksheet MainMenu had "frmMainMenu.Show" as the only line in
its Worksheet Activate event and removed it. Problem remains.

Just to recap, I want to achieve the result of showing all the tabs,
starting from the leftmost (ManiMenu) and have the Plots worksheet
activated.

Thanks for your continued interest.

Larry
 
Back
Top