Go To Previous Worksheet / Go To Next Worksheet {Problem}

[irapa]

Hi there, I am trying to create a macro button which moves between specified worksheets. The scope of the macro is to move between worksheets and when the last worksheet has been chosen a vbExclamation will pop up telling the user that he cannot go forward anymore.

The macro is working fine and is moving through worksheets as needed, until it comes to the last worksheet and a debug error pops up.

Any help would be appreciated!

{Code as follows}

Sub Forward()


Call PreFunction


Dim SheetNum, SheetLen As Integer
Dim FindNum, NextPg As Variant


SheetNum = CInt(Right(ActiveSheet.Name, 1))
Set FindNum = Sheets("Check").Range("B:B").Find(SheetNum, LookAt:=xlWhole)
NextPg = Sheets("Check").Range(FindNum.Address).Offset(1, 0).Value
SheetLen = Len(ActiveSheet.Name) - 1


If NextPg < SheetNum Then
MsgBox "You cannot go forward from this page!", vbExclamation
ElseIf Sheets(NextPg).Visible = False Then
MsgBox "You cannot go forward from this page!", vbExclamation
Else
Sheets(Left(ActiveSheet.Name, SheetLen) & NextPg).Select
End If


Call PostFunction


End Sub

[Yongle]

would this work for you?

Sub NextSheet()
If ThisWorkbook.Worksheets.count = ActiveSheet.Index Then
MsgBox "You cannot go forward from this page!", vbExclamation
Else
End If
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub

[Yongle]

and to extend it to prevent user moving forward if the next sheet is hidden, try

Sub NextSheet()
If ThisWorkbook.Worksheets.count = ActiveSheet.Index Then
MsgBox "You cannot go forward from this page!", vbExclamation
Exit Sub
Else
End If
If Sheets(ActiveSheet.Index + 1).Visible = False Then
MsgBox "You cannot go forward from this page!", vbExclamation
Exit Sub
Else
End If
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub

[irapa]

Hey there, thanks for the reply.

I used your code and managed to get the result I wanted on the MsgBox. No debugging message is occuring, though one problem is that I want it to navigate through specific sheets not between sheets which are after each other, that is why in my code I use the "check" sheet because in column B of the check sheet I have numbers from (-20) 0-9 (20) with the -20 and 20 acting as a flag so that I can use them to stop check the sheets active. That's the problem I am having now, it's that I want to move between specific sheets and not just sheets being placed after each other.

Thanks for the reply really helped me

[Yongle]

Try this minor modification. I have created an array for valid sheets.The sequence in which sheets are listed in the array determines the next sheet. We can revert to using column B later - this way it keeps everything in the macro, which makes it easier for me to test things on my PC.
What you need to do
Create a new test workbook with 6 sheets (Sheet1 to sheet6)
Run the macro from Sheet1 and Sheet6 will be activated
Run the macro from Sheet3 and Sheet1 will be activated
Is this what you are looking for?

Sub NextSheet2()
'the 3 below should be amended to reflect the number of valid sheet names
Dim ValidSheets(1 To 3) As String
Dim NextSheet As String
Dim i As Integer
'create array of VALID sheetnames to navigate
ValidSheets(1) = "Sheet3"
ValidSheets(2) = "Sheet1"
ValidSheets(3) = "Sheet6"

If ThisWorkbook.Worksheets.count = ActiveSheet.Index Then
MsgBox "You cannot go forward from this page!", vbExclamation
Exit Sub
Else
End If
On Error Resume Next
'check current sheetname and go to next one listed in array ValidSheets
For i = 1 To UBound(ValidSheets) - 1 ' no need to try last sheet
If ActiveSheet.Name <> ValidSheets(i) Then
'do nothing
Else
NextSheet = ValidSheets(i + 1)
MsgBox NextSheet & vbNewLine & Sheets(NextSheet).Name
End If
Next i
Sheets(NextSheet).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub

Tailoring it to your workbook it is quite easy
- you simply list the names of all your "valid" sheets changing the array number each time ValidSheets(4) , ValidSheets(5) etc
- amend the number of items in the array from 3 to the total number of valid sheets that you want to include in the array (matches last array number)

NB Assumption that the last sheet in the array is the last active sheet to navigate to - we can change that if that is not what you want etc

[irapa]

The concept I am trying to make here is that the user chooses the number of worksheets{Letters to be shown} to make visible. If the user picks 5 to show, let us call them Sheet 1 - Sheet 5, the user will then go on the first sheet and then pressing the macro button to move between the specified sheets.

The first thing the user has to do is to choose the type of letter{Sheets} to be chosen. After this is done the user picks the number of {Sheets} Letters to be seen.

After that is done the user can then skip through the sheets using the macro and it will stop at the specific sheets which are not hidden.

If I had to do an array I don't think it would be a very suitable idea seeing that there are 10 {Sheets} Letters x 4 Types of Letters { Format of Letter } to be chosen from.

I don't know if I explained myself well.

Thanks for the help !

[Yongle]

I think you are explaining very well.
The user first chooses type of letter and has 4 choices (say type A,B,C,D)
then
The user first chooses how many sheets and has 10 sheets available (sheet 1,2,3...10)

