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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
I hope I have explained what I want to do accurately.
Is what I am trying to acheive even possible?
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412
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?
 

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
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:

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346

ADVERTISEMENT

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
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412
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
 

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346

ADVERTISEMENT

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!!!
 

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
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:
Code:
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?
 

Phixtit

Active Member
Joined
Oct 23, 2008
Messages
346
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...
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

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
Top