Autohide row base on numerical value input in a cell

[4inthemorning]

I am not well verse in MSExcel 2007,

I am hoping someone can help me with my problem. I am creating an excel file that will help my work more accurate and more faster. I have posted an image also for reference.

Is is possible to auto hide a row base on numerical value input in a cell?

Example:

I have maximum of 7 years of service, Now if I input the numbers of years needed(e.g. 4 years) the rows exceeding 4 years should be automatically hide (e.g. year 5, year 6 and year 7).

Question.

Is there a function in excel that this situation can be done?

Thank you very much

 

[Peter_SSs]

Try adding the blue lines

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim yrs As Long
    
    If Not Intersect(Target, Range("B5")) Is Nothing Then
        Application.ScreenUpdating = False
        ActiveSheet.Unprotect Password:="123456"
        yrs = Range("B5").Value
        Rows("11:17").Hidden = False
        If yrs < 7 Then
            Rows(11).Offset(yrs).Resize(7 - yrs).Hidden = True
        End If
        ActiveSheet.Protect Password:="123456"
        Application.ScreenUpdating = True
    End If
End Sub

 

[4inthemorning]

Thank you so much sir peter. It works now i dont really know how to thank you. More knowledge to come sir.

 

[Peter_SSs]

Thank you so much sir peter. It works now i dont really know how to thank you.

Glad we got there in the end.

BTW, for future reference, you will get many more potential helpers if you post any sample data in small screen shots directly in your post. That way people do not have to go to another site to look and also they can copy your sample data to a worksheet to test. My signature block has three ways to do that. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[4inthemorning]

Sir Why is that when i closed the file the hide doesn't work again?

 

[Peter_SSs]

Sir Why is that when i closed the file the hide doesn't work again?

If you right click the sheet name tab and choose 'View Code', do you still see the code you entered there?

 

[4inthemorning]

If you right click the sheet name tab and choose 'View Code', do you still see the code you entered there?

Yes sir the code is still there.

 

[Peter_SSs]

It is possible that your code is not working because 'Events' (eg Worksheet_Change) are disabled. In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter
Now go back to your sheet and try again

 

[4inthemorning]

Error:

The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros.

 

[Peter_SSs]

Please refer to the online help or documentation of the host application to determine how to enable macros.

That was good advice. Did you try it?

Help is the blue circle with ? near the top right of the Excel window.
Search for 'enable macros' .
Then look for the section 'Enable or disable macros with the Trust Center'.
Then the part that relates to Excel.

 

[4inthemorning]

yes sir, thank you so much. More knowledge to come sir.