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,
"JulieD" wrote:
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
......
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Mr. G." wrote in message
...
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!!
"JulieD" wrote:
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.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Mr. G." wrote in message
...
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)?