Help so MultiPage in UserForm points to the last number of a named worksheet

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
I'm using MultiPage in a UserForm
I have multiple worksheets that can range from: HOLE 1 and go up to HOLE 30
I have other multiple worksheets that can range from: SAFETY 1 and go up to SAFETY 30
When I use the Code below it always goes to the 1st worksheet named either HOLE 1 or SAFETY 1
How can I have it so that when I click on the MultiPage Tab that it goes to the last sheet on the right for each?
Example: If I'm on SAFETY 5 it would go to that sheet, and not SAFETY 1 like it does now.

Here is my code:
Code:
Private Sub MultiPage1_Change()
    Select Case MultiPage1.Value
    Case 0 'page 1
        Workbooks("Workbook.xls").Sheets _
        ("HOLE 1").Activate
    Case 1 'page 2
        Workbooks("Workbook.xls").Sheets _
        ("SAFETY 1").Activate
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I hope I have explained what I want to do accurately.
Is what I am trying to acheive even possible?
 
Upvote 0
How can I have it so that when I click on the MultiPage Tab that it goes to the last sheet on the right for each?

The last sheet on teh right of what?


Code:
Example: If I'm on SAFETY 5 it would go to that sheet, and not SAFETY 1 like it does now
.

If your on sheet5 it's already activated so why do you have to activate it?
 
Upvote 0
So close to being solved!

Okay, there are 2 Tabs I'm using in a MultiPage Userform.

Tab 1 is HOLE Sheets, which I want to Activate sheet called HOLE #
I want it to go to the last # created.
So if there are 2 sheets --> HOLE 1 and HOLE 2 it would Activate HOLE 2.
There could be up to 30 sheets all called:
HOLE 1,2,3,4,5,6,7,8,9,etc.
I want it to Activate the last sheetname --> HOLE #

Tab 2 is SAFETY Sheets.
I have it working with Tab 2 as it goes to the sheets SAFETY # <-- (last number created) with the code below:

Here is my new code:
Code:
Private Sub MultiPage1_Change()
    Select Case MultiPage1.Value
    Case 0 'page 1
        Workbooks("Workbook.xls").Sheets _
        ("HOLE 1").Activate
    Case 1 'page 2
        Worksheets(Worksheets.Count).Activate
End Sub
 
Last edited:
Upvote 0
I think I might be getting closer, but it's still not working the way I want it to.
Here is a code I've been testing:
Code:
Private Sub MultiPage1_Change()
    Select Case MultiPage1.Value
    Case 0 'page 1
    Dim wks     As Worksheet
    Dim iWks    As Long
 
    For iWks = ThisWorkbook.Worksheets.Count To 2 Step -1
        Set wks = Worksheets(iWks)
 
        If wks.Name Like "*HOLE 1*" Then
            If MsgBox("Go to sheet " & wks.Name & "?", vbOKCancel + vbQuestion) = vbOK Then
                Application.DisplayAlerts = False
                wks.Activate
                Application.DisplayAlerts = True
            Else
                Exit For
            End If
        End If
    Next iWks
    Case 1 'page 2
 
    For iWks = ThisWorkbook.Worksheets.Count To 2 Step -1
        Set wks = Worksheets(iWks)
 
        If wks.Name Like "*SAFETY 1*" Then
            If MsgBox("Go to sheet " & wks.Name & "?", vbOKCancel + vbQuestion) = vbOK Then
                Application.DisplayAlerts = False
                wks.Activate
                Application.DisplayAlerts = True
            Else
                Exit For
            End If
        End If
    Next iWks
End Sub
 
Upvote 0
Try this code. The code assumes you have cosecutively numbered sheet names
i.e. SAFETY 1, SAFETY 2, SAFETY 3, ...... with one space between SAFETY and the number
Code:
Option Explicit
Private Sub MultiPage1_Change()
    Dim TargetSheets As String
    Dim ws As Worksheet
    Dim SheetNumber As Integer
 
    Select Case MultiPage1.Value
    Case 0: TargetSheets = "HOLE "    'page 1
    Case 1: TargetSheets = "SAFETY "  'page 2
    End Select
 
    For Each ws In Worksheets
        If ws.Name Like TargetSheets & "*" Then
            SheetNumber = SheetNumber + 1
        End If
    Next ws
Worksheets(TargetSheets & SheetNumber).Select
End Sub
 
Upvote 0
Solved! :)

Try this code. The code assumes you have cosecutively numbered sheet names
i.e. SAFETY 1, SAFETY 2, SAFETY 3, ...... with one space between SAFETY and the number
Code:
Option Explicit
Private Sub MultiPage1_Change()
    Dim TargetSheets As String
    Dim ws As Worksheet
    Dim SheetNumber As Integer
 
    Select Case MultiPage1.Value
    Case 0: TargetSheets = "HOLE "    'page 1
    Case 1: TargetSheets = "SAFETY "  'page 2
    End Select
 
    For Each ws In Worksheets
        If ws.Name Like TargetSheets & "*" Then
            SheetNumber = SheetNumber + 1
        End If
    Next ws
Worksheets(TargetSheets & SheetNumber).Select
End Sub

Thanks so much, Bill.
Problem Solved! :)
You Are A Life Saver!!!
 
Upvote 0
1 tiny Issue

The code you provided works perfectly for the Pages linked to Worksheets.
However, I have 2 other Pages in MultiPage that are not linked to any worksheets.
I get this error:
PHP:
Run-time error '9':
Subscript out of range

When I debug Code:
Code:
Private Sub MultiPage1_Change()
    Dim TargetSheets As String
    Dim ws As Worksheet
    Dim SheetNumber As Integer
 
    Select Case MultiPage1.Value
    Case 0: TargetSheets = "HOLE "    'page 1
    Case 1: TargetSheets = "SAFETY "  'page 2
    End Select
 
    For Each ws In Worksheets
        If ws.Name Like TargetSheets & "*" Then
            SheetNumber = SheetNumber + 1
        End If
    Next ws
[COLOR=red]Worksheets(TargetSheets & SheetNumber).Select[/COLOR]
End Sub

Any Ideas?
 
Upvote 0
I think I got it

I've been trying everything & I think I finally solved the tiny issue I was having.
I took a guess! LOL
Can you let me know if I did it correctly?
Does this code look right?
Code:
Private Sub MultiPage1_Change()
    Dim TargetSheets As String
    Dim ws As Worksheet
    Dim SheetNumber As Integer
 
On Error Resume Next
    Select Case MultiPage1.Value
    Case 0: TargetSheets = "HOLE "    'page 1
    Case 1: TargetSheets = "SAFETY "  'page 2
    End Select
 
    For Each ws In Worksheets
        If ws.Name Like TargetSheets & "*" Then
            SheetNumber = SheetNumber + 1
        End If
 
    Next ws
 
Worksheets(TargetSheets & SheetNumber).Select
On Error GoTo 0
End Sub

Thanks...
 
Upvote 0
I think better to use Case Else
Code:
Private Sub MultiPage1_Change()
    Dim TargetSheets As String
    Dim ws As Worksheet
    Dim SheetNumber As Integer
 
    Select Case MultiPage1.Value
    Case 0: TargetSheets = "HOLE "    'page 1
    Case 1: TargetSheets = "SAFETY "  'page 2
    Case Else: Exit Sub
    End Select
 
    For Each ws In Worksheets
        If ws.Name Like TargetSheets & "*" Then
            SheetNumber = SheetNumber + 1
        End If
 
    Next ws
 
Worksheets(TargetSheets & SheetNumber).Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top