Excell - trouble with hiding-unhiding rows 1

[blazblaz]

I already asked this question, got few answers, but did not work exactly.
What I want is to hide-unhide some rows, depending from values in 3 different fields.
I want to change independantly these values.
For Example:
- 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 num1=1 unhide rng_01, hide rng_02, rng_03 )
(If num1=2 unhide rng_02, hide rng_01,rng_03 )
(If num1=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

My last VBA program was the following:
----------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
Case Range("num1&quot.Address
Call Num1Change
Case Range("num2&quot.Address
Call Num2Change
Case Range("num3&quot.Address
Call Num3Change
Case Else
Exit Sub
End Select
End Sub
---------------------------
Sub Num1Change()
'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 "123"
curcell = ActiveCell.Address
If num1 = 1 Then
Range("rng_01&quot.Select
Selection.EntireRow.Hidden = False
Range("rng_02&quot.Select
Selection.EntireRow.Hidden = True
ElseIf num1 = 2 Then
Range("rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range("rng_02&quot.Select
Selection.EntireRow.Hidden = False
Else
Range("rng_01&quot.Select
Selection.EntireRow.Hidden = True
Range("rng_02&quot.Select
Selection.EntireRow.Hidden = True
End If
Application.Goto Range(curcell), True
Sheets(cursht).Protect "123"
Application.ScreenUpdating = True
End Sub
----------
Sub Num2Change()
--
--
End Sub
----------
Sub Num3Change()
--
--
End Sub
----------

Thos program does not hide and unhide those rows I want.
Does anybody have some comment why?

 

[DaleWatson123321]

blaz,

If you'd like to email me your most recent file, I'll see if I can resolve the problem for you.

In the file, you should make sure you have assigned all the range names referred to in the VBA code.

You can reach me at home on the weekend.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[Gavona]

OOPs I have responded to the original thread. I am sure Dale's solution will be much, much better than mine ...

 

[Zathras]

I don't really like doing total re-write of code, but in this case I'm willing to make an exception. I would venture to say that the reason you are having problems is that you don't have a good naming scheme and that your code is not well structured. (Nothing personal.) Here is a complete solution: The first routine goes into the Sheet code and the rest go into a module. All you need to do is set the constants to the values you need:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
  Set rng = Intersect(Target, Union(Range("num1"), Range("num2"), Range("num3")))
  If Not rng Is Nothing Then ShowOrHide Target.Name.Name, Target.Value
  Set rng = Nothing
End Sub

Option Explicit
Const Range1A = "10:11"

 'Show for num1 = 1

Const Range1B = "12:13"

 'Show for num1 = 2

Const Range1C = "14:15"

 'Show for num1 = 3

Const Range2A = "20:21"

 'Show for num2 = 1

Const Range2B = "22:23"

 'Show for num2 = 2

Const Range2C = "24:25"

 'Show for num2 = 3

Const Range3A = "30:31"

 'Show for num3 = 1

Const Range3B = "32:33"

 'Show for num3 = 2

Const Range3C = "34:35"

 'Show for num3 = 3

Sub ShowOrHide(WhichRange As String, WhichCase As Integer)
  
  If WhichRange = "num1" Then
    Select Case WhichCase
      Case 1
        HideHideShow Range1B, Range1C, Range1A
      Case 2
        HideHideShow Range1C, Range1A, Range1B
      Case 3
        HideHideShow Range1A, Range1B, Range1C
      Case Else
        HideHideHide Range1A, Range1B, Range1C
    End Select
  End If
          
 If WhichRange = "num2" Then
    Select Case WhichCase
      Case 1
        HideHideShow Range2B, Range2C, Range2A
      Case 2
        HideHideShow Range2C, Range2A, Range2B
      Case 3
        HideHideShow Range2A, Range2B, Range2C
      Case Else
        HideHideHide Range2A, Range2B, Range2C
    End Select
  End If

  If WhichRange = "num3" Then
    Select Case WhichCase
      Case 1
        HideHideShow Range3B, Range3C, Range3A
      Case 2
        HideHideShow Range3C, Range3A, Range3B
      Case 3
        HideHideShow Range3A, Range3B, Range3C
      Case Else
        HideHideHide Range3A, Range3B, Range3C
    End Select
  End If
End Sub

Sub HideHideShow(Hide1 As String, Hide2 As String, Show As String)
  Range(Hide1).EntireRow.Hidden = True
  Range(Hide2).EntireRow.Hidden = True
  Range(Show).EntireRow.Hidden = False
End Sub

Sub HideHideHide(Hide1 As String, Hide2 As String, Hide3 As String)
  Range(Hide1).EntireRow.Hidden = True
  Range(Hide2).EntireRow.Hidden = True
  Range(Hide3).EntireRow.Hidden = True
End Sub

 

[Zathras]

I just realized that I left out the security aspects, but you should have no trouble putting that back in. Just bracket everything in the ShowOrHide sub.

 

[blazblaz]

Thanks everybody for suggestions,especially to Zathras, I will try to set it up my file this evening and will inform you how it worked. It's just excited to learn here from experts in Excell.

 

[blazblaz]

Zathras

Bingo. You're the master of the Excell! It works so beautifull, did not belive it. Based on the sample, I've even developed the program for more different ranges, so I can use it in the future for similar projects. Thank you again for your help. Also Dave and Gavona I appreciate your willingness to help me. It was worth to be persistent to solve this problem.

Les

 

[Zathras]

Les, if the user changes more than one cell at a time (e.g. copies the number "2" from one cell and pastes it into more than one of the target "numN" cells at the same time, the macro breaks. Here is a revised version that corrects that fault (sorry for any inconvenience):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim c As Range
  Set rng = Intersect(Target, Union(Range("num1"), Range("num2"), Range("num3")))
  If Not rng Is Nothing Then
    For Each c In rng
      ShowOrHide c.Name.Name, c.Value
    Next c
  End If
  Set rng = Nothing
End Sub

 

[blazblaz]

Thanks Zathras

I did not experience this trouble, becasue I always changing those nubres indipendently, no need to paste it, but I will add this change in the program. If you wil read this text, I will ask you something else (Or I will place another thread):
Same scenario as above need to hide unhide rows based on values in 3 fields. If I had 2 worksheets, changing the values in 1 worksheet, can I hide unhide automatically rows in second worksheet too? I tried to auotamitacally update values num1,num2, num3 in second worksheet, (like fields num11,num12,num13). Based on these updated values hiding and unhiding does not work. I have to inicitate change manually and press ENTER, in order to make hiding-unhiding. What's your opinion?

 

[Zathras]

As you found out, Worksheet_Change only fires for the cell that is keyed into. Related cells (by formula) are updated but no further events are fired for those cells.

The way out of this dilemma is to check the contents of each cell of interest on any change and if the value there is different from the previous value, then do something.

Something like this:

Option Explicit
Dim Previous22 As Integer

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Current22 As Integer

  Current22 = ThisWorkbook.Worksheets("Sheet2").Range("num22").Value
  If Current22 <> Previous22 Then
    MsgBox (&quot;Do stuff because num22 changed&quot;)
    Previous22 = Current22
  End If
End Sub

Hope this helps.

 

[blazblaz]

Thanks Zathras

I would never imagine I can fix the problem on this way, mainly becasue don't know what are the real possibilities in Excell. I am the old Fox-Dos database programmer, I can use a lot of ideas and techniques to solve the problems, but don't know all the tools in Excell. So, I've purchased the VBA programming book, and hopefully will get more knowledge in the future. Untill then, this is a good web to solve the problems with nice guys as you, Dave W, and the rest of them.
Thanks again, the idea gave you me here worth a star.

 

[blazblaz]

Zathras

I understood what's your intention with your last suggestion, but probably I did not specify correctly in the program. I had your previous Worksheet_Change and I tried to combine now with your last suggestion. I understand I have to the specify Option Explicit on module level: (May I use some field on sheet as specifying the variable?) Then I have to combine somehow these 2 requirements for worksheet change what I did on this way (I've just changed some variable names, and worksheet names, but the process is same). The value in Sheet4 num0=sheet1!num, so any change in sheet1!num will be udpated in Sheet4!num0.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Current22 As Integer
Dim c As Range
Dim rng As Range
Set rng = Intersect(Target, Union(Range("num0&quot, Range("num1&quot, Range("num2&quot, Range("num3&quot, Range("num4&quot))
Current22 = ThisWorkbook.Worksheets("sheet4&quot.Range("num0&quot.Value
If Current22 <> Previous22 Then
MsgBox (&quot;Do stuff because num22 changed&quot
For Each c In rng
ShowOrHide c.Name.Name, c.Value
Next c
Previous22 = Current22
End If
If Not rng Is Nothing Then
For Each c In rng
ShowOrHide c.Name.Name, c.Value
Next c
End If
Set rng = Nothing
End Sub

I was expecting, after I change the value NUM in SHEET1, opening the SHEET4 it will triger the ShowOrHide routine, and make the neccesarry hide-unhide rows, but it does not do. Whats' wrong?

 

[Zathras]

It was only an example, not the exact code you need for your situation.

There are several things you need to understand:

First, the &quot;Option Explicit&quot; should be the first line in any module. It tells VBA that all variables will be explicitly defined with a Dim statement. The primary benefit is to protect you from spelling mistakes. Without the &quot;Option Explicit&quot; variables can be created simply by using them. That means you could make the following sort of mistake (and if the statements are not close together it can be hard to find):

TimeOfDay = Now
SaveTimeOfDay = TimOfDay

(VBA would happily create the variable &quot;TimOfDay&quot; for you but it would never have any contents.)

Second, there is no code to modify sheet4 -- only the MsgBox to show you where code is needed.

Third, when referencing multiple sheets you have to be more specific. Without specifying which sheet you want things to happen to, the default is the active sheet.

Fourth, the expression &quot;For each c in rng&quot; only makes sense if rng is a range. Executing it in two places without changing the value of rng usually doesn't make any logical sense. You should set &quot;rng&quot; to a new value before trying to reference it (at least in this case).

Having said all that, here is the revised code to handle one additional range (&quot;num0&quot on one additional sheet (&quot;Sheet4&quot -- It assumes that there is a single cell on sheet4 with the name &quot;num0&quot; and the formula &quot;=num1&quot;

Sheet1 Code:

Option Explicit
Dim Previousnum0 As Integer

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Currentnum0 As Integer
Dim rng As Range
Dim c As Range
  
  Set rng = Intersect(Target, _
         Union(Range("num1"), Range("num2"), Range("num3")))
  
  If Not rng Is Nothing Then

    ' Do this sheet first...

    For Each c In rng
      ShowOrHide c.Name.Name, c.Value
    Next c

    ' Do other sheet(s)...

    Set rng = ThisWorkbook.Worksheets("Sheet4").Range("num0")
    Currentnum0 = rng.Value
    If Currentnum0 <> Previousnum0 Then
      ShowOrHide rng.Name.Name, rng.Value
      Previousnum0 = Currentnum0
    End If
  End If
  
  Set rng = Nothing
End Sub

Module code:

Option Explicit
Const Range1A = "10:11"

'Show for num1 = 1

Const Range1B = "12:13"

'Show for num1 = 2

Const Range1C = "14:15"

'Show for num1 = 3

Const Range2A = "20:21"

'Show for num2 = 1

Const Range2B = "22:23"

'Show for num2 = 2

Const Range2C = "24:25"

'Show for num2 = 3

Const Range3A = "30:31"

'Show for num3 = 1

Const Range3B = "32:33"

'Show for num3 = 2

Const Range3C = "34:35"

'Show for num3 = 3

Const Range0A = "10:11"

'Show for num0 = 1

Const Range0B = "12:13"

'Show for num0 = 2

Const Range0C = "14:15"

'Show for num0 = 3

Sub ShowOrHide(WhichRange As String, WhichCase As Integer)
Dim WhichSheet As Worksheet

  Set WhichSheet = Range(WhichRange).Worksheet
  
  If WhichRange = "num0" Then
    Select Case WhichCase
      Case 1
        HideHideShow WhichSheet, Range0B, Range0C, Range0A
      Case 2
        HideHideShow WhichSheet, Range0C, Range0A, Range0B
      Case 3
        HideHideShow WhichSheet, Range0A, Range0B, Range0C
      Case Else
        HideHideHide WhichSheet, Range0A, Range0B, Range0C
    End Select
  End If
  
  If WhichRange = "num1" Then
    Select Case WhichCase
      Case 1
        HideHideShow WhichSheet, Range1B, Range1C, Range1A
      Case 2
        HideHideShow WhichSheet, Range1C, Range1A, Range1B
      Case 3
        HideHideShow WhichSheet, Range1A, Range1B, Range1C
      Case Else
        HideHideHide WhichSheet, Range1A, Range1B, Range1C
    End Select
  End If
          
 If WhichRange = "num2" Then
    Select Case WhichCase
      Case 1
        HideHideShow WhichSheet, Range2B, Range2C, Range2A
      Case 2
        HideHideShow WhichSheet, Range2C, Range2A, Range2B
      Case 3
        HideHideShow WhichSheet, Range2A, Range2B, Range2C
      Case Else
        HideHideHide WhichSheet, Range2A, Range2B, Range2C
    End Select
  End If

  If WhichRange = "num3" Then
    Select Case WhichCase
      Case 1
        HideHideShow WhichSheet, Range3B, Range3C, Range3A
      Case 2
        HideHideShow WhichSheet, Range3C, Range3A, Range3B
      Case 3
        HideHideShow WhichSheet, Range3A, Range3B, Range3C
      Case Else
        HideHideHide WhichSheet, Range3A, Range3B, Range3C
    End Select
  End If
  
  Set WhichSheet = Nothing
End Sub

Sub HideHideShow(Sheet As Worksheet, _
       Hide1 As String, Hide2 As String, Show As String)
  With Sheet
    .Range(Hide1).EntireRow.Hidden = True
    .Range(Hide2).EntireRow.Hidden = True
    .Range(Show).EntireRow.Hidden = False
  End With
End Sub

Sub HideHideHide(Sheet As Worksheet, _
       Hide1 As String, Hide2 As String, Hide3 As String)
  With Sheet
    .Range(Hide1).EntireRow.Hidden = True
    .Range(Hide2).EntireRow.Hidden = True
    .Range(Hide3).EntireRow.Hidden = True
  End With
End Sub