You can make sheets visible (xlSheetVisible), hidden (xlSheetHidden) and very hidden (xlSheetVeryHidden).

The property xlSheetHidden hides the sheet, but it can be made visible by any user through the Excel user interface.
If you use xlSheetVeryHidden, however, the user cannot make the sheet visible through the Excel user interface.

So create Main sheet (that will always be visible).
And there add buttons for each sheet that will launch prompt question or userform where you can enter password.

In module add all passwords like this:

Public admin_pass As String
Public user1_pass As String
Public user2_pass As String
Public user3_pass As String
'Passwords

Public Sub Passwords()
    user1_pass = "user1"
    user2_pass = "user2"
    user3_pass = "user3"
    admin_pass = "admin"
End Sub
And in code allow those passwords for each sheet.

Something like (example for userform)
Private Sub UserForm_Terminate()
Call Passwords
If Me.TextBox1.Text <> user3_pass And Me.TextBox1.Text <> admin_pass Then
    Msgbox "Wrong password"
    Exit sub
End If
End Sub
At the end protect VB Project so other users cannot see or edit module with passwords.