Hiding Rows based on user input

[coake]

Hey Folks,

By default I display 100 rows (rows 21 to 120)
In cell I19 I ask, how rows do you want to see? Lets say the answer is 10, but it could be later changed to 15, or 3.
Based on that, what would the syntax be to hide rows 31 to 100 and have the hidden rows hide/unhide based on the changing value in I19?

I have seen some pretty fancy examples to hide rows based on values in the actual row, or hiding a range of rows based on certain criteria, but nothing as simple as I'm looking to do.

Obviously, this does not work

Application.ScreenUpdating = False
On Error Resume Next
Dim NumRows As Integer

NumRows = "I19" + 2
Worksheets("Charter").Range("NumRows:$120").EntireRow.Hidden = True

End Sub


Regards,

 

[Rick Rothstein]

I carefully looked at spelling. From what little I know about error #9 it tells me the same thing - no such tab
But...
Worksheets("HSE Req").Range("$55:$55").EntireRow.Hidden = Not EPR.Value throws the subscript error;
Worksheets("HSE Req").Range("$55:$55").EntireRow.Hidden = EPR.Value actually works, just opposite. I have to uncheck the box for the row to be visible. When I check the box again, the row hides.

What kind of values can EPR.Value have in it?

 

[coake]

I believe, since it's an Active X checkbox, only True or False.
It looks like we post near the same time.

I'm just curious, does "Not" translate to "not equal to" in this context as an argument of Range?

 

[Rick Rothstein]

I believe, since it's an Active X checkbox, only True or False.
It looks like we post near the same time.

I'm just curious, does "Not" translate to "not equal to" in this context as an argument of Range?

Yes... and if your values are Boolean True and False, then Not would convert them to the opposite Boolean. The CheckBox is a tri-state control and although the documentation say the value is True or False for the normal two-state condition, I wonder if that is actually true. See if this code line works for you or not...

Worksheets("HSE_Req").Range("$55:$55").EntireRow.Hidden = (EPR.Value = 0)

 

[coake]

Same error.
It actually makes sense, you are right, the check box is a tri-state object, true|false|partial
Although in the Properties, "Enabled" can only be true or false, one does have the ability to set the TripleState to True or False giving you the option of partial (which is checked, but shaded in).
I've tried TripleState = True and False, same result.
When you set TripleState to true (enabling the 3rd option), the value of the checkbox has replaced True|False with nothing.

I've come to terms with 9 lines of code for each and it didn't take too long to complete

 

[Rickys03]

Assuming the value in cell I19 is changed manually (that is, the cell does not contain a formula), then this event code should do what you want automatically...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "I19" Then
    If Target.Value < 1 Or Target.Value > 100 Then
      MsgBox "Bad number of rows specified!", vbCritical
    Else
      Rows("21:120").Hidden = True
      Rows(21).Resize(Range("I19").Value).Hidden = False
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Hi, Could you please explain why the "ByVal Target As Range" is necessary as the function input in the parentheses?

This code works for me (based on your code above); however, I took Target.Address out and I do not call on the Range command. Why do I still need "ByVal Target As Range"? The code won't work for me if I don't include that. Thanks!

 

[Joe4]

"Worksheet_Change" is Event Procedure VBA code that runs automatically when some event (like the manual changing of a cell value occurs).

In order for it to run automatically, it has to be "recognized" as Event Procedure VBA code, and not other VBA code.
In order for this to happen, it HAS to follow these STRICT requirements:
1. It MUST be placed in the proper Module (a "ThisWorkbook" or appropriate "Sheet" module). It will not work placed in any other module.
2. It MUST be named a certain way, and CANNOT be altered at all.

So, if you got a sheet module, and add the "Change" event, it automatically populate the shell of the code for you like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

The first line (the line in red) CANNOT be altered in any way! You must leave it like this if you want your code to run automatically.

Note that "Target" is the range that was just changed that triggers this code to run. That is often important to know, because you may want/need to know which range was changed so you can run your code against the correct cells.

Here is a simple example that tells you what cell was updated (just so you can see what "Target" is, and does):

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Updated cell: " & Target.Address
End Sub