Hide and unhide worksheets that begin with a name

[StillUnderstanding]

Hello,

I have a workbook that is used by 10+ team members to track holidays and working hours. Every 4 weeks the team member will click a macro that will create a new sheet for the next 4 weeks ahead.

For instance, Jane doe would have created the following tabs
Jane Doe 29-Aug-2022 and next month would create Jane Doe 27-Dec-2022 (Every 4 weeks they would create a new tab)

John Doe would also do the same.

The naming conventions would always be the same.

I am wanting to add the ability for each user to lock and unlock only the tabs that starts with their names and to be able to do it with a password. If you don't have the password then you can't unhide the tabs.

Can anyone advise on how this could be done?

 

[StillUnderstanding]

Managed to work it out:-

Sub UnhideSheetsWithSpecificText()

Dim Password As String
Password = InputBox("Please enter password below", "Password", "????")
If Password <> "me" Then
MsgBox "Incorrect Password"
Exit Sub
Else
End If

For Each ws In ThisWorkbook.Worksheets
If InStr(ws.Name, "Jane Doe") > 0 Then
ws.Visible = xlSheetVisible
End If
Next ws
End Sub

What I have not worked out yet is how to get it so that it only shows for specific users and not all users with in a shared workbook.

 

[Micron]

Rather than lock/unlock, just hide all but the ones where the sheet names belong to them? Should be able to get their user profile name with Environ variable so they can't spoof. You'll have to hide with xlVeryHidden or else it's easy to right click on a sheet and unhide 'hidden' sheets so there's no point in just simple hiding?

 

[dmt32]

Hi,
If your users have access to the workbook over your corporate network then as already suggested, just use their network username to unhide their tabs. This negates need for any password management

As an example

Place both codes in the ThisWorkbook Code page

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim SheetName   As String
    Dim i           As Long
   
    Do
        SheetName = Environ("USERNAME") & " " & Format(Date + i, "dd-mmm-yyyy")
        i = i + 1
    Loop Until Not Evaluate("ISREF('" & SheetName & "'!A1)")
   
    Sh.Name = SheetName
    Sh.Move After:=Sheets(Sheets.Count)
End Sub

Private Sub Workbook_Open()
    Dim ws          As Worksheet
   
    Application.ScreenUpdating = False
   
    Worksheets(1).Visible = xlSheetVisible
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Index > 1 Then ws.Visible = UCase(ws.Name) Like UCase(Environ("USERNAME")) & "*"
    Next ws
    Application.ScreenUpdating = True
   
End Sub

Note: 1 worksheet MUST always be visible - I have made the FIRST worksheet (master or Home sheet?) Visible.

NewSheet event will, each time new sheet added, automatically ADD users name & date (which can be amended if needed) this is needed to reduce naming error to ensure that the Open event code finds their tabs.

This is just an idea - you will need to adapt to meet specific project need as required

Hope helpful

Dave