select case

[Hein]

Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

 

[Don Guillett]

As ALWAYS, post YOUR code for comments

 

[Don Guillett]

Why did you RE post?

 

[JH]

Hi,
Can you not protect the worksheet with the option of format rows
allowed, this way rows can not be inserted, but rows can be hidden.

JH

 

[Hein]

Under the impression the original wasn't posted. However found it now.

The user must have access to insert rows.

The code that is currently there is as follows:
Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit Sub
Select Case Target.Value

Case "yes"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Select

End Sub

Effectively it should probably run with a loop whereby the range B3 will
adjust depending on the amount of rows inserted and the rows selections in
the case selection must also adjust depending on the rows inserted.

Thanks

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Hein]

Thanks

It works with naming the ranges.
Hein

The user must have access to insert rows.

The code that is currently there is as follows:
Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "yes"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Select
End Sub

Effectively it should probably run with a loop whereby the range B3
will
adjust depending on the amount of rows inserted and the rows selections
in
the case selection must also adjust depending on the rows inserted.

First, and as an aside, your select case code can be shorter:
Application.ScreenUpdating = False
Select Case Target.Value
Case "yes": Rows("10:20").Hidden = False
Case "no": Rows("10:20").Hidden = True
End Select
Application.ScreenUpdating = True

Second, try setting up a named range which, to some extent, will resize
itself when rows are inserted and deleted. Eg. set up a named range
called *TheRange* (Here, you should select a block of cells spanning
rows 10 to 20 inclusive. The number of columns you choose to include in
the range is up to you. Bear in mind that if you choose to make it only
one column wide and a user at some stage might delete that column, you'd
lose your named range, so include several columns which are never going
to be deleted - or select entire rows to be the named range.)
BTW, to name a _new_ range, it's quickest to select that range then
type the name you choose (-TheRange -in the example below) in the -Name
Box- which is usually leftmost in the -Formula bar.-
Then replace your -select case- code with the following:
Application.ScreenUpdating = False
Select Case Target.Value
Case "yes": Range("TheRange").EntireRow.Hidden = False
Case "no": Range("TheRange").EntireRow.Hidden = True
End Select
Application.ScreenUpdating = True
There are a few caveats:
Inserting rows in the middle rows is always OK
Inserting rows at the bottom is OK, but inserting row(s) at row 1 of
the range doesn't expand the range.

Experiment on a blank sheet on which has this code behind it (after
you've set up a named range, say B10:B20, called TestRange in it):
Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Range("TestRange").Interior.ColorIndex = 3
End Sub
This will re-highlight the named range everytime you make
changes to the sheet.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=155819

Microsoft Office Help

.