I have been working with Simon on this particular issue. I have a
workbook that was created just for our company. I am just trying to
clean it up. One of the things i did was create buttons on the 8 tabs;
these button will take you to the worksheets that i have hidden. Once
you click on that button it will hide that sheet you were just on but
bring up the new sheet you want to now view. The problem is when i
reopen the workbook the tabs appear and in order to make my buttons work
i have to rehide the tabs. I was given a good code but with not being
able to see the rest of the workbook and other codes i am now getting
erros. Please let me know if you can help me with this. Thank you in
advance Brenda


+-------------------------------------------------------------------+
|Filename: Geminator Clean Version 2.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=11|
+-------------------------------------------------------------------+

--
broop
------------------------------------------------------------------------
broop's Profile: http://www.thecodecage.com/forumz/member.php?userid=26
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

You can't do it without code. In the VBA Editor, go to the
ThisWorkbook module and enter the following code:

Private Sub Workbook_Open()
' display the sheets that should be visible at startup
Application.ScreenUpdating = False
Me.Worksheets("VSheet1").Visible = True
Me.Worksheets("VSheet2").Visible = True
' and so on

' hide the sheets that should be hidden at startup
Me.Worksheets("HSheet1").Visible = xlVeryHidden
Me.Worksheets("HSheet2").Visible = xlVeryHidden
' and so on
Application.ScreenUpdating = True
End Sub

In this code, change the sheet names "VSheet1" and "VSheet2" and so on
to the names of the sheets that you want visible when the workbook is
opened. Change the sheet names "HSheet1" and "HSheet2" and so on
to the sheets that you want hidden when the workbook opens. This
will restore the sheet visibility to a consistent start up state. Note
that there must always be one sheet visible at all times, so be sure
you make sheets visible before hiding the other sheets.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 11 Nov 2008 17:47:24 +0000, broop
wrote:


I have been working with Simon on this particular issue. I have a
workbook that was created just for our company. I am just trying to
clean it up. One of the things i did was create buttons on the 8 tabs;
these button will take you to the worksheets that i have hidden. Once
you click on that button it will hide that sheet you were just on but
bring up the new sheet you want to now view. The problem is when i
reopen the workbook the tabs appear and in order to make my buttons work
i have to rehide the tabs. I was given a good code but with not being
able to see the rest of the workbook and other codes i am now getting
erros. Please let me know if you can help me with this. Thank you in
advance Brenda


+-------------------------------------------------------------------+
|Filename: Geminator Clean Version 2.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=11|
+-------------------------------------------------------------------+

Great I will try this as well. Much appreciated. I didn't realize before
i posted the workbook that whomever gets this workbook program on their
computer they have to give me their computer code so that i can enter it
and give them a registration code to enter, in order for them to view.
I didn't create this program so i didn't remember that part.


--
broop
------------------------------------------------------------------------
broop's Profile: http://www.thecodecage.com/forumz/member.php?userid=26
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

Hi,

I understand that the sheets are visible when you open the workbook and you
want them hidden, Chip took care of that. My concern is "I get errors" - why
would you get errors if the sheets were already visible and you clicked a
button to make them visible - that should not cause an error, it should do
nothing. What error are you getting and why?

--
Thanks,
Shane Devenshire


"broop" wrote:


I have been working with Simon on this particular issue. I have a
workbook that was created just for our company. I am just trying to
clean it up. One of the things i did was create buttons on the 8 tabs;
these button will take you to the worksheets that i have hidden. Once
you click on that button it will hide that sheet you were just on but
bring up the new sheet you want to now view. The problem is when i
reopen the workbook the tabs appear and in order to make my buttons work
i have to rehide the tabs. I was given a good code but with not being
able to see the rest of the workbook and other codes i am now getting
erros. Please let me know if you can help me with this. Thank you in
advance Brenda


+-------------------------------------------------------------------+
|Filename: Geminator Clean Version 2.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=11|
+-------------------------------------------------------------------+

--
broop
------------------------------------------------------------------------
broop's Profile: http://www.thecodecage.com/forumz/member.php?userid=26
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

Here we go, when you open the workbook you should find a new menu item
(if xl2007 it will show in add-ins) when you click this a userform
appears where you can select your next destination, upon selection you
get transported to that sheet, only the instruction sheet and the
destination sheet should remain open.

Any problems let me know!


+-------------------------------------------------------------------+
|Filename: Geminator Clean Version.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=15|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

Bug fixed...all works well now!


+-------------------------------------------------------------------+
|Filename: Geminator Clean Version.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=16|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

Simon, so far so good. I need to go in now and change colors etc. like
on the one previously. I have unprotected the workbook and the page that
I am but it isn't letting me still makes changes. Where do i need to go
to unprotect sheets so that i can clean it up? I will also need to
change colors etc. on the tabs that remain hidden. YOu did a great job
on this...Did you go in and view the orginal one that i sent to you? Do
you personally think that the one button looks and appeals better than
having a button on each page? I would say yeah since that is what you
did. I will wait to hear back from you. What did you think of this
program?:)


Simon Lloyd;117459 Wrote:
Bug fixed...all works well now!


--
broop
------------------------------------------------------------------------
broop's Profile: http://www.thecodecage.com/forumz/member.php?userid=26
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107

Just put the code below in a module and run it, the workbook will be
unprotected and all the sheets will be unprotected and visible, just
remember to change "your password" to your actual password for the
workbook.

Like i said about the button on every page, where would you put it so
it is always in view and not obscuring data?, however you can go ahead
and create a button on every page and in the commandbuttonx_Click event
you can use Call SheetJumpForm to get to the sheet selection.

Comments on the workbook: do you really need that many sheets available
to the customer?, it looks like it could prove confusing and difficult
to manage. Although the workbook is saved upon close you make no
provision for a back up so should the workbook be left open and the pc
switched off or there is a crash then all new data to the workbook will
be lost, just a thought as its you that will have to maintain and manage
the workbook for your customers.


Code:
--------------------
Sub show_all_unprotect_all()
Dim sht As Worksheet
ThisWorkbook.Unprotect Password:="your password"
For Each sht In Sheets
sht.Visible = True
sht.Unprotect Password:="your password"
Next sht
End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=28107