Auto hide/show worksheets based on cell value?

[slam]

I've seen some examples of doing this when a cell value matches a worksheet name and only that worksheet is hidden or unhidden, but I haven't quite seem what I'm looking for.

I have 40 worksheets named R1, R2, and so on.

On a worksheet called Setup in Cell B3, the user enters a number between 1 and 40. This corresponds to how many worksheets are visible. If 1 is entered, only R1 is visible and the other 39 are hidden, if 40 is entered, they're all visible..... and all possibilities in between.

Is this possible?

Thanks

 

[Stridhan]

could try to add this code in the Setup module

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
    For Each ws In ThisWorkbook.Sheets
        If ws.Name > "R" & Target.Value Then
            ws.Visible = False
        Else: ws.Visible = True
        End If
    Next ws
End If
End Sub

 

[slam]

could try to add this code in the Setup module

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
    For Each ws In ThisWorkbook.Sheets
        If ws.Name > "R" & Target.Value Then
            ws.Visible = False
        Else: ws.Visible = True
        End If
    Next ws
End If
End Sub

That seems to be hiding every worksheet that starts with a letter > R plus if I enter 10 in B3 for instance, R1 and R10 are visible, but all others are hidden.

 

[Stridhan]

sorry my bad, will correct it in a min

 

[Stridhan]

fell asleep last night
try this in a copy

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myVal As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False
If Not Intersect(Target, Me.Range("B3")) Is Nothing Then
    For Each ws In Worksheets
        If ws.Name Like "R*" Then
            If Val(Mid(ws.Name, 2, Len(ws.Name) - 1)) > Target.Value Then
                ws.Visible = False
            Else
                ws.Visible = True
            End If
        End If
    Next ws
End If
Application.ScreenUpdating = True
End Sub

 

[slam]

Perfect, thank you!

 

[Stridhan]

You're welcome