Hide and unhide part of the screen in Excell 1

[blazblaz]

How can I give an instruction to hide and unhide some fields on screen?
I want the following: I want to write in field A1= 1, 2 or nothing . If in field
A1=1, unhide B2: E10
A1=2 hide B2:E10 and unhide F2:K10
Otherwise hide all B2:K10.

May I use an unused field (like A2) and write some instruction for above process?

 

[DaleWatson123321]

blazblaz,

Because you are referring to ranges instead of the entire columns, this suggestes you do NOT want to hide the ENTIRE columns.

If this is the case, then one potential option is the use of Conditional Formatting - where the result would be the coloring of the text "white" in your ranges - in response to entering a 1,2 or blank in cell A1.

This will work. I've just created an example file I can email you if this is an option.

However, as you can appreciate, if the user moves the cursor to one of the cells colored "white", the contents of the cell will still be visible in the formula-bar at the top of the screen.

Also, with condition "1", you mention hiding B2:E10. However, you didn't confirm what you want to have happen for F2:K10.

Can you provide more information - i.e. to confirm whether you can or cannot hide the ENTIRE columns, and whether Conditional Formatting might be an option.

If you want to email me, I can return the file I've created.

Regards, ...Dale Watson

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

 

[blazblaz]

Dale

To make it easier, in case A1=1 I want to unhide rows from A90-A130, and hide rows from A131 to A170. In case A1=2, I want to hide rows A90-A130, and unhide A131-A170. In case A1=any other, I want to hide all, A90-A170.
To hide and unhide these rows I don't want to use any mouse by user, want to define in sheet by programming it, and puting a protection.

What commands are hiding and unhiding rows (not by mouse, I know that)

 

[DaleWatson123321]

blazblaz,

Based on your last posting, here is a routine that will work...

This routine of course needs to be place in the Sheet Object (not in a Module). Also, assign the range name "num" to cell A1. And of course with the sheet protected, cell A1 needs to be unlocked.

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

I hope this is what you needed. Please advise as to how it fits.

Regards, ...Dale Watson

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

 

[blazblaz]

Hi Dale

I got your excell sheet as a sample how should work the hiding and unhiding rows. Am I doing something wrong, or something does not work, I don't know, but something is wrong.
In beginning you already have hiden rows from 131-to 170, beacaue num(A1)=1
If I enter num(A2)=2 I thought the rows 90-130 will be hiden and the 131-170 will be unhiden.This option does not work.
If there is no any number in num(A1), then all rows dissapear from 90-170, waht's OK.
But again, If I enter in A1=1 or 2, no any rows will be comeback.
Whats' wrong?

 

[DaleWatson123321]

blazblaz,

Regarding your comment... &quot;If I enter num(A2)=2 I thought the rows 90-130 will be hiden and the 131-170 will be unhiden. This option does not work.&quot;

For rows 90-130, I assigned the range name &quot;rng_1&quot; and for rows 131-170 I assigned the range name &quot;rng_2&quot;. From the above routine, the following is the section that you say is not working when you enter the value 2 in &quot;num&quot; (the range name assigned to cell A1)

ElseIf num = 2 Then
Range(&quot;rng_1&quot.Select
Selection.EntireRow.Hidden = True
Range(&quot;rng_2&quot.Select
Selection.EntireRow.Hidden = False

This routine worked for me, and as you can see from this routine, if num = 2, then &quot;rng_1&quot; will be selected and hidden (Hidden = True), and &quot;rng_2&quot; will be selected and un-hidden (Hidden = False).

Then, you say... If I enter num(A2)=2 I thought the rows 90-130 will be hiden and the 131-170 will be unhiden.This option does not work.

Again, this worked in the file I sent you, so I can't understand what would cause it NOT to work for you.

First, can you confirm that the SAME file I sent your works WITHOUT any modification ?

Secondly, perhaps you should consider emailing me the file you have modified. I'll then be able to see what you might have done to cause the transition of the code from my file to your file NOT to work.

If you can email me the file, it will certainly save us both some time. I hope this can an option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca