Rows not hiding. VBA not showing MsgBox on value

[Derick63]

Greetings all.
I've been to frustration land and back with this issue and I just can't figure it out. It took me a while to get where the VBA code is now but now, I'm totally stuck.
The code is a Worksheet_Change event. Cell D3 (Target) controls visibility of rows 4 through 12 (inclusive). There is a MsgBox that supposed to display if D3>10 but it doesn't if D3 is 11 but will display if D3>=12. Add to that, if D3=1 or IsEmpty, Row 3 is the only Row supposed to be visible but Row 4 is still visible.
This is driving me nuts and it's something so simple I'm overlooking it.

This image shows how I set up a test worksheet:

Thanks for your generous attention, I really appreciate it.
Derick

Here's the event code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide/Show rows 4 through 12

    If Target.Address = "$D$3" Then
        Application.EnableEvents = False 
       
        Dim visibleRowCount As Long
        visibleRowCount = Range("D3").Value - 1 'If not -1, it will show an additional row ??????? I don't know why. I just tried -1 and it worked.
       
    ' Hide rows 4 to 12 to start.
        Rows("4:12").EntireRow.Hidden = True
       
        If visibleRowCount > 10 Then
            MsgBox "No more than 10 rows will be shown. Please enter a value from 1 to 10.", vbExclamation, "Incorrect Value"
            Range("D3").Value = 10
            Rows("4:12").EntireRow.Hidden = False
        ElseIf IsEmpty(visibleRowCount) Then
            Range("D3").Value = 1
            Rows("4:12").EntireRow.Hidden = True
        Else
            Rows("4:" & visibleRowCount + 3).EntireRow.Hidden = False '+3 because working with rows 4:12
        End If
       
        Application.EnableEvents = True
    End If

End Sub

 

[jolivanes]

I guess if we get wrong information, you get wrong results.
The way to go is to supply the right information. All of it.

 

[Akuini]

That work exactly as I wanted until D3=10. I got a Run-Time Error: 1004 "Application-defined or object-defined error on the line Rows(x + Target.Value).Resize(10 - rws(i)).Hidden = True.

@jolivanes
When D3=10 then this part:

.Resize(10 - rws(i))

will be read as:

.Resize(0)

that's what threw the error. So I think it should be:

For i = LBound(rws) To UBound(rws) - 1

 

[jolivanes]

Sorry, this is what was meant to be posted. My apologies.
Of course, Akuini's fix works also. It might even be better.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(3, 4).Address Then
Rows(3).Resize(10).Hidden = False
If Target >= 10 Then Exit Sub
Dim rws, x As Long, i As Long
rws = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
x = 3
Application.ScreenUpdating = False
Rows(3).Resize(10).Hidden = False
    For i = LBound(rws) To UBound(rws)
        If rws(i) = Cells(3, 4) Then Rows(x + Target.Value).Resize(10 - rws(i)).Hidden = True: Exit For
    Next i
End If
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

Forgot to say "Thanks" to Akuini.
Please consider it done.

 

[jolivanes]

Actually we don't need to use an array with row numbers.
You could test run this on a copy of your original workbook with more data rows as you indicated in Post #10.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(3, 4).Address Then
Dim avT As Long, avB As Long
avT = Application.InputBox("Top ""Always Visible"" Row", "Row Number", Type:=1)
avB = Application.InputBox("Bottom ""Always Visible"" Row", "Row Number", Type:=1)
Rows(avT).Resize(avB - avT).Hidden = False
If Target >= avB - avT Then Exit Sub
Rows(avT + Target.Value).Resize(avB - avT - Target.Value).Hidden = True
End If
End Sub

Substitute the InputBox lines to

avT = 3
avB = 13    '<---- or whatever row number

If you want to

In this code snippet in Post # 13, delete the middle line.

Application.ScreenUpdating = False
Rows(3).Resize(10).Hidden = False    '<----- delete this line
    For i = LBound(rws) To UBound(rws)

If or when you reply to my post, don't quote. Refer to Post numbers. I generally know and even sometimes remember what I wrote/did.

 

[Micron]

This seems to work for me as well, based on post 2. Slight modification, but similar to what I first posted. The other code I was referring to that ran was my own.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim visibleRowCount As Long

On Error GoTo errHandler
If Not Target.Address = "$D$3" Then Exit Sub
If visibleRowCount > 10 Then 'if true, no sense in doing anything else; e.g. disabling events
     MsgBox "No more than 10 rows will be shown. Please enter a value from 1 to 10.", vbExclamation, "Incorrect Value"
     Exit Sub
End If

visibleRowCount = Target
If Target > 0 And Target < 10 Then
     Application.EnableEvents = False
     Rows("3:" & Target + 2).Hidden = False
     Rows(Target + 3 & ":" & "12").Hidden = True
Else
     Rows("3:12").Hidden = False
End If

exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Derick63]

Ref post #15
Jolivanes, that works perfectly. I'm impressed by the length of the code and it does exactly as I needed it. I used the row numbers as your offered option. As I mentioned before the 10 rows was the test and having the option to change these variables is exactly what I was looking at.
Thanks for the lessons learned. I'm still learning and I humbly apologize for not mentioning about the intent of using the code for 200 rows. I'll mark this thread as done.

 

[Derick63]

@Micron. I want to thank you for your time and effort. I'm grateful and sincerely appreciate the time you took to help me finding a solution. jolivane offered the solution I was looking for in Post #15. The code was concise and exactly what I was looking for.
Thanks again.