Selected Answer
Semaj
In the attached file, I have a sheet called "Document Control" plus three sheets from your file (which are very hidden so must be unhid using VBA).
When the file is opened (macros-enabled), it protects and lists the three sheets, starting from cell A2 of "Document Control" using this event code (with comments):
Private Sub Workbook_Open()
Dim ws As Worksheet, n As Integer
myPwd = "*" ' is variable declared in Module 1
n = 2
Sheet1.Columns(1).ClearContents
' loop through sheets
For Each ws In ThisWorkbook.Sheets
If ws.Name <> Sheet1.Name Then
' add to list on DC sheet, protect and hide
Sheet1.Cells(n, 1) = ws.Name
ws.Protect myPwd
ws.Visible = xlSheetVeryHidden
' increment row counter
n = n + 1
End If
Next ws
Sheet1.Columns(1).AutoFit
End Sub
If you click on a cell with one of the listed sheet names, that sheet is revealed and cell B13 selected (and optionally - lines in bold - written to and that sheet re-protected) using this event code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
' do nothing unless a single cell in column A is picked
If Intersect(Target, Columns(1)) Is Nothing Or Target.Count > 1 Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
If Target.Value <> "" And ws.Name = Target.Value Then
' if sheet found, show with B13 selected
ws.Visible = xlSheetVisible
ws.Unprotect myPwd
ws.Activate
ws.Cells(13, 2).Select
' ### optional write and re-protect
ws.Cells(13, 2).Value = ws.Name & " Activated"
ws.Protect myPwd
End If
Next ws
End Sub
Hope you can modify your code to use this approach but using ComboBox selections. If so, please remember to mark this Answer as Selected.
