unhiding rows automatically

M

Mr. G.

How do you open a (group of) hidden rows automatically upon the User
populating a cell(s) from a drop down list (via data validation)?
 
J

JulieD

Hi

you'll have to use a Worksheet_Change event e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value <> "" Then
Rows("2:20").EntireRow.Hidden = True
Select Case Target.Value
Case "cat"
Rows("2:10").EntireRow.Hidden = False
Case "dog"
Rows("11:15").EntireRow.Hidden = False
Case Else
Rows("16:20").EntireRow.Hidden = False
End Select
End If
End Sub
------
to use this code, right mouse click on the sheet tab with your data
validation drop down box on it and choose view code
copy & paste the code into the right hand side of the screen
.... you'll then need to change target.address to the cell with your data
validation box in it
.....the "cat" and "dog" to actual options in your data validation drop down
box
and the row numbers
then use ALT & F11 to switch back to your worksheet and try it out.
 
M

Mr. G.

Julie,

Thank you for responding. Your macro has gotten me past the first step to
what I'm eventually trying to acheive. Unfortunately, I left out (quite) a
few more functions that I'd like for this macro to do as well. Hopefully,
you can still help. Can this macro be modified to either unhide one row at a
time so that when everytime the User populates a cell, the following row
becomes unhidden. Besides my wksht (entitled "EXP RPT") being protected
(password "lindAP"), I've allocated 7 rows (18-24) for the User to populate,
if necessary. Cells "F18: F24" each containing a drop down list of 7 items
(via data validation). In order to minimize space, I've hidden rows 19:24
and have instructed the User to only unhide these rows as needed. Obviously,
row 18 is always visable when the User opens up this file. My goal is to
have these rows unhide/hide automatically. For example, lets say the User
populates (via drop down list or manual entry) cell "F18", I'd then like for
row 19 to automatically become visable while rows 20:24 remain hidden. Once
again, If the User populates cell "F19", the following row (20) will
automatically become available while rows 21:24 will remain hidden. Now on
the other hand, let's say the User decides to unpopulate cell "F20", I'd like
for this cell and corresponding row to revert back to its hidden state.
However, only under the condition that cell "BC20" (which represents the sum
of cells K20:BB20) equals zero. This will assure me that the hidden rows
from this group of cells equal zero. Now to add even more complexity, this
wksht is linked to another wksht (within the same wkbk) entitled "WKLY RPT".
I'd like for the rows in the "WKLY RPT" wksht to correspond with the
hide/unhide commands reflected in the "EXP RPT" wksht. Sorry for being so
detailed, but I didn't know how else to get information across.

Any assistance would be greatly appreciated!!
 
J

JulieD

Hi

how about
--------
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F18:F24")) Is Nothing Then '1
If Target.Value = "" And Target.Offset(0, 49) = 0 And Not Target.Row
= 18 Then '2
Target.EntireRow.Hidden = True '3
Sheets("WKLY RPT").Rows(Target.Row).EntireRow.Hidden = True '4
End If '5
If Target.Row = 18 And Target.Offset(1, 0).Value = "" And
Target.Offset(1, 49) = 0 Then '6
Target.Offset(1, 0).EntireRow.Hidden = True '7
Sheets("WKLY RPT").Rows(Target.Row + 1).EntireRow.Hidden = True
'8
End If '9
If Target.Offset(1, 0).Value = "" And Target.Offset(1, 49) = 0 Then
'10
Target.Offset(1, 0).EntireRow.Hidden = True '11
Sheets("WKLY RPT").Rows(Target.Row + 1).EntireRow.Hidden = True
'12
End If '13
If Target.Value <> "" Then '14
Target.Offset(1, 0).EntireRow.Hidden = False '15
Sheets("WKLY RPT").Rows(Target.Row + 1).EntireRow.Hidden = False
'16
End If '17
End If '18
End Sub

---
i've placed a row number at the end of each row so that if the ng wraps the
code you can figure out what goes with what, so when you copy & paste the
code, each row should have a green number at the end of it

note this code assumes rows 19 through 24 hidden in both sheet to start with
......
 
M

Mr. G.

Hi Julie,

Thanx for your prompt response. Unfortunately, I'm having a few problems.
Per you instructions, rows 19-24 are hidden. However, when I select an item
from the drop down list in cell F18, rather than row 19 becoming available
I'm getting the following mesage:
"Run-time error '1004': Unable to set the Hidden Property of the Range Class"
And when I delete the item from cell F18, I'm getting the following mesage:
"Run-time error '13': Type mismatch"'
A few things to keep in mind (and I'm not sure if they even matter):
1) My "WKLY RPT" wksht is hidden and protected.
2) Does it matter whether or not I remove the green row numbers?
3) Was I suppose to incorporate this macro with the previous one that you
sent? If so, can you tell me how?
And finally (and I'm afraid to ask, but here it goes anyways), once we
resolve this problem would it be to much trouble to set-up the rows 32-34 and
46-56 in the same manner?

Thank you,
 
J

JulieD

Hi Mr G
1) My "WKLY RPT" wksht is hidden and protected.
Click to expand...
.... that makes a difference ... will need to rework the code ..... see after
'1 and before '18

'new line to be added in
Sheets("WKLY RPT").unprotect ("pwd")

'new line to be added in
Sheets("WKLY RPT").protect ("pwd")

----
the "pwd" is the password you protect / unprotect the sheet with ... if you
don't use a password just omit the ("pwd") bit entirely.

2) Does it matter whether or not I remove the green row numbers?
Click to expand...
... nope as long as none of the lines go red you're fine
3) Was I suppose to incorporate this macro with the previous one that you
sent? If so, can you tell me how? --- nope, ditch the first one
And finally (and I'm afraid to ask, but here it goes anyways), once we
resolve this problem would it be to much trouble to set-up the rows 32-34
and
46-56 in the same manner?
Click to expand...
-- hey why not :) ... but let's get this one up & running first
 
M

Mr. G.

Hi JulieD,

I'm still getting the same error messages. Would it be OK if I e-mailed my
worksheet to you? Therefore, you could maybe identify my problem.

Thank you,
 
J

JulieD

Hi Mr G

my mail server is down at the moment, trying to get on to isp to see what's
going on but as it's 11:45pm here i'm not sure i will be able to resolve
this until after work tomorrow. but i will look at it as soon as i am able.
 
J

JulieD

Hi

mail server back up, but haven't received your email, please resend to
julied_ng at hcts dot net dot au
 
Top