macro: jump to cell in next sheet

[as_sass]

Hey,

you guys are so helpful, so I'll try it once again:

I am trying to have a button in a sheet of my workbook that runs
macro that will then jump to a specified cell in the next sheet.

However, the next sheet is hidden. Also, I want the sheet that contain
the button to hide once the button is clicked.

In the end, I want to end up with a workbook where users are taken fro
sheet to sheet when they click on the buttons in the sheets, while the
can only see the active sheet. The workbook itself is protected, and s
are the sheets (some cells are unlocked).

Thanks!

a

 

[kkknie]

The code would be something like

Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Range("A1").Select
Sheets("Sheet1").Visible = False

Not sure what will happen with protected sheets, but if it fails, yo
can just use Sheets("Sheet2").Unprotect and Sheets("Sheet2").Protect i
need be.

 

[JE McGimpsey]

One way:

Public Sub NextSheet()
Const sPASSWORD As String = "drowssap"
Dim wsOldActive As Worksheet
Set wsOldActive = ActiveSheet
Application.ScreenUpdating = False
ThisWorkbook.Unprotect Password:=sPASSWORD
With wsOldActive
With Sheets(.Index Mod Sheets.Count + 1)
.Visible = True
Application.Goto .Range("J10")
End With
.Visible = False
End With
ThisWorkbook.Protect Password:=sPASSWORD
Application.ScreenUpdating = True
End Sub