Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > command button add another command
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 318
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 318
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 318
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Error Message - "Command Text not set for command obje Jeff Divian Excel Discussion (Misc queries) 0 November 7th 07 10:26 PM
VBA Command Button IntricateFool Excel Discussion (Misc queries) 5 December 8th 06 04:39 AM
Command Button Steved Excel Worksheet Functions 2 February 19th 06 11:01 PM
Using a command button to add Chance Excel Discussion (Misc queries) 5 October 21st 05 08:08 PM
Command button Mark New Users to Excel 4 March 10th 05 02:37 PM


All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement