Navigate workbook with userform buttons

[SimmonsDeux]

I have a userform with two buttons. butNext and butPrev. I was wondering if it is possible to navigate to the adjacent worksheet using these buttons respectively.
Thanks!

 

[dmt32]

I have a userform with two buttons. butNext and butPrev. I was wondering if it is possible to navigate to the adjacent worksheet using these buttons respectively.
Thanks!

Hi,
see if this code does what you want:

CommandButton Code

Private Sub butNext_Click()
SelectSheet Direction:=xlNext
End Sub


Private Sub butPrev_Click()
SelectSheet Direction:=xlPrevious
End Sub

Navigation code: (can go in standard module or Forms Code page)

Sub SelectSheet(ByVal Direction As XlSearchDirection)
Dim indx As Integer, r As Integer
Dim wsCount As Integer


      wsCount = ThisWorkbook.Worksheets.Count
      indx = ActiveSheet.Index
    'set value of r
     r = indx + IIf(Direction = xlPrevious, -1, 1)
     'ensure value of r stays within sheet count
      If r < 1 Then r = 1
      If r > wsCount Then r = wsCount
      
      ThisWorkbook.Worksheets(r).Select
End Sub

Code assumes all sheets are visible.

Dave

 

[Domenic]

Here's an example that takes into consideration hidden sheets, and other types of sheets (ie. chart sheet)...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] butPrev_Click()
    [COLOR=darkblue]Dim[/COLOR] ShtCnt      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ShtIndx     [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    ShtCnt = Sheets.Count
    ShtIndx = ActiveSheet.Index
    [COLOR=darkblue]Do[/COLOR]
        [COLOR=darkblue]If[/COLOR] ShtIndx <> 1 [COLOR=darkblue]Then[/COLOR]
            ShtIndx = ShtIndx - 1
        [COLOR=darkblue]Else[/COLOR]
            ShtIndx = ShtCnt
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] Sheets(ShtIndx).Visible
    Sheets(ShtIndx).Select
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub
[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] butNext_Click()
    [COLOR=darkblue]Dim[/COLOR] ShtCnt      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ShtIndx     [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    ShtCnt = Sheets.Count
    ShtIndx = ActiveSheet.Index
    [COLOR=darkblue]Do[/COLOR]
        [COLOR=darkblue]If[/COLOR] ShtIndx <> ShtCnt [COLOR=darkblue]Then[/COLOR]
            ShtIndx = ShtIndx + 1
        [COLOR=darkblue]Else[/COLOR]
            ShtIndx = 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]Until[/COLOR] Sheets(ShtIndx).Visible
    Sheets(ShtIndx).Select
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!