VBA Macro Coding which will take you to the Excel sheet you select from a drop down list on Sheet1.

[Galapagos15]

1. An excelWorkbook has four sheets which are named Sheet1, HR, IT and Finance.
2. Sheet1 has adrop down list in cell A1 which lists the names of the other three sheets whichare the company departments HR, IT and Finance. <o></o>
3. I want theuser to open the Excel Workbook and at first only have access to Sheet1 so the otherthree sheets will be locked. <o></o>
4. On Sheet1 theywill select their department name from the drop down list in cell A1.<o></o>
5. Once theyselect their department from the drop down list on Sheet1 the macro will unlockthat sheet for the user and take the user to that sheet so they can enter information which would be eitherthe HR, IT or Finance sheet. The other two department sheets will remain locked.

<o> </o>
Thank you inadvance!<o></o>

 

[mumps]

Place this macro in the code module for ThisWorkbook:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            ws.Visible = False
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Place this macro in the code module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            ws.Visible = False
        End If
    Next ws
    Sheets(Target.Value).Visible = True
    Sheets(Target.Value).Select
    Application.ScreenUpdating = False
End Sub

The first macro will ensure that only Sheet1 will be visible when the workbook is opened. The second macro will activate the sheet selected in the drop down in A1.

 

[Galapagos15]

Thank you so much! Is there a way that the Macro will still make all Sheets visible but only unlock the one that they select from the drop down list?

 

[mumps]

Replace the previous 2 macros with these:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            ws.Unprotect
            ws.Cells.Locked = True
            ws.Protect
            ws.EnableSelection = xlUnlockedCells
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Sheet1" Then
            ws.Unprotect
            ws.Cells.Locked = True
            ws.Protect
            ws.EnableSelection = xlUnlockedCells
        End If
    Next ws
    Sheets(Target.Value).Unprotect
    Sheets(Target.Value).Select
    Application.ScreenUpdating = False
End Sub

You should start by protecting all the sheets except Sheet1. I have not used a password. If you want to protect the sheets with a password, please let me know and I will modify the macros.

 

[Galapagos15]

Thank you so much for your help. Unfortunately, when I revise the coding and open the file it must be protecting Sheet1 because the drop down list in Cell A1 no longer works.

 

[mumps]

This line of code in both macros:

If ws.Name <> "Sheet1" Then

excludes Sheet1 from being locked so I'm not sure why you are having that problem. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.