Excell - Hiding Unhiding rows - 2 conditions 1

[blazblaz]

The following program will hide and unhide some rows depending from value in field "num" (Nice program from Dave W.)

Private Sub Worksheet_Change(ByVal num As Range)
'1 = unhide rng_1 and hide rng_2
'2 = hide rng_1 and unhide rng_2
'anything else, hide rng_1 and rng_2
If num.Address <> Range(&quot;num&quot.Address Then Exit Sub
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
If num = 1 Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
ElseIf num = 2 Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = False
ElseIf num > 2 Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
ElseIf num < 1 Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
ElseIf num = &quot;&quot; Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
pplication.ScreenUpdating = True
End If
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub

I have in same worksheet another variable field, called &quot;typ&quot;. I want to hide and unhide some other rows depending from value in &quot;typ&quot;, indipendently from &quot;num&quot;
Can I define in VBA Project this second condition for hiding-unhiding rows, and how to do it?

 

[BlackburnKL]

First, let me suggest a minor cleanup of your existing code. My suggestion is shown below. What it does is exactly what you describe in the first three (commented) lines. A Select Case command can take the place of multiple If commands. It evaluates the value of the condition (num in this case) and executes the code which follows the matching value. The Case Else command handles all other values.

Private Sub Worksheet_Change(ByVal num As Range)
'1 = unhide rng_1 and hide rng_2
'2 = hide rng_1 and unhide rng_2
'anything else, hide rng_1 and rng_2
If num.Address <> Range(&quot;num&quot.Address Then Exit Sub
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
Select Case num
Case 1
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
Case 2
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = False
Case Else
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
End Select
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub

As for the 'typ' variable, I would handle it the same way. I would create a Sub with a Select Case command for the values you want to test for. BlackburnKL

 

[blazblaz]

BlackburnKL

Thanks for your suggestion for clean-up, it's working. But you still did not answer my question. I want to use in same worksheet 2 different changes (hiding and unhiding rows) changing 2 different fields (&quot;num&quot; and &quot;typ&quot Changing &quot;num&quot; wil hide-unhide rng_1 adn rng_2, changing &quot;typ&quot; wil hide-unhide rng_3 and rng_4.
I am not VB programmer, but I tried to define 2 changes between :
Private Sub Worksheet_Change(ByVal num As Range)
--------
--------
Endsub
as I understand here you can not control 2 fields changes.
I tried to open another sub defining changes for &quot;typ&quot;:
Private Sub Worksheet_Change(ByVal typ As Range)
----
----
Endsub

but it does not work, says &quot;ambiguous name detected:Worksheet_Change&quot;.
Can somebody tell me how to program 2, or more changes hiding-unhiding in one worksheet?

 

[xlbo]

You can't have 2 worksheet_change subs for the same worksheet - hence the &quot;ambiguous name detected&quot; message
you need to introduce a test into your code that decides whether it is TYP or NUM that has been changed:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NumTest as Boolean
'1 = unhide rng_1 and hide rng_2
'2 = hide rng_1 and unhide rng_2
'anything else, hide rng_1 and rng_2
If target.Address = Range(&quot;num&quot.Address then
NumTest = true
elseif target.address = range(&quot;Typ&quot.address then
NumTest = false
else
exit sub
end if
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
Select Case target.value
Case 1
if NumTest = true then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
else
Range(&quot;rng_3&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_4&quot.Select
Selection.EntireRow.Hidden = True
end if
Case 2
if NumTest = true then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = False
else
Range(&quot;rng_3&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_4&quot.Select
Selection.EntireRow.Hidden = False
end if
Case Else
if NumTest = true then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = True
else
Range(&quot;rng_3&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_4&quot.Select
Selection.EntireRow.Hidden = True
end if
End Select
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub

The TYP conditions that I have introduced are identical to the NUM ones except for the ranges they reference
HTH

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[BlackburnKL]

xlbo is right. I missed the name of the sub. Sorry about that. BlackburnKL

 

[blazblaz]

Thanks Geoff for your program, it worth a star.
I tried to implement this program, in case if I have more then 2 variable fields
( example num. typ, and desc), and each variable can have values 1,2,3 which will manage to hide-unhide ranges from rng_01 to rng_23 .
(If num=1 unhide rng_01, hide rng_02, rng_03 )
(If num=2 unhide rng_02, hide rng_01,rng_03 )
(If num=3 unhide rng_03, hide rng_01,rng_02 )
(anything else hide rng_01,rng_02,rng_03 )
Similar thing for the values for Typ and Desc:

num-> 1. rng_01, 2.rng_02, 3.rng_03
typ -> 1. rng_11, 2.rng_12, 3.rng_13
desc-> 1. rng_21, 3.rng_22, 3.rng_23

I can not use Num Test as Boolean, I have more then 2 values for it. I tried to use the variable Num Test as an Integer and to use Case commands into case commands, but just messed up did not get the right result. Can somebody show me how to do it based on XLBO program?
Thanks

 

[xlbo]

I think that with 3 conditions, it would be easier to split this code into 4 seperate subs

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Select case target.address
case range(&quot;num&quot.address
call NumChange
case range(&quot;typ&quot.address
call TypChange
case range(&quot;desc&quot.address
call DescChange
case else
exit sub
end select
end sub

Then, create 3 normal subs called NumChange, TypChange and DescChange

These subs would look like the one that BlackburnKL provided
but you would leave out the
If num.Address <> Range(&quot;num&quot.Address Then Exit Sub
line as you wouldn't need to check it
All you then need to do is use
Select Case Target.value
and adjust the range refs to be hidden / unhidden Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[blazblaz]

Geoff

I 've copied you suggestion to my file, and wrote one of
the subs (NumChange) you suggested, still does not work, got a error: Run Time Error &quot;1004&quot; Method 'Range' of Object_Worksheet' Failed. My first sub looks like the following: (I must made some error)

Sub NumChange(ByVal num As Range)
'1 = unhide rng_01 and hide rng_02
'2 = unhide rng_02 and hide rng_01
'anything else, hide rng_01 and rng_02
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
Select Case num
Case 1
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = True
Case 2
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = False
Case Else
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = True
End Select
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub


What's wrong?

 

[Gavona]

I haven't followed through all the code but it sounds to me as if the problem could best be approached using Advanced Filters.

If you need to filter (=hide rows)in different ways on different occaisions then name the ranges containing your alternative criteria, (consider putting these on a separate worksheet)...
..and consider if it is worthwhile having a simple macro to apply the advance filtering (do it manually with macro recorder on)

 

[xlbo]

what line does it error on ?? Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[blazblaz]

Geoff
I tried to change even names of fields to avoid the troubles, this is the last I had in the program:
-----------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Select Case Target.Address
Case Range(&quot;num1&quot.Address
Call No1Change
Case Range(&quot;num2&quot.Address
Call No2Change
Case Range(&quot;num3&quot.Address
Call No3Change
Case Else
Exit Sub
End Select
End Sub
------------
Sub No1Change()
'1 = unhide rng_01 and hide rng_02
'2 = unhide rng_02 and hide rng_01
'anything else, hide rng_01 and rng_02
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
If num1 = 1 Then
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = False
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = True
ElseIf num1 = 2 Then
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = False
Else
Range(&quot;rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_02&quot.Select
Selection.EntireRow.Hidden = True
End If
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub

-------
Sub No2Change()
..
..
Endsub
--------
Sub No3Change()
..
..
Endsub
-----


When I click on num1 to enter the value, the program sudenly starts to hide and unhide rows continuously, so I have to stop it with Esc. With Debug I am finding the very first line marked:
&quot;Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

What's wrong? Do I need to define more parameters in Subs?
I purchased VBA programming book yesterday, it's time to learn more about programming.

 

[xlbo]

I think the problem is that the initial sub is in the SELECTION change event - therefore, whenever a different range is selected, the event fires.
The code should be placed in the Worksheet_CHANGE event which will only fire when a value on the sheet changes Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[blazblaz]

Geoff

It Looks I am lost, spent whole day working on this program.As you told I made just a sub worksheet_change.
but just does not work. Made 100 different scenarios, got 100 errors, or no errors but nothing happened with hiding unhiding. Maybe I try to put a new thread, with a description of my problem, somebody could help me:
- I have 3 fields, num1, num2, num 3
- Depending from value in this field, 1,2,or 3, i will
hide unhide 3 ranges rows, for each
(If num=1 unhide rng_01, hide rng_02, rng_03 )
(If num=2 unhide rng_02, hide rng_01,rng_03 )
(If num=3 unhide rng_03, hide rng_01,rng_02 )
(anything else hide rng_01,rng_02,rng_03 )

same thing for num=2,3 just different ranges:
num2-> 1.rng_11, 2.rng_12, 3.rng_13
num3-> 1.rng_21, 2.rng_22, 3.rng_23

I am still not familiar with these events, modules, though started to read a VBA programming book. I really want to fix this problem. It's possible that I just did not write correctly some variables in the program.

 

[Gavona]

So, you want to:
Hide Rng_01 unless Num1 = 1
Hide Rng_02 unless Num1 = 2
Hide Rng_03 unless Num1 = 3
Hide Rng_11 unless Num2 = 1
Hide Rng_12 unless Num2 = 2
Hide Rng_13 unless Num2 = 3
Hide Rng_21 unless Num3 = 1
Hide Rng_22 unless Num3 = 2
Hide Rng_23 unless Num3 = 3
Show all other ranges

&quot;In that case I have a solution for you. It involves inserting two columns (which would be hidden in the operational worksheet).

The first identifies the range, containing values such as Rng_01, Rng_02, there must be a value in each row that you wish to selectively hide.

The second (which has a heading &quot;&quot;HIDE?&quot;&quot contains a formula (eg for row 20):&quot;
&quot;=IF(ISERR(INDIRECT(A20)),&quot;show&quot;,IF(INDIRECT(A20)=VALUE(RIGHT($A20,1)),&quot;show&quot;,&quot;hide&quot)
The formulae and the heading form the named range &quot;hideme&quot;.

The cell containing Num1 has three rangenames: &quot;Rng_01&quot;, &quot;Rng_02&quot; & &quot;Rng_03&quot;
The cell containing Num2 has three rangenames: &quot;Rng_11&quot;, &quot;Rng_12&quot; & &quot;Rng_13&quot;
The cell containing Num3 has three rangenames: &quot;Rng_21&quot;, &quot;Rng_22&quot; & &quot;Rng_23&quot;

You could then autofilter on the range &quot;hideme&quot; but I don't know how to make the filter update each time you change a value in Num1, Num2 or Num3 so I suggest advanced filtering:

There is a named range &quot;criteria&quot;. This has two cells:
HIDE?
<>hide

Finally there is a macro
Sub applyfilter()
' applyfilter Macro
' uses rangenames &quot;hideme&quot; and &quot;criteria&quot;

Range(&quot;hideme&quot.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range(&quot;Criteria&quot, Unique:=False
End Sub

I guess this could be linked to a worksheet event to make it happen automatically but I've never actually done that and I guess ideally it would only be kicked off by one of the three cells changing… alternatively you could instead set up a keyboard shortcut to activate it or attach it to a button.
I have a sample spreadsheet which might be easier to follow and certainly appears to work - always assuming I have understood your requirement.

Gavona