next sheet macro

[gaur]

... To begin with.. I am a total beginner.

I want to make two macros that I can assign to buttons. One should activate the next sheet in the workbook and the other button should activate the previous sheet.



thanks

 

[Jay Petrulis]

Hi Gaur,

The following will move to the next or previous sheets in the workbook. This replicates clicking on the tabs for the sheet.

---begin VBA---
Sub next_sheet()
Dim wkshtcount As Integer
wkshtcount = ThisWorkbook.Sheets.Count
If ActiveSheet.Index <> wkshtcount Then
Sheets(ActiveSheet.Index + 1).Activate
End If
End Sub

Sub previous_sheet()
If ActiveSheet.Index <> 1 Then
Sheets(ActiveSheet.Index - 1).Activate
End If
End Sub
---end VBA---

Regards,
Jay

 

[NateO]

Same thing only different:

sub pre()
ActiveSheet.Previous.Select
end sub

sub nxt()
ActiveSheet.Next.Select
end sub

 

[Dave Hawley]

Hi Gaur

Just a quick ammedment to Nates

Sub pre()
On Error Resume Next
ActiveSheet.Previous.Select
On Error GoTo 0
End Sub

Sub Nxt()
On Error Resume Next
ActiveSheet.next.Select
On Error GoTo 0
End Sub


Just to stop any Run-time errors if you are on the last or first sheet.

 

[NateO]

Right-o Dave, nice catch, and I apologize for the sloppy code. Might want to have the end-user catch some grief as below.

Sub pre() 
On Error goto errorhandler
ActiveSheet.Previous.Select 
exit sub
errorhandler:
msgbox "Yer already on the first worksheet!"
End Sub

Sub Nxt() 
On Error goto errorhandler
ActiveSheet.next.Select 
exit sub
errorhandler:
msgbox "Yer already on the last worksheet!"
End Sub

Incidentally, in all seriousness, nice site Dave.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-20 19:42