Hidden Sheets keep appearing

B

broop

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

Chip Pearson

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

broop

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

ShaneDevenshire

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

Simon Lloyd

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

broop

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?:)
 
S

Simon Lloyd

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

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

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

Code
-------------------
Sub show_all_unprotect_all(
Dim sht As Workshee
ThisWorkbook.Unprotect Password:="your password
For Each sht In Sheet
sht.Visible = Tru
sht.Unprotect Password:="your password
Next sh
End Su
-------------------

--
Simon Lloy

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com
 
B

broop

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?




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:
--------------------
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 --------------------
Click to expand...


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

Simon Lloyd

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

As for your information pages i need to know which page should be show
upon request with which page? i.e if Mix page is shown, when clickin
show info button in add-ins (i will create one!) should ingredients pag
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 gladl
recieve one as it pays for the maintainance and bandwidth costs

--
Simon Lloy

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com
 
S

Simon Lloyd

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

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

--
Simon Lloy

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com
 
B

broop

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

Simon Lloyd

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

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

--
Simon Lloy

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

simple but can't make it, delete blank pages?!?! 1
Here is Mortgage Calculator with Amortization 12
Emailing a sheet from a workbook with VBA 0
Help - need to consolidate workbooks 3
INDEX_MATCH or VLOOKUP with multiple criteria? 4
difficult VLOOKUP question: 2
Sum Across Multiple Sheets 1
Extracting data from database into separate sheets 0

Top