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

 

[Micron]

I would say the 11 vs 12 issue is because you've already subtracted 1 from the variable. You're saying the message box should raise if D3>10 but in reality, it's if the variable value is greater than 10. If 10 is in D3 then 10-1 is not greater than 10. 11-1 is not greater than 10 either. Also, IsEmpty is best used with variant variable types. Your use of it will probably always result in False being returned because a) D3 contains a value (e.g. 10), or b) D3 contains nothing but the variable wasn't set to Empty so it's not Empty, or c) it was set to Empty if D3 contains nothing, in which case for a numeric variable it becomes 0, so not really Empty.
Also, IMO turning off application settings without using an error handler will likely result in them remaining off.

It's not really clear to me what you are trying to accomplish.

Cell D3 (Target) controls visibility of rows 4 through 12 (inclusive)

I see what happens if I enter 15 in D3. What should happen if I enter 6?

EDIT - Just notice that your message asks user to enter a different value, but your code simply continues. Should it not exit instead?

 

[jolivanes]

Could you just tell us what needs to happen when the target value changes.
If it is x then hide rows y to z incl
If it is xx then hide only row ??
If it is xxx then do whatever.
If it is xxxx the do another thing

 

[Derick63]

Could you just tell us what needs to happen when the target value changes.
If it is x then hide rows y to z incl
If it is xx then hide only row ??
If it is xxx then do whatever.
If it is xxxx the do another thing

Hello jolivanes. Thanks for your reply.

Here's the breakdown of what rows would be shown and hidden for each value of cell D3:

• If D3 = 1:
  • Rows 3 will be shown. (row 3 is always visible)
  • Rows 4 through 12 will be hidden.
• If D3 = 2:
  • Rows 3 and 4 will be shown.
  • Rows 5 through 12 will be hidden.
• If D3 = 3:
  • Rows 3 through 5 will be shown.
  • Rows 6 through 12 will be hidden.
• If D3 = 4:
  • Rows 3 through 6 will be shown.
  • Rows 7 through 12 will be hidden.
• If D3 = 5:
  • Rows 3 through 7 will be shown.
  • Rows 8 through 12 will be hidden.
• If D3 = 6:
  • Rows 3 through 8 will be shown.
  • Rows 9 through 12 will be hidden.
• If D3 = 7:
  • Rows 3 through 9 will be shown.
  • Rows 10 through 12 will be hidden.
• If D3 = 8:
  • Rows 3 through 10 will be shown.
  • Rows 11 and 12 will be hidden.
• If D3 = 9:
  • Rows 3 through 11 will be shown.
  • Row 12 will be hidden.
• If D3 = 10:
  • Rows 3 through 12 will be shown. (all rows visible)

 

[Micron]

In testing, even though I turned off events, other code still ran and messed things up, so not used here, although the error handler shows what I was referring to before. Something like this perhaps?

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

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

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

End Sub

I might have overlooked something because I'm rushing. Duty (the Mrs.) calls.

 

[Derick63]

Hello Micron. I'm not a noob but not very well versed in VBA so I'll try my best to answer with my limited knowledge.

I would say the 11 vs 12 issue is because you've already subtracted 1 from the variable.

This is the part that had me pulling my hair. If I removed the -1 from the variable, an extra row would be visible. It was sheer luck and a "What if I did this" moment. It worked but apparently caused a follow-up issue.

I would say the 11 vs 12 issue is because you've already subtracted 1 from the variable. You're saying the message box should raise if D3>10 but in reality, it's if the variable value is greater than 10. If 10 is in D3 then 10-1 is not greater than 10. 11-1 is not greater than 10 either.

This makes sense now but when D3=10, rows 4 through 12 are visible (10 rows). It just works but when D3=1 or blank or empty, rows 3 and 4 is visible when row 3 is the only row need to be visible when D3 value is 1 or just blank.

Also, IsEmpty is best used with variant variable types. Your use of it will probably always result in False being returned because a) D3 contains a value (e.g. 10), or b) D3 contains nothing but the variable wasn't set to Empty so it's not Empty, or c) it was set to Empty if D3 contains nothing, in which case for a numeric variable it becomes 0, so not really Empty.

Changing Dim visibleRowCount from Long to Variant didn't solve it when I hit the delete key followed by the Enter key. Not sure what else to do in this case. Thanks for the eye opener on using a Variant instead of Long though

It's not really clear to me what you are trying to accomplish.

Okay, I know I should have given the back story on this, but it would have been too long of a post. Here goes, each row, 3 through 12 totaling 10 rows is where the user would enter an employees name and enter their payroll information to be posted on the relevant month sheet. There are actually 200 rows I need to deal with, at the bottom of the 200 rows there are 4 buttons with macros assigned to them, one main button is to post all the data to one specific sheets. The 2nd, 3rd and 4th buttons post partial data to other sheets like the Pay slip sheet, a government contribution work sheet and the employee's data sheet.
If one company using this app only has xx employees, the user would enter xx in D3 then rows xx+1 through row 200 will be hidden and the four buttons will be right under the last row visible.
I wrote this for 10 rows for ease of getting it right then I will expand it to handle 200 rows after it works as intended.

I see what happens if I enter 15 in D3. What should happen if I enter 6?

• If D3 = 6:
  • Rows 3 through 8 will be shown. (6 visible rows)
  • Rows 9 through 12 will be hidden. (4 hidden rows)
    

Just notice that your message asks user to enter a different value, but your code simply continues. Should it not exit instead?

I thought it did end by continuing to End Sub. Thanks for the heads up on that. I added Exit Sub after the user click on Okay.

 

[Derick63]

In testing, even though I turned off events, other code still ran and messed things up, so not used here, although the error handler shows what I was referring to before. Something like this perhaps?

I tried your code but nothing happens when cell D3 changes. I like where you're going with it though, it makes sense. I'm not sure what other code ran for you but in my test sheet it's the only code in the project.

I might have overlooked something because I'm rushing. Duty (the Mrs.) calls.

Priorities! (the Mrs.)

Thanks for your assistance.

 

[Derick63]

In testing, even though I turned off events, other code still ran and messed things up, so not used here.

I failed to mention the reason I use Application.EnableEvents = False/True; In the actual workbook app, Sheet27 is where I have that Event issue. There are five intense Worksheet_Change events on Sheet27. Also, the workbook is set to Manual Calculation because of speed so I use ActiveSheet.Calculate as needed to get values recognized by formulas within the sheet. Currently the File is close to 43Mb and seriously considering converting to .xlsb Binary format but that's for another day and topic.

 

[jolivanes]

Does this come close?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(3, 4).Address Then
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

 

[Derick63]

Hello jolivanes.

Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

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.

Now, just to let you know, there are actually 200 rows I need to deal with and I wrote this for 10 rows for ease of getting it right then I will expand it to handle 200 rows after it works as intended.