My questions
Can the user choose as many sheets as he wants (max 10)?
If he chooses 5 sheets which sheets are they? Is that dependant on the type of letter, or is the user free to decide?

thanks

[irapa]

Yes that's the concept

The user can choose a minimum of 1 letter and a maximum of 10 letters.

The number of sheets chosen is dependent on the type chosen (A,B,C,D). The user cannot have letters from A and letters from B at the same time.

Thanks a lot !

[Yongle]

Ok
So a different mix of sheets needs to be linked with each type of letter

which sheets go with TypeA
which sheets go with TypeB
which sheets go with TypeC
which sheets go with TypeD

[irapa]

Sheets are linked accordingly:

Sheets:
BC-AA-0 - BC-AA-9 (sheets 1 through 9) linked with TypeA
BC-RE-0 - BC-RE-9 linked with TypeB
BC-CASL-0 - BC-CASL-9 linked with TypeC
BC-VC-0 - BC-VC-9 linked with TypeD
BC-KS-0 - BC-KS-9 linked with TypeE

that's my concept of the sheets linked to each other.

[Yongle]

So using BC-AA-0 - BC-AA-9 TypeA as an example

Sheets are
BC-AA-0
BC-AA-1
BC-AA-2
BC-AA-3
BC-AA-4
BC-AA-5
BC-AA-6
BC-AA-7
BC-AA-8
BC-AA-9

Can the user select any number of those sheets to be made visible (minimum 1, maximum 10) ?
Say he chooses 5, could he choose BC-AA-2 & BC-AA-3 & BC-AA-5 & BC-AA-6 & BC-AA-9 ?

[irapa]

No when the user selects for example 5 letters to be shown, the letters will be shown in order BC-AA-0 - BC-AA-4

[Yongle]

And the first sheet will ALWAYS be BC-AA-0 for TypeA?

[irapa]

Yep that's basically it

[Yongle]

There are 2 input boxes to capture the Type and number of sheets.
This code ends with a message box for you to verify that the selections are correct based on user input.
It changes nothing in the workbook and so you can run it anywhere.

If all is well, we can then bolt on the code allowing the user to click through his selection of sheets.

To test that the first sheet being selected is correct, remove the apostrophe at the beginning of line

'Sheets(SheetCode & "0").Activate

and run it a few times with different selections against the file containing all 50 worksheets.

Sub NextSheet3()

Dim i As Integer
Dim SheetCode As String, ChosenLetter As String, ChosenSheets As Integer


ChosenLetter = InputBox("Enter A or B or C or D or E", "Select Letter Type", "A")
ChosenLetter = UCase(ChosenLetter)
ChosenSheets = InputBox("Enter a number between 1 and 10", "How many visible sheets", "1")


Select Case ChosenLetter
Case "A"
SheetCode = "BC-AA-"
Case "B"
SheetCode = "BC-RE-"
Case "C"
SheetCode = "BC-CASL-"
Case "D"
SheetCode = "BC-VC-"
Case "E"
SheetCode = "BC-KS-"
End Select


MsgBox "You selected" & vbNewLine & "Type" & ChosenLetter & vbNewLine _
& ChosenSheets & " sheets" & _
vbNewLine & "from " & SheetCode & "0" & " to " & SheetCode & ChosenSheets - 1


'Sheets(SheetCode & "0").Activate
End Sub

[irapa]

Thanks changed it a little bit just to fit my personal use and works brilliantly you are a life saver !

[Yongle]

And finally...
(you may have already done this a different way, in which case, feel free to ignore this suggestion, but seeing a different approach may help your future VBA endeavours)

This is the bit that makes VBA go to the next sheet and stop at the last sheet.

First, we need to save some variable values to use later. One way to do that is to dump the values into a worksheet - sheet "Check" seemed the obvious place. I have used cells ZA1 to ZA4 - you can change these to whatever you want, but remember to also change the 2nd macro to match.

Add these lines just above the End Sub line in the previous macro

 With Worksheets("Check")
.Range("ZA1").Value = sheetcode
.Range("ZA2").Value = chosensheets
.Range("ZA3").Value = ChosenLetter
.Range("ZA4").Value = 0
End With

The macro to move the user to the next sheet is:

Sub NextSheet4()

Dim i As Integer
Dim NextSheet As String
Dim ws As Worksheet
Set ws = Sheets("Check")

If ws.Range("ZA4").Value = ws.Range("ZA2").Value Then
MsgBox "You cannot go forward from this page!", vbExclamation
Exit Sub
End If

ws.Range("ZA4").Value = ws.Range("ZA4").Value + 1
NextSheet = ws.Range("ZA1").Value & ws.Range("ZA4").Value
Sheets(NextSheet).Activate

End Sub

How it works
Relevant values are stored in worksheet "Check"
Each time the next worksheet is selected, 1 is added to cell ZA4, which is the tail end of the next sheet name
There is a check to see if the active sheet is the last selected sheet, in which case, the warning message pops up.
There should be no need to clear out the values in cells ZA1 to ZA4, because the values are overwritten each time. But it would do no harm to do that at the end of the second macro if you felt the urge!