December 3, 2023


Welcome to the core

Show and hide sheets based on user

This is a handy bit of code for a simple show and hide command, using the “Windows Username in a cell” code with this show/hide code is a great feature for any workbook used by a teams or departments that have different jobs within the workbook.

This is a private sub and will activate on change of a cell, and the cell changes from the code when the workbook is opened, will show the 2 sheets (Superdata and normaldata) to the user.

Parts that need to changed are in bold.

Code —-

Private Sub worksheet_change(ByVal target As Excel.Range)
Select Case Worksheets(“Front Sheet”).Range(“C1”).Value
Case “User1”, “User2”
Worksheets(“Superdata”).Visible = True
End Select
End Sub

FrontPage – The worksheet that the parameter is held (Username or a date can be used)
C1 – the cell where the data/value is held
User1 User 2- This example uses windows login name – so this would be the username
Superdata – The sheet that will be unhidden for this user/s
True – If this value is set to “True” the sheet will be shown to the user / If you change this value to “False” it will hide the “Superdata” sheet from the user.

Very handy for an input workbook used by many different roles and users

Hope this is clear!
Copyright © All rights reserved. | Newsphere by AF themes.