command button add another command

[Wanna Learn]

Hello Below is the code for a command button I Have. I have sevaral of
these command buttons in the file.
Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("a36:V70")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

To this command button I want to add another command which is if Cell A
70 = Non-Standard (this is from a drop down list) then unhide rows 71 thru
79. Thanks in advance

 

[Dave Peterson]

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("a36:V70")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

Me.Range("a71:a79").EntireRow.Hidden _
= not (CBool(LCase(Me.Range("a70").Value) = LCase("non-standard")))
End Sub

 

[Wanna Learn]

Thanks Dave I know that your answer is correct, as always, but I am doing
something wrong.
Here is what I now have
Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("a36:W70")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

Me.Range("a71:a79").EntireRow.Hidden _
= Not (CBool(LCase(Me.Range("b70").Value) = LCase("non-standard")))
End Sub

Let me cllarify when a person selects command button 1 it unhides row A36
thru W70.

IN cell B70 I have a dropdown list , choices are Standard or Non-Standard.
row 71 thru 79 are hidden.
"Standard" Option always shows but if a person selects "Non Standard" then
I want rows 71 thru 79 to be unhiidden. I copied your formula making the
adjustment to say B70 . If I select non- standard from the drop down box; it
does not give me an error message and row 71 thru 79 are still hidden Again
Many many thanks

 

[Dave Peterson]

This code runs when you click the commandbutton1. Did you click that button?

 

[Wanna Learn]

Dave
Again Thanks I understand. It does work if I click the command button .
Your are correct . But I realize this does not do what I need. So this is
what I did. Now this works for the B70 section but If I try it for
B116 it does not work. (If this works , I have to repeat this in the
wooksheet several times)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$70" Then Exit Sub
If Target.Value = "Non-Standard" Then
Rows(71).Hidden = False
Rows(72).Hidden = False
Rows(73).Hidden = False
Rows(74).Hidden = False
Rows(75).Hidden = False
Rows(76).Hidden = False
Rows(77).Hidden = False
ElseIf Target.Value = "Standard" Then
Rows(71).Hidden = True
Rows(72).Hidden = True
Rows(73).Hidden = True
Rows(74).Hidden = True
Rows(75).Hidden = True
Rows(76).Hidden = True
Rows(77).Hidden = True

Else
If Target.Address <> "$B$116" Then Exit Sub
If Target.Value = "Non-Standard" Then
Rows(117).Hidden = False
Rows(118).Hidden = False
Rows(119).Hidden = False
Rows(120).Hidden = False
Rows(121).Hidden = False
Rows(122).Hidden = False

ElseIf Me.Range("$B$116") = "Standard" Then
Rows(117).Hidden = True
Rows(118).Hidden = True
Rows(119).Hidden = True
Rows(120).Hidden = True
Rows(121).Hidden = True
Rows(122).Hidden = True


End If
End If

End Sub

 

[Dave Peterson]

Maybe...

If those cells always control the next 6 rows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("b70,B116,B162")

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, myRng) Is Nothing Then
Exit Sub
End If

Target.Offset(1, 0).Resize(6, 1).EntireRow.Hidden _
= CBool(LCase(Target.Value) = LCase("standard"))

End Sub

Dave
Again Thanks I understand. It does work if I click the command button .
Your are correct . But I realize this does not do what I need. So this is
what I did. Now this works for the B70 section but If I try it for
B116 it does not work. (If this works , I have to repeat this in the
wooksheet several times)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$70" Then Exit Sub
If Target.Value = "Non-Standard" Then
Rows(71).Hidden = False
Rows(72).Hidden = False
Rows(73).Hidden = False
Rows(74).Hidden = False
Rows(75).Hidden = False
Rows(76).Hidden = False
Rows(77).Hidden = False
ElseIf Target.Value = "Standard" Then
Rows(71).Hidden = True
Rows(72).Hidden = True
Rows(73).Hidden = True
Rows(74).Hidden = True
Rows(75).Hidden = True
Rows(76).Hidden = True
Rows(77).Hidden = True

Else
If Target.Address <> "$B$116" Then Exit Sub
If Target.Value = "Non-Standard" Then
Rows(117).Hidden = False
Rows(118).Hidden = False
Rows(119).Hidden = False
Rows(120).Hidden = False
Rows(121).Hidden = False
Rows(122).Hidden = False

ElseIf Me.Range("$B$116") = "Standard" Then
Rows(117).Hidden = True
Rows(118).Hidden = True
Rows(119).Hidden = True
Rows(120).Hidden = True
Rows(121).Hidden = True
Rows(122).Hidden = True


End If
End If

End Sub