Need a shorter VBA code: Hide and unhide sheets based on presence of name in list

[DocAElstein]

Hi

I do think you can simplify this much, but I would do it a little bit different


_1 ) I assume you do not intend selecting or changing more than one cell at a time. But if you do that by accident then sometimes that can give unexpected results. One way to avoid such problems is as follows:
VBA tells us the range that was selected by returning that range as range object in the range object variable, Target. The .Value Property applied to a range object, will either return a single value, or an array of values if the range object contains more than one value. So we can check for that, and exit the procedure for a multi cell selection
_2) It is a good idea to initially have a code part which restrict the macro from running most of the coding if you are not selecting in your range of interest, C5 – C8. We can use the VBA Intersect function for this. Once again we use the Target range along with the range of interest to us, Range("C5:C8"). If we include these two ranges in the ( arguments , , , , ) of Intersect, then Intersect will try to return a range object corresponding to where they overlap. If they do not overlap, then we did not select within , C4 – C8 . In such a case, VBA Intersect function does not error, - it returns no range, which VBA sees as Nothing
So we can check for that

_3)
Having done that, the only simplifications that I can think of are minimal.
Once again I assume you are only selecting a single cell at any time. So The macro I have modified to just look at the value of the cell that you selected.
We can select worksheets by their item number, which is the integer number counting from the left. We can determine the item number related to the row number of your selection quite easily.
For example ,
C4 has row 4. You want to check worksheets item 3 for that, ( I think that is your sheet name 1, possibly? ) so that item number is the (row number -1)


Alan

P.S. You might want to consider if Private Sub Worksheet_Change(ByVal Target As Range) works better than Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rem 1 Exit sub if more than one cell selectd
    If IsArray(Target.Value) Then Exit Sub
Rem 2 Exit sub if we do not select inside our range of interest
    If Application.Intersect(Target, Me.Range("C4:C8")) Is Nothing Then Exit Sub
Rem 3 Do it
Dim WsItmNmbr As Long: Let WsItmNmbr = Target.Row - 1
'hide unhide worksheet
     If Target.Value <> "" Then
        Worksheets.Item(WsItmNmbr).Visible = True
     Else
        Worksheets.Item(WsItmNmbr).Visible = False
     End If



''hide unhide sheet 1
'     If [C4] <> "" Then
'        Sheet3.Visible = True
'     Else
'        Sheet3.Visible = False
'     End If
'
'   'hide unhide sheet 2
'     If [C5] <> "" Then
'        Sheet4.Visible = True
'     Else
'        Sheet4.Visible = False
'     End If
'
'   'hide unhide sheet 3
'     If [C6] <> "" Then
'        Sheet5.Visible = True
'     Else
'        Sheet5.Visible = False
'     End If
'
'   'hide unhide sheet 4
'     If [C7] <> "" Then
'        Sheet6.Visible = True
'     Else
'        Sheet6.Visible = False
'     End If
'
'   'hide unhide sheet 5
'     If [C8] <> "" Then
'        Sheet7.Visible = True
'     Else
'        Sheet7.Visible = False
'     End If
'
End Sub