command button add another command

  • Thread starter Thread starter Wanna Learn
  • Start date Start date
W

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
 
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
 
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
 
This code runs when you click the commandbutton1. Did you click that button?
 
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
 
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


Wanna said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

command button commands to much 1
hide unhide rows -Protected sheet 3
option button (yes No) to hide rows 3
Command Button to Hide/Unhide Rows 4
Autoadjust range declaration coding when inserting rows 4
Inserting rows 2
Inserting or Deleting Rows 4
Command Buttons 3

Back
Top