Excell - Hiding & Unhiding rows 3

[blazblaz]

Can somebody help me in this problem. I want to hide and unhide some rows in Excell depending from the values entered in cell A1.
If the value in A1=1, unhide rows from 90-130, and hide rows from 131-170
If the value in A1=2, hide rows from 90-130 and unhide rows from 131-170
In any other cases A1, hide rows from 90-170.
Dale Watson the master of Excell programming wrote me this program:

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 ActiveCell.Address <> Range(&quot;num&quot.Address Then Exit Sub
cursht = ActiveSheet.Name
Sheets(cursht).Unprotect &quot;123&quot;
curcell = ActiveCell.Address
Application.ScreenUpdating = False
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
End If
Application.Goto Range(curcell), True
Sheets(cursht).Protect &quot;123&quot;
Application.ScreenUpdating = True
End Sub

Dale says it' working on his computer, but on mine just does not work. If I delete any number in cell A1, rows 90-170 are hidden, but other options are not working. Entering A1=1, it does not hide rows 131-170, and rows 90-130 are not unhidden.
Can somebody try out on his computer the check does this program work for given condition? I believe to Dale that it's working on his computer, but why does not work in mine? Did I set something wrong in my Excell?
Does anybody have any idea how can I solve this hide-unhide condition? It looks so easy task, but I just can't solve it.

 

[Zathras]

Did you create the required range names? (Insert/Name/Define... from the menu)

You need three ranges (The sheet name may vary according to your particular setup):

   num    =Sheet1!$A$1
   rng_1  =Sheet1!$A$90:$A$130
   rng_2  =Sheet1!$A$131:$A$170

After setting up those three ranges, it worked ok for me. Entering a 1, hides 131 thru 170 and entering anything else (or nothing) hides 90 thru 130. The macro leaves the worksheet protected, so if you have to change anything manually, you will need the password &quot;123&quot; to unprotect it first. (Tools/Protection/Unprotect Sheet... from the menu.)

 

[blazblaz]

Zathras

It must be something wrong, it does not work with my Excell. Dale told me that
on his computer works just fine, and you're saying same . I do have defined range rng_1, rng_2, and num, Dale sent me original his file from his computer, which does not work here. This is now mistery to me, why does not work. I've opened his file, without modification, and did just 2 steps. First, with opening I am already in cell A1. (he already entered number 1 in it). Then:
1) I type number 2
2) Press Enter.
It does not happen anything, same as at beginning, 90-130 unhidden, 131-170 hiden.
The sheet is protected in beginning, I just can not imagine what could I do wrong in these 2 steps, now way to mess up anything.
If I delete any number in cell A1, (empty), the everything is hidden, from 90-170.
If i try to enter again 1 or 2, it happens nothing, still hidden 90-170, that's the end of the story.
I have a headache from this, any special Excell parameter set-up need for this?
Would appreciate any your help.

 

[Zathras]

I'm going to have to play around with this some more. It still works ok for me.

I mis-spoke earlier about the behavior, though. Typing 1 un-hides rows 90-130 and hides rows 131-170, typing 2 hides rows 90-130 and un-hides rows 131-170, typing anything else hides rows 90-170.

Are you absolutely sure you are typing 1 or 2? Just the number? Only one key-stroke, followed by the Enter key?

Anything like '1 (Quote one) or A1=1 (four characters) will cause all rows 90-170 to be hidden.

 

[blazblaz]

Zathras

I am absolutely sure that I am typing just number 1 and 2. No character like '2, I understand that is not a number. I can send you a file what I am working with.
Is it any special Excell set-up, which is required for this? What else can go wrong
I am doing? Hope maybe tomorrow you can give me some suggestions.

zalbzalb@hotmail.com

 

[DaleWatson123321]

blazblaz,

It was a good idea to involve others.

It's certainly an interesting mystery.

Perhaps it would help those like Zathras to know that (if I recall correctly) your Excel is set up with the Dutch language.

I'm wondering if anyone in Tek-Tips has encountered a similar problem - relating to configuring of Excel for a different language.

I hope this helps.

Regards, ...Dale Watson

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

 

[blazblaz]

Thanks Dale for your involment again.

You told me last time that I have some dutch version of Excell, that I was using at home to test the program. I have a official version of Excell 97 in my company, and the program behaves on same way as in home computer Office-2000. I will try tomorrow this your file again in another computers in my company, to see is there anything different. Will inform you about the result soon.
Thank you again for you honest help in the past.

 

[Zathras]

The way the macro is written, you MUST press enter to change the value to 1 or 2.

If you type 1 or 2 and then press TAB or click some other cell with the mouse, the macro will not do anything, because the ActiveCell address is not the same as the address where you typed.

You can revise this line of code:

  If ActiveCell.Address <> Range(&quot;num&quot;).Address Then Exit Sub

to be

  If

num

.Address <> Range(&quot;num&quot;).Address Then Exit Sub

That should fix the problem.

 

[blazblaz]

Zathras

Thank you for you suggestion, It's fantastic, finally it's working.
I still don't get it how come it was working with your
Excell with a previous version of a program, and not with mine?
Also thanks Dale for your effort to help me, I realy appreciate time you spent to solve this problem.

 

[Zathras]

This is only a guess, but you probably have the &quot;Move selection after Enter&quot; option checked. (Tools/Options... Edit tab)

I usually leave that option un-checked, and apparently Dale does, too.

Glad to be able to help, and thanks for the star.

 

[xlbo]

Could also be that in Tools>Options>Transition
Transition Navigation Keys is ticked
This being the case, if you enter 1 in A1, it actually enters '1 - have had this problem before as the value and text properties of the range REALLY don't like this particular option - the ' kinda IS there but also kinda ISN'T there - depending on how you are accessing the cell Rgds
Geoff

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