What to use???

[Brenda]

I have a question and i am not sure what to do. I have a worksheet that has 8
tabs. I would like to have those tabs hidden however if you click on the main
tab(which would have to stay open) you would be able to select "button not
sure this is my problem)" that would let you view what ever tab you wanted to
select however once you select another tab it would close out the tab that
you were just on. I am not sure how to go about doing this or what to even
use to make this happen. I hope i haven't confused anyone reading this. If
you can help me i would appreciate any input on what to do and how to do it.

 

[Barb Reinhardt]

Are you saying you want to hide/unhide the worksheets with the click of a
button?

Barb Reinhardt

 

[Brenda]

Yes when they click on the "button" it will bring up the tab i have hidden
they then would have the option of selecting another tab and once they bring
up the tab they now want it will hide the tab they were on. Basically there
will only be two tabs visible. The main tab and the tab they are working on.
Does this make sense?

 

[OssieMac]

Hi Brenda,

I started testing this before I saw Barb's post so I'll give it to you
anyway. Sorry if I have cut in on you Barb.

In lieu of buttons, I would list the worksheet names in cells and double
click the appropriate cell to do what you want. (Don't list the main sheet
name.) My example has the sheet names in cells A1 to G1 (Sheets 2 to 8) on
the main sheet. The macro tests that the double click occurred in this range,
saves the value of the cell that was clicked and then performs what you want.
You do have to return to the main sheet each time to select another sheet but
I think that is what you intended with a button.

To install the macro, right click on the main worksheet name, select View
Code and then copy and paste the macro into the VBA editor. Close the VBA
editor and save the workbook.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim isect As Object
Dim ws As Worksheet
Dim strTarget

'Note: Range("A1:G1") contains the names of your worksheets
Set isect = Application.Intersect(Target, Range("A1:G1"))
If Not isect Is Nothing Then
Cancel = True
strTarget = Target.Value
For Each ws In Worksheets
If ws.Name = "Sheet1" Or ws.Name = strTarget Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetHidden
End If
Next ws
'Activate the worksheet selected
Sheets(strTarget).Activate
Else
Exit Sub
End If

End Sub

 

[OssieMac]

PS Brenda,

Change Sheet1 in the following line to your main sheet name.

If ws.Name = "Sheet1" Or ws.Name = strTarget Then

 

[Brenda]

okay wish me luck i am going to try this tomorrow. Thank you so much for your
help.

 

[Brenda]

Before i start one quick questions because all of my tabs are already labeled
do i have to rename them? Also, whould i hide the tabs before i install the
macro?

 

[Brenda]

I hope that i did it correctly here is what i am getting now:
Compile error
Only comments may appear after End Sub, End Function, or End Property.

I entered the names of the tabs all in the same row but in different
columns. Then i hide the sheets. After I entered the macro on the main sheet,
i saved the document, then when got the error code i listed above. Now i
didn't go out of the workbook to open it completely open again. Will the
tabs stay hidden when i go back in completely? I really appreciate your help
on this it means a lot.

 

[OssieMac]

Hi Brenda,

No you do not have to rename them.

No you do not have to hide the sheets first but it will not matter if you
do. Hiding will take place the first time you activate the macro by double
clicking one of the cells with the sheet names.

Just insert the tab names that you already have in a range of cells on the
main sheet. However, whatever range you use on the main sheet for the tab
names you will have to ensure that the range in the following line of code
matches that range. For instance if you place the names one under the other
in cells A1 to A7 then you would change A1:G1 to A1:A7.

Set isect = Application.Intersect(Target, Range("A1:G1"))

Also you have to change the Sheet1 name in the following line of code to
match the name of your main sheet that never gets hidden.

If ws.Name = "Sheet1" Or ws.Name = strTarget Then

Feel free to get back to me if you have any problems with it.

 

[Brenda]

Ossie,
First of all i appreciate your patience.
okay i made the change as well to the Sheet1 with the name i have on page
one (Instructions). Now here is what i am coming up with. The person that
created this program has a password on every page (not sure if this will
create problems). Now when i completely closed the program and opened it
back up the tabs were still visible. Also i am still getting the error
message i listed before. Any clue where i go from here?

 

[Brenda]

you are more than welcome to email me i don't want to tie up so much space on
here not sure if thats allowed (e-mail address removed)

 

[OssieMac]

Hi Brenda,

I have all the patience in the world. I have been there;done that and know
exactly what you are going through.

The error message indicates you have something in the VBA editor past the
End Sub. You should have only copied data between and including the following
two lines.

Private Sub Worksheet_BeforeDoubleClick _

End Sub


If can't find the problem, Copy the entire contents that you have in the VBA
editor and post it here and I will tell you what you have wrong.

 

[Brenda]

hey i have good NEWS...okay here is where i am now. I found the problem with
the End Sub it was in the beginning. Now that is straight. Okay when i go
back into the program after completely closing out the workbook and go back
in the tabs are still showing, but when i go in on each sheet after
unprotecting the sheets with the password and hide them then it works like it
is suppose to. Should i take the passwords out? I just have to find out where
he put them. You think after i take them out it should work then when i go
out and go back in? I am going to stop here tonight as it is getting late and
work comes early. I will continue to work on this tomorrow, hopefully you
will still be available to help me as you are very very helpful and i am
making progress slowly (my fault) but I am getting this worked out in baby
steps. LOL

 

[OssieMac]

I am happy for you Brenda. It is always feels good when you beat the system
and make it do what you want. I will need to test with the password thing
before I can answer that.

Yes I will be around but probably not when you start work. We are 17hrs in
front of US Pacific time and I will still be tucked up in bed but I will
check in when I arise which will probably after lunch your time.

 

[Brenda]

I will wait to you back from you.

 

[OssieMac]

Hi Brenda,

Getting a bit late in the day for you now but I have spent some time this
morning testing the code with protected sheets and protected workbooks.
Hiding the sheets fails if the workbook is protected but works OK if only the
worksheet is protected. However, there are so many options for protection
that I cannot be sure that I have a replica of what you have.

Anyway with my testing, after saving and closing the workbook it re-opens
with the same sheets hidden as were hidden when saved.

I thought that once you unprotect then they remain unprotected until you
actually protect again. If this is not occurring, perhaps protection is being
reapplied with code in the close or open event. If so, you should find this
code in ThisWorkbook module in the VBA explorer.