Activating hidden worksheets

[Johnlemons]

All of my sheets are hidden (28 sheets)
They are not sorted as sheet1 sheet2 etc..
I need a button that will go to the next very next worksheet.

Example:
sheet6 is named: aaa
sheet2 is zzz
sheet9 is ccc
sheet7 is bbb

If I was in the sheet aaa and I hit the button, it needs to go to zzz (not to bbb)
any ideas on this?

Thanks a lot
John lemons

[Bob Phillips]

All of my sheets are hidden (28 sheets)

I doubt it.

They are not sorted as sheet1 sheet2 etc..
I need a button that will go to the next very next worksheet.

Example:
sheet6 is named: aaa
sheet2 is zzz
sheet9 is ccc
sheet7 is bbb

If I was in the sheet aaa and I hit the button, it needs to go to zzz (not to bbb)
any ideas on this?

Thanks a lot
John lemons

Do you mean goto a hidden sheet? If so, why?

[austenr]

OK I have to ask...If all your sheets are hidden, how do you even get to the first one?

[Zack Barresse]

Hello John, what xld and Austenr is referring to is the fact that Excel cannot hide all sheets, you must have at least one sheet visible at all times. It is quite impossible to hide them all at the same time.

Also, I must say, I'm a little perplexed as to what your actual requirements are. Do you mean to say that you only have one sheet not hidden at all times, and the rest are hidden? And you want a button on each sheet, so that when you click it, the next sheet in the list will become unhidden and the current sheet will become hidden?? If so, maybe something like this ...

[vba]Option Explicit

Sub NextSheet()
On Error GoTo NoSheet
Sheets(ActiveSheet.Index + 1).Visible = True
GoTo IsSheet
NoSheet:
Sheets(1).Visible = True
IsSheet:
ActiveSheet.Visible = False
End Sub[/vba]

[malik641]

Maybe he meant that 28 of his sheets are hidden and the others are not. And say the next sheet he wants to select will be the next visible sheet. If this is what he means, then here's something a little different:[VBA]Option Explicit

Sub NextSheet()
Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
If Sheets(ActiveSheet.Index + i).Visible <> False Then
Sheets(ActiveSheet.Index + i).Select
Exit For
End If
Next i
End Sub[/VBA]

[Zack Barresse]

Well if that be the case, you wouldn't need to loop through the sheets checking for a non-visible sheet because you can't select a hidden sheet.

[Johnlemons]

Yes FireFytr thats correct all are hidden except my main menu sheet.
Right now I have a button to pick which sheet I want and on
that sheet I have a return button. But many times I know I
need to go to the very next sheet (or the sheet before). Instead
of always having to go to the main menu I'd like to just click on a
button to go to the next or previous sheet.

Thanks so much.
John Lemons.

[Bob Phillips]

Yes FireFytr thats correct all are hidden except my main menu sheet.
Right now I have a button to pick which sheet I want and on
that sheet I have a return button. But many times I know I
need to go to the very next sheet (or the sheet before). Instead
of always having to go to the main menu I'd like to just click on a
button to go to the next or previous sheet.

But I ask again, why doi you want to goto a hidden sheet? Do you want to unhide it, do you want to get data from it, what? I ask as you cannot activate a hidden sheet, and if you unhide it and activate, it is no longer hidden.

[malik641]

Well if that be the case, you wouldn't need to loop through the sheets checking for a non-visible sheet because you can't select a hidden sheet.

Well that was my first thought, as I tried to just select the next indexed sheet...but I got an error when running the macro. (BTW, I was checking a Visible sheet to select, not a non-visible one...If the next sheet was non-visible, then go to the next and check again)

So I figured instead of making a bunch of GoTo statements, I would just loop through the amount of sheets there were until I found the next visible one, then select it. But I noticed that this wouldn't work if you are on the last sheet...soooo I made this:
[VBA]
Option Explicit

Sub NextSheet()
On Error GoTo SelectFirst
Dim i As Long
For i = 1 To ActiveWorkbook.Worksheets.Count
If Sheets(ActiveSheet.Index + i).Visible <> False Then
Sheets(ActiveSheet.Index + i).Select
Exit Sub
End If
Next i
SelectFirst:
For i = 0 To ActiveWorkbook.Worksheets.Count
If Sheets(ActiveWorkbook.Sheets(1).Index + i).Visible <> False Then
Sheets(ActiveWorkbook.Sheets(1).Index + i).Select
Exit Sub
End If
Next i
End Sub
[/VBA] If I missed something...sorry Zack I'm not sure I understand what you're saying.

[Zack Barresse]

I see what you're doing. What if it was the last sheet though? You're going to make them going through 2 loops?? Did you see my code? It does pretty much the same thing.

I'm also curious, as xld is, why the sheets need to be hidden in the first place. ??

[Johnlemons]

Solved: Thanks guys. That worked for me.
Sheets(ActiveSheet.Index + i) is what I needed to put me in the right direction.
XLD yes If I want to view a hidden sheet, it must be unhidden. The "hidden" sheet was for peoples info when programming it. Kinda like a FYI its hidden.

[Johnlemons]

Firfytr scrolling through 28 of those tags are tiresome. Its much nicer and neater to view only the sheets that you are currently on, rather than scrolling to the right and coming to the end and having to go back to the left to find your sheet that you want. I can go to any sheet within 5 seconds. And now I don't have to return to main menu and select the next one.
Thanks,
John

[malik641]

I see what you're doing. What if it was the last sheet though? You're going to make them going through 2 loops?? Did you see my code? It does pretty much the same thing.

....Yeah I can see how your code does the same thing. I guess I just overlooked it when I wrote mine

The only difference really is that mine went to the next visible sheet...and once it is on the last it would start at the beginning and find the first visible sheet (incase the first sheet or couple of sheets are hidden).

...But now that I look at it...I realize it's useless. Complete, and entirely, useless. My code is the same as just clicking on the next sheet DER!!!

Sorry about that...I guess I was in a rush because I'm at work and have some other things to finish up. I'll try to think a little further next time

[Zack Barresse]

I wouldn't say your code is useless Joseph, just more widely used as a native feature.

John: Have you taken a look directly to the left of all your sheet tabs? There are four buttons. They help you navigate through your sheets. Also, Ctrl + Page Up will scroll one sheet [index] up & Ctrl + Page Down will scroll one sheet [index] down. The only advantage the code would give you is to start from the beginning right away, but you can be there in two clicks with the native methods..

[austenr]

I would have to agree with you Zack. If it isn't broken don't fix it. Perhaps he has a need for it we do not understand.