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

[Anshu]

I have a workbook having 7 sheets, named
MS, CWS, 1, 2, 3, 4, 5


On sheet MS there is a table in range C4:C8

I want to hide unhide sheets on the basis of table, except the sheet "CWS"

(Please see the attachment)


here is the code:




Private Sub Worksheet_Change(ByVal Target As Range)

'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


This code can be simplified, but I don't know how.

Would you please help me to do so.

[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

[Anshu]

Sorry that I was unable to convey my message properly.

Please find the attachment below. I have described my case in the attached excel file.

[DocAElstein]

Hi,
Here’s your explanation form your file…_
_..... sheet "1" is made for cell C4 (ABHISHEK), sheet "2" is made for cell C5 (ANCHAL)…………sheet "65" is made for cell C68 (SHAHNAWAZ HUSAIN)
my requirement is:
If I delete the content of any cell, the corresponding sheet should be hide automatically.
Means, if C4 is blank, sheet 1 hides if C5 is blank, sheet 2 hides….
if I select C6:C9 and delete it, the corresponding sheet should be deleted automatically
Also If I fill the cell again, the corresponding sheet should reappear.
If I paste or delete multiple name in one click, still the corresponding sheets should hide or unhide accordingly….

I am slightly confused that you are talking about hiding and deleting sheets. Hiding and deleting are two very different things

For now I will assume that you are meaning just hiding / unhiding..

If my next shot at a solution is not what you want , then explain carefully again what you want. ( But I will not be able to reply for a couple of days, as I am busy elsewhere )



I will answer this question for now
If I delete the content of any cell, the corresponding sheet should be hide automatically.
Means, if C4 is blank, sheet 1 hides if C5 is blank, sheet 2 hides….
if I select C6:C9 and delete the contents, the corresponding sheets should be hidden automatically
Also If I fill the cell again, the corresponding sheet should reappear.
If I paste or delete multiple names in one click, still the corresponding sheets should hide or unhide accordingly


My previous macro will need to have the initial check for multiple cell selection removed.
I think, a Private Sub Worksheet_Change(ByVal Target As Range) is preferable to a Private Sub Worksheet_SelectionChange(ByVal Target As Range)
The other change is not so substantial. The basic logic remains the same . I simply need to loop for each of the cells changed, and apply the same logic for each of those cells


Option Explicit
Private Sub Worksheet_Change(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:C68")) Is Nothing Then Exit Sub
Rem 3 Do it
Dim WsItmNmbr As Long: ' Let WsItmNmbr = Target.Row - 1
Dim RngCel As Range
For Each RngCel In Target
Let WsItmNmbr = RngCel.Row - 1
'hide unhide worksheet
If RngCel.Value <> "" Then
Worksheets.Item(WsItmNmbr).Visible = True
Else
Worksheets.Item(WsItmNmbr).Visible = False
End If
Next RngCel
End Sub


Alan

[Anshu]

Works Perfectly!!

The macro works perfectly in the way I need.

Thanks a lot....and Sorry for the inconvenience caused.