command button add another command
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 |
command button add another command
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 wrote: 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 |
command button add another command
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" wrote: 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 wrote: 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 |
command button add another command
This code runs when you click the commandbutton1. Did you click that button?
Wanna Learn wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
command button add another command
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" wrote: This code runs when you click the commandbutton1. Did you click that button? Wanna Learn wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
command button add another command
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 Learn wrote: 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" wrote: This code runs when you click the commandbutton1. Did you click that button? Wanna Learn wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 11:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com