user not allowed to change sheet

[Sonyk]

Hello again

I want to make a book complety impossible to change from sheet to sheet (that can only be done by buttons that redirect the user to that specific sheet)

Is there a way to do this?

[Paul]

Hi Sonyk,

The following macro does what I think you're after. Start by hiding all of the worksheets in the workbook (except one, of course, since Excel requires one worksheet as visible). Add this code to your ThisWorkbook module (in the VB Editor window) and then on the Review ribbon, Protect the Workbook. The code below uses a workbook protection password of "aaa".

The code, which could be attached to a toolbar button (rather than a button on each sheet in the workbook), simply asks the user which sheet they want to go to - and it provides a list of all of the worksheets available in the workbook. The user just needs to type the name of the worksheet in the space provided and press OK. The macro will unprotect the workbook, hide the active sheet, open the new sheet, then re-protect the workbook.

Hope that helps!

Option Explicit

Sub listSheetsAndGo()
Dim i As Integer, mySheets() As Variant, shCount As Integer
Dim strSheets As String, ans As String, currSht As String

Application.ScreenUpdating = False
currSht = ActiveSheet.Name
shCount = Sheets.Count
ReDim mySheets(shCount - 1)

For i = 1 To shCount
    mySheets(i - 1) = Sheets(i).Name
Next i
strSheets = Join(mySheets, ", ")
ans = InputBox("Which sheet would you like to go to?" _
    & vbCrLf & vbCrLf & strSheets, "Sheet Navigator")

If ans = currSht Then
    MsgBox "You're already on sheet: " & ans
Else
    On Error GoTo errHandler
    ThisWorkbook.Unprotect Password:="aaa"
    Sheets(ans).Visible = True
    Sheets(currSht).Visible = False
    ThisWorkbook.Protect Password:="aaa"
    On Error GoTo 0
End If

Application.ScreenUpdating = True
Exit Sub

errHandler:
Application.ScreenUpdating = True
ThisWorkbook.Protect Password:="aaa"
MsgBox "An incorrect sheet name was entered."
End Sub