My 1st IF-THEN-ELSE Procedure

A

AMY Z.

Hi,
This is what I'm trying to do: It is in Worksheet2. I would like it to run
when cell A1 changes:

If cell A1 says Apples then hide rows 10 & 20.
If A1 says Pears then hide rows 15 & 25.
If A1 says Oranges then hide rows 30 & 35.

If A1 doesn't say any of the above then Unhide all rows (to reset) and hide
rows 12 & 16. (This is Default for all other Fruits).


This is what I wrote but it won’t work. I’m not getting any code errors, but
it just sits there.

If Worksheets("Sheet2").Range("A1").Text = "Apples" Then
Rows("10:10").Select
Selection.EntireRow.Hidden = True
Rows("20:20").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Pears" Then
Rows("15:15").Select
Selection.EntireRow.Hidden = True
Rows("25:25").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Oranges" Then
Rows("30:30").Select
Selection.EntireRow.Hidden = True
Rows("35:35").Select
Selection.EntireRow.Hidden = True
Else: Worksheets ("Sheet2")
Rows("1:35").Select
Selection.EntireRow.Hidden = False
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("16:16").Select
Selection.EntireRow.Hidden = True
End If

End Sub

Should I put this under the General section of the Sheet2 Object in the VB
editor?
This is the first procedure that I’ve tried to write.
Thank for your time,
Amy
 
D

davesexcel

Assuming the code works I would place it into a worksheet module
right click on sheet 2 tab and select view code

in the dropdown box select worksheet
the next box select change

enter your code in there, it will look like this with the target as A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Worksheets("Sheet2").Range("A1").Text = "Apples" Then
Rows("10:10").Select
Selection.EntireRow.Hidden = True
Rows("20:20").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Pears" Then
Rows("15:15").Select
Selection.EntireRow.Hidden = True
Rows("25:25").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Oranges" Then
Rows("30:30").Select
Selection.EntireRow.Hidden = True
Rows("35:35").Select
Selection.EntireRow.Hidden = True
Else: Worksheets("Sheet2").Select
Rows("1:35").Select
Selection.EntireRow.Hidden = True
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("16:16").Select
Selection.EntireRow.Hidden = True
End If

End If
End Su
 
J

Jim Cone

Hello Amy,
You are very close. The code belongs in the Sheet2 module.
It is "event" driven code.
When any cell on the sheet is changed the code will run.
However, the code checks if cell A1 is the changed cell
(the target cell) and if not the code exits...
'---------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If Target.Text = "Apples" Then
Rows("10:10").EntireRow.Hidden = True
Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
Rows("15:15").EntireRow.Hidden = True
Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
Rows("30:30").EntireRow.Hidden = True
Rows("35:35").EntireRow.Hidden = True
Else
Rows("1:35").EntireRow.Hidden = False
Rows("12:12").EntireRow.Hidden = True
Rows("16:16").EntireRow.Hidden = True
End If
End If

End Sub

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"AMY Z." <[email protected]>
wrote in message
Hi,
This is what I'm trying to do: It is in Worksheet2. I would like it to run
when cell A1 changes:
If cell A1 says Apples then hide rows 10 & 20.
If A1 says Pears then hide rows 15 & 25.
If A1 says Oranges then hide rows 30 & 35.
If A1 doesn't say any of the above then Unhide all rows (to reset) and hide
rows 12 & 16. (This is Default for all other Fruits).
This is what I wrote but it won’t work. I’m not getting any code errors, but
it just sits there.

If Worksheets("Sheet2").Range("A1").Text = "Apples" Then
Rows("10:10").Select
Selection.EntireRow.Hidden = True
Rows("20:20").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Pears" Then
Rows("15:15").Select
Selection.EntireRow.Hidden = True
Rows("25:25").Select
Selection.EntireRow.Hidden = True
ElseIf Worksheets("Sheet2").Range("A1").Text = "Oranges" Then
Rows("30:30").Select
Selection.EntireRow.Hidden = True
Rows("35:35").Select
Selection.EntireRow.Hidden = True
Else: Worksheets ("Sheet2")
Rows("1:35").Select
Selection.EntireRow.Hidden = False
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("16:16").Select
Selection.EntireRow.Hidden = True
End If

End Sub

Should I put this under the General section of the Sheet2 Object in the VB
editor?
This is the first procedure that I’ve tried to write.
Thank for your time,
Amy
 
D

Don Guillett

you may find this idea useful to combine

Sub hidenoncontingentrows()
Range("a1,a3,a5").EntireRow.Hidden = True
End Sub
 
A

AMY Z.

Thanks Dave & Jim for answering my question and taking time to explain this.
It works!
Well sort of.....
Something I didn't think about, Cell A1 in Sheet2 is actually linked to Cell
A1 in Sheet1. Then it doesn't work.

I tried setting the same procedure up in Sheet1 and added a line at the
start of each IF block that says: "Sheets("Sheet2").Select". But it still
hides the rows in Sheet1.

Should I leave the procedure in Sheet2 the way it was, and somehow change
the Target.Address line to: Sheet1 "A1"?
If it is the Target.Address line, could you help with the syntax to set it
up? I can't find an example in any of my books that refers the Target.Address
for another worksheet. Or do I have to start from scratch.

Thank you again for your time. I'm excited that I have at least got the
procedure to work, except for the linkage problem.
 
J

Jim Cone

Events would be the word to look up not target.
You are using the Worksheet Change event.
In the sheet module at the top are two drop down boxes.
Set the left box to "Worksheet" and then look at the items displayed
in the right box. It shows all the events applicable to that sheet.
Click one and see what happens.

Remove the code from the Sheet2 module and place the following
code in the Sheet1 module...
(notice the dots in front of Rows)
'---------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If Target.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"AMY Z." <[email protected]>
wrote in message
Thanks Dave & Jim for answering my question and taking time to explain this.
It works!
Well sort of.....
Something I didn't think about, Cell A1 in Sheet2 is actually linked to Cell
A1 in Sheet1. Then it doesn't work.

I tried setting the same procedure up in Sheet1 and added a line at the
start of each IF block that says: "Sheets("Sheet2").Select". But it still
hides the rows in Sheet1.
Should I leave the procedure in Sheet2 the way it was, and somehow change
the Target.Address line to: Sheet1 "A1"?
If it is the Target.Address line, could you help with the syntax to set it
up? I can't find an example in any of my books that refers the Target.Address
for another worksheet. Or do I have to start from scratch.
Thank you again for your time. I'm excited that I have at least got the
procedure to work, except for the linkage problem.
 
A

AMY Z.

Thanks for the tip Don. I think it will help me consolidate my code.

I'm still green on whether my procedure will work because of the linkage
problem with the Target Cell on Sheet1.

Amy
 
A

AMY Z.

You guys might laugh at me, but to get around the linkage problem, I set up
an IF Change procedure in Sheet1 A1 that copies and pastes to Sheet2 A1.
This eliminates the linkage in the background of Sheet2 A1. Now the IF Change
procedure in Sheet2 works. This seems sort of over coding to do it this way,
but it's the only way I know right now. I'm just learning.
Thanks for everyone taking time to help me.
Amy
 
A

AMY Z.

Thanks Jim! It works great!
I missed your answer yesterday. Thanks for the Event tip too. I'm going to
study it.
Amy
 

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

Macro Trigger 10
Option Box on user from to update workbook 6
Code to hide rows if cell in another WS is ="No" 11
Hidden Rows not working correctly 2
If then else if problem for DA 3
Error with toggle code 2
Data Validation List Hide Unhide Rows 5
Hiding Rows and Columns in code 5

Top