PDA

View Full Version : Solved: macro to hide/unhide select cells



joeyc
07-06-2008, 06:54 PM
Hi,

In Cell S3 of Worksheet1, the user has a choice of "Yes" and "No" from a drop-down menu. If the user selects "Yes" to this question, I would like the following rows on Worksheet2 to become unhidden: 54-66, 119-132, and 183-195. If the user selects "No" to this question, I would like the following rows on Worksheet2 to become hidden: 54-66, 119-132, and 183-195.

Now, I would like others cells on other worksheets to become hidden/unhidden as well but I reason if can get help with this I can do the rest.

Thank you in advance for any help.

mdmackillop
07-06-2008, 11:33 PM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$S$3" Then Exit Sub
With Sheets(2)
If Target = "Yes" Then
.Rows("54:66").Hidden = False
.Rows("119:132").Hidden = False
.Rows("183:195").Hidden = False
Else
.Rows("54:66").Hidden = True
.Rows("119:132").Hidden = True
.Rows("183:195").Hidden = True
End If
End With
End Sub

joeyc
07-07-2008, 12:00 AM
Let me have a look at this. The code looks simple enough. Let me use it and modify it to my liking. I will be back.

joeyc
07-07-2008, 01:14 AM
I took the code you gave me and I edited it. In reality, I have a drop down box in S3 on Sheet1 where if No is selected, I want rows hidden on 3 sheets (4,5, and 6) from the left and if Yes is selected I want all these rows displayed.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' For Staff Member # 1
If Target.Address <> "$S$3" Then Exit Sub
With Sheets(6)
If Target = "Yes" Then
.Rows("41:53").Hidden = False

Else
.Rows("41:53").Hidden = True

End If
End With

With Sheets(5)
If Target = "Yes" Then
.Rows("41:53").Hidden = False
Else
.Rows("41:53").Hidden = True
End If
End With


With Sheets(4)
If Target = "Yes" Then
.Rows("16:22").Hidden = False
Else
.Rows("16:22").Hidden = True
End If
End With
End Sub

However, I need this done for 2 additional members. S4 and S5 will control if these rows are displayed on the same sheets (4,5, and 6). So I wrote this below. And not a surprise it is not working. I am not integrating the arguments properly.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' For Staff Member # 1
If Target.Address <> "$S$3" Then Exit Sub
With Sheets(6)
If Target = "Yes" Then
.Rows("41:53").Hidden = False

Else
.Rows("41:53").Hidden = True

End If
End With

With Sheets(5)
If Target = "Yes" Then
.Rows("41:53").Hidden = False
Else
.Rows("41:53").Hidden = True
End If
End With


With Sheets(4)
If Target = "Yes" Then
.Rows("16:22").Hidden = False
Else
.Rows("16:22").Hidden = True
End If
End With

' For Staff Member # 2
If Target.Address <> "$S$4" Then Exit Sub
With Sheets(6)
If Target = "Yes" Then
.Rows("119:132").Hidden = False

Else
.Rows("119:132").Hidden = True

End If
End With

With Sheets(5)
If Target = "Yes" Then
.Rows("92:103").Hidden = False
Else
.Rows("92:103").Hidden = True
End If
End With


With Sheets(4)
If Target = "Yes" Then
.Rows("37:43").Hidden = False
Else
.Rows("37:43").Hidden = True
End If
End With


' For Staff Member # 3
If Target.Address <> "$S$4" Then Exit Sub
With Sheets(6)
If Target = "Yes" Then
.Rows("183:195").Hidden = False

Else
.Rows("183:195").Hidden = True

End If
End With

With Sheets(5)
If Target = "Yes" Then
.Rows("143:153").Hidden = False
Else
.Rows("143:153").Hidden = True
End If
End With


With Sheets(4)
If Target = "Yes" Then
.Rows("58:62").Hidden = False
Else
.Rows("58:62").Hidden = True
End If
End With




End Sub

mdmackillop
07-07-2008, 05:13 AM
If I understand correctly, something like

Private Sub Worksheet_Change(ByVal Target As Range)

' For Staff Member # 1
'If Target.Address <> "$S$3" Then Exit Sub

Select Case Target.Address
Case "$S$3"
With Sheets(6)
If Target = "Yes" Then
.Rows("41:53").Hidden = False
Else
.Rows("41:53").Hidden = True
End If
End With
Case "$S$4"
'do other stuff
Case "$S$5"
'do other stuff
Case Else
Exit Sub

End Select
End Sub

joeyc
07-07-2008, 10:14 AM
The code for your last response looks more like it. The select case structure here just seems so much more organized and easier to read.

If I can get Staff Member #1 complete, the rest should follow. How can this code for Sheet 5 and Sheet 4 be incorporated into Case $S$3?

With Sheets(5)
If Target = "Yes" Then
.Rows("41:53").Hidden = False
Else
.Rows("41:53").Hidden = True
End If
End With


With Sheets(4)
If Target = "Yes" Then
.Rows("16:22").Hidden = False
Else
.Rows("16:22").Hidden = True
End If
End With

mdmackillop
07-07-2008, 10:52 AM
Just add your code before Case $S$4

joeyc
07-07-2008, 11:44 AM
Done! Thank you once again!:beerchug:

By the way, this is the final product.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' For Staff Member # 1
'If Target.Address <> "$S$3" Then Exit Sub

Select Case Target.Address
Case "$S$3"
With Sheets(6)
If Target = "Yes" Then
.Rows("54:68").Hidden = False
Else
.Rows("54:67").Hidden = True
End If
End With
With Sheets(5)
If Target = "Yes" Then
.Rows("41:53").Hidden = False
Else
.Rows("41:53").Hidden = True
End If
End With
With Sheets(4)
If Target = "Yes" Then
.Rows("16:22").Hidden = False
Else
.Rows("16:22").Hidden = True
End If
End With

'For Staff Memeber # 2
Case "$S$4"
With Sheets(6)
If Target = "Yes" Then
.Rows("120:132").Hidden = False
Else
.Rows("120:132").Hidden = True
End If
End With
With Sheets(5)
If Target = "Yes" Then
.Rows("92:103").Hidden = False
Else
.Rows("92:103").Hidden = True
End If
End With
With Sheets(4)
If Target = "Yes" Then
.Rows("37:43").Hidden = False
Else
.Rows("37:43").Hidden = True
End If
End With

'For Staff Memeber # 3
Case "$S$5"
With Sheets(6)
If Target = "Yes" Then
.Rows("184:196").Hidden = False
Else
.Rows("184:196").Hidden = True
End If
End With
With Sheets(5)
If Target = "Yes" Then
.Rows("143:153").Hidden = False
Else
.Rows("143:153").Hidden = True
End If
End With
With Sheets(4)
If Target = "Yes" Then
.Rows("58:63").Hidden = False
Else
.Rows("58:63").Hidden = True
End If
End With


Case Else
Exit Sub

End Select

mdmackillop
07-07-2008, 12:01 PM
Hi Joey,
If your code becomes long and repetitive you should consider passing variables to another sub-routine to carry out the repeated tasks. It makes it much easier to follow and to maintain.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' For Staff Member # 1
Select Case Target.Address
Case "$S$3"
DoStuff 6, Target, "54:68"
DoStuff 5, Target, "41:53"
DoStuff 4, Target, "16:22"
End Select
End Sub

Sub DoStuff(Sht As Long, Target As Range, Rws As String)
With Sheets(Sht)
If Target = "Yes" Then
.Rows(Rws).Hidden = False
Else
.Rows(Rws).Hidden = True
End If
End With
End Sub

joeyc
07-07-2008, 12:20 PM
Thanks for the advice. I will look over this.