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

Simon, after working on my colors changes etc. last night and this
morning, i
ran into a couple of issues.

1. When I go to the Add-Ins button to select a page the only ones that
need to show up there are the following tabs: Herd Description,
Requirements, Ingredients, Make A Mix, Auto-Balance, Evaluate, Report
Auto Balance, Reports Evaluate. All of the others in that are showing
up in the Add-ins button shouldn't be visiable and can't be.

2. On the requirements page i would like to have the Notes on a hidden
page and a button there to replace all of that text. So they would hit
the button and it will give them the message. That will help clean that
sheet up. I will wan to do this on other sheets as well where there are
text telling them what to do. Again, I don't want them to show up in the
Add-ins list. once i hide the worksheet.

3. On the make a mix sheet something is wrong on the buttons they
aren't bringing up to correct things. The first button works fine but
the others are not correct. If you need me to send you an old version so
that you can see what is suppose to be there let me know. I am afraid
the make changes to this like this becuase of all of the codes and i
don't have a clue what they mean.


At the moment this is good for now it's a start. As i continue to work
in this program i will let you know if I see anything else. Please make
your changes off of the one that is attached since i have worked on this
one. I will delete the old one once you send it back to me again so that
i don't get confused on which one to use. I would also like to send you
an appreciation gift for all of your work if this isn't aloud should i
just make a donation to The Code Cage?





Simon Lloyd;117885 Wrote:
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
--------------------



+-------------------------------------------------------------------+
|Filename: Geminator round 1.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=17|
+-------------------------------------------------------------------+

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

The issues that you state already remained in your workbook that you
sent (germinator clean), the sheets that you do not want to see when the
userform appears after clicking the button in add-ins, just list those
and i will see to that.

As for your information pages i need to know which page should be shown
upon request with which page? i.e if Mix page is shown, when clicking
show info button in add-ins (i will create one!) should ingredients page
show?

Give me a bit of better dtructured info and i can sort that for you.

As for a gift, well i don't need one but the code cage would gladly
recieve one as it pays for the maintainance and bandwidth costs.


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

Hi Broop, i have fixed your sheets that should show when the extra
menubar item is clicked or the button in Add-Ins in xl2007, as for your
buttons on other sheets to show information, take a look at the code i
have used to hide or unhide sheets, you can then use a command button
where you can assign a macro to show or hide the sheets as needed, you
need to experiment with it as it will help you understand what is
happening as you will be maintaining this workbook.


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

--
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, i sent you a email please review. Also when i opened up this
workbook it is telling me that it is in a read only, are you deleting
the old workbook with the new one that i sent to you and making your
changes off of that one?

Will it make a different if someone is working in Excel 2003 vs. 2007.
If so should we have a version for 2003 and 2007?


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

As for read only just save as another name and all should be fine. All
the modules in the workbook can be imported to your current use
workbook.

I did see your email which asked me to not do anything about point 3
but fix the other 2, i fixed point 1 and gave you pointers on how to
tackle point 2.


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

Ok thanks Simon i will import those into my workbook then.


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

Its not an add-in, you need to export the modules from one workbook and
import to the other or just copy the code from one to the other.


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

Sheet jump menu fixed to leave out sheets.


+-------------------------------------------------------------------+
|Filename: Geminator round 1.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=21|
+-------------------------------------------------------------------+

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

Update* Fixed for hidden notes


+-------------------------------------------------------------------+
|Filename: Master_Geminator.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=30|
+-------------------------------------------------------------------+

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (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