VBA to hide all sheetson workbook open, but then unhide sheets where sheet name matches en

[scotlandsneo]

Hi everyone,

I am stuck with the problem where i am trying to have my workbook hide all sheets by using xlsheetveryhidden but i struggle to have the workbook then unhide sheet where the sheet name matches environment username. any help would be appreciated.
the below code shows sm1212 and rw1212 as usernames, my login windows ID is sm1212 so when i login team dash and sm1212 should be the only sheets i can view.

Private Sub Workbook_Open()
 
Sheets("Team Dash").Visible = True
Sheets("sm1212").Visible = xlSheetVeryHidden
Sheets("rw1212").Visible = xlSheetVeryHidden

Sheets Environ("Username") = xlSheetVisible

End Sub

thanks,

[Norie]

Have you tried this?

Sheets(Environ("Username")) = xlSheetVisible

[scotlandsneo]

Have you tried this?

Sheets(Environ("Username")) = xlSheetVisible

Thanks for the suggestion By implementing that suggested code i get an error "run-time error '438': object doesn't support this property or method.... not sure where to go with it now lol

>

Private Sub Workbook_Open()
 
Sheets("Team Dash").Visible = True
Sheets("sm1212").Visible = xlSheetVeryHidden
Sheets("rw1212").Visible = xlSheetVeryHidden

Sheets(Environ("Username")) = xlSheetVisible

End Sub

[Norie]

Oops, missed the .Visible bit, which might be a bit important.

Sheets(Environ("Username")).Visible = xlSheetVisible

[scotlandsneo]

Excellentio! it works great thank you !

i was also wondering, how can i make it so that upon wthe workbook opening this macro is automatically ran.. im looking to have it so that when the user opens it only the page permitted to them is viewable. as it currently stands they can see everything until the macro runs lol

[Vraag en antwoord]

To debug you can use this.

Sub VenA()
For Each sh In Sheets
    If sh.Name = Environ("Username") Then
        sh.Visible = True
        MsgBox sh.Name & " Found and is now visible"
        Exit Sub
    End If
Next
MsgBox "Sheets " & Environ("Username") & " Not found"
End Sub

[scotlandsneo]

Thanks for ther code guys, is the below looking good? my next step is automatically having the macro run without being prompted when workbook is opened. is this possible?

Private Sub Workbook_Open()
 
Sheets("Team Dash").Visible = True
Sheets("sm1212").Visible = xlSheetVeryHidden
Sheets("rw1212").Visible = xlSheetVeryHidden

Sheets(Environ("Username")).Visible = xlSheetVisible

End Sub
Sub VenA()
For Each sh In Sheets
    If sh.Name = Environ("Username") Then
        sh.Visible = True
        Msgbox sh.Name & " Found and is now visible"
        Exit Sub
    End If
Next
Msgbox "Sheets " & Environ("Username") & " Not found"
End Sub

[Norie]

What are the users being prompted for?

[scotlandsneo]

What are the users being prompted for?

So when the workbook opens i want to make it so that only the sheet name that matches their windows ID and the sheet called Team Dash is showing, but i need it done autmoatically because it's going to have my teams stats in the workbook and i dont want them viewing each others pages, only their own and the overall team dash. (saves office politics around embarrassment of poor stats etc)

[Norie]

Sorry I'm a bit confused, if you want code to run when a workbook opens you put it in the workbook's Open event and you've just posted code for that.

[scotlandsneo]

omg!! sory for the confusion, i hadn't set the worksheet from General to Workbook .. that solved the problem lol you guys were awesome thanks for the help!!

[scotlandsneo]

Ah ok, then something isn't working.. when i switch to my colleagues computer to open the sheet after i save it, the macro does not run and all sheets are visible until i manually go in and run the macro lol

[Vraag en antwoord]

Try this in your workbook_open

For Each sh In Sheets
    If sh.Name = "Team Dash" Or sh.Name = Environ("Username") Then sh.Visible = True Else sh.Visible = False
Next sh

[scotlandsneo]

this code works perfectly however - all sheets are visible until the excel documents " enable content" has been clicked when it pops up, on the occasion where this does not pop up the macro runs perfectly, but i have no idea how to stop them being bale to click on all the sheets until the "enable content" has been clicked any ideas?

[Vraag en antwoord]

Try this link https://www.youtube.com/watch?v=VvtQsw5-BFw

[dipique]

Can you hide all the sheets by default (except the dashboard) and display the user's page when macros are enabled?

[scotlandsneo]

great work Vraag, in the end i ended up using the below code.

Private Sub Workbook_Open()
On Error Resume Next
For Each sh In Sheets
Dim StrName As String
    StrName = UCase(Environ("UserName"))
        If sh.Name = "Team Dash" Or sh.Name = StrName Then sh.Visible = True Else sh.Visible = xlSheetVeryHidden
Next sh
End Sub

[scotlandsneo]

Ended up with this code instead Vraag, any idea's on how to have it recognise my ID only to unhide all sheets on open without it leaving the sheets open on any save?

Private Sub Workbook_Open()
On Error Resume Next
For Each sh In Sheets
Dim StrName As String
    StrName = UCase(Environ("UserName"))
        If sh.Name = "Team Dash" Or sh.Name = StrName Then sh.Visible = True Else sh.Visible = xlSheetVeryHidden
Next sh
End Sub

[Vraag en antwoord]

try this

Private Sub Workbook_Open()
Dim StrName As String
    StrName = UCase(Environ("UserName"))
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each sh In Sheets
        If StrName = UCase("my username") Then
            sh.Visible = True
        Else
            If sh.Name = "Team Dash" Or UCase(sh.Name) = StrName Then sh.Visible = True Else sh.Visible = xlSheetVeryHidden
        End If
    Next sh
    Application.ScreenUpdating = False
End Sub