VBA: MsgBox Warning if row is hidden

[ThomasA1993]

Hi all

I'm new here and kinda of new to VBA, but trying to learn.

I have a bit of a tough one (for me at least).

I am trying to write a code that can do the following.

Whenever a row is hidden manually if the row contains a value greater or smaller than zero, and not text string in columns H, J and L - i need msgbox to pop up and say "Warning - you are hiding a data".

Is this possible - and can it work if multiple rows are hidden together?

I would have posted my own attempt - but i am not even close to one yet.

 

[Joe4]

Welcome to the Board!

Is there always data visible in rows AFTER the hidden data (meaning, are the hidden data rows ever at the end, or are they always somewhere in the middle)?
What column can we use to determine where the data ends (meaning, for any row with data, is there some column that is always guaranteed to have something in it)?

Whenever a row is hidden manually if the row contains a value greater or smaller than zero, and not text string in columns H, J and L

So, do you want the MsgBox only when there are numeric values in ALL three columns, or just any one of the three columns?

 

[ThomasA1993]

Hi Joe4

there will always be some data available in some rows. from 1 to 1000 its is probably half that gets hidden. it can however occur to be the last row of the data set that needs to be hidden. I should probably explain a little better. There are blank rows in between some of the rows, and some rows only contain string. If these are hidden the MsgBox needs to stay quiet. Only if a row has the numeric value of 0 in columns H, J and L should it be hidden manually. So it is sort of a fail safe if a row containing data in either of the 3 columns or all them, and that row is hidden, then i want the MsgBox showing the user he made a mistake. All 3 columns are always guaranteed to have data in them.

If there is a numeric value different from 0 in either of the three columns or all of them at the same time, and that row is hidden. Then the msgbox should appear.

 

[Joe4]

Try this code:

Sub MyCheck()

    Dim r As Long
    Dim str As String
    
    For r = 1 To 1000
'       Check to see if row is hidden
        If Rows(r).EntireRow.Hidden Then
'           Check to see if a non-zero numeric value in H, J, or L
            If (IsNumeric(Cells(r, "H")) And Cells(r, "H") <> 0) Or _
                (IsNumeric(Cells(r, "J")) And Cells(r, "J") <> 0) Or _
                (IsNumeric(Cells(r, "L")) And Cells(r, "L") <> 0) Then
'               Write row number to string
                str = str & r & ","
            End If
        End If
    Next r
    
'   Return message box of row that should not be hidden
    If Len(str) > 0 Then
        MsgBox "The following rows contain values and should not be hidden:" & vbCrLf & _
            Left(str, Len(str) - 1), vbOKOnly, "ALERT!!!"
    Else
        MsgBox "Data looks good!", vbOKOnly, "DATA CONFIRMED!"
    End If
    
End Sub

 

[ThomasA1993]

Hi Joe

It works great, however i do get a debug, when i try to add it to an existing data sheet?

 

[jasonb75]

Another approach to the challenge,

Option Explicit
Private Sub Worksheet_Calculate()
Dim c As Range, rng As Range, rlist As String
For Each c In Intersect(Selection.EntireRow, Range("H:H,J:J,L:L"))
    If c.EntireRow.Hidden And IsNumeric(c.Value) And c.Value <> 0 Then
        If rng Is Nothing Then
            Set rng = c
        Else
            Set rng = Application.Union(rng, c)
        End If
    End If
Next
    If Not rng Is Nothing Then MsgBox "You are hiding data in cells" & vbCrLf & rng.Address(0, 0), vbCritical
End Sub

 

[Joe4]

It works great, however i do get a debug, when i try to add it to an existing data sheet?

What do you mean? Please explain.
What is the exact error message you are getting, and what line of code does it highlight when you hit debug?

 

[ThomasA1993]

I tried again and started by unhiding all rows, and then i had no problem - but the debug occured on the following lines.

I dont remember the debug code i think however it was 13. but i am not sure

HI Jason
thank u for the code - i am however this much of a newbie that i am not sure on how to implement private subs yet, and also the code posted by Joe is actually doing the job, but thanks for the help

If (IsNumeric(Cells(r, "H")) And Cells(r, "H") <> 0) Or _
(IsNumeric(Cells(r, "J")) And Cells(r, "J") <> 0) Or _
(IsNumeric(Cells(r, "L")) And Cells(r, "L") <> 0) Then

 

[jasonb75]

i am not sure on how to implement private subs yet,

The implementation of a private sub is the same as any other, it just means that it can't be accessed by another sub in a different module.

The difference with the approach that I suggested is that it uses an 'Event', which means it runs automaticaly when something happens. Hiding or unhiding rows is a volatile action, which means that it causes formulas to recalculate, I've used code that will detect this recalculation and automatically check any rows being hidden without user intervention.

To use this code, the only thing that you need to do is drop it into the module that is specific to the worksheet where you want to check the hidden rows. You can either do this from the project explorer in the vba editor or by right clicking the sheet tab on the excel worksheet, then clicking 'View Code' in the context menu.

If you try it, you will be surprised how easy it is

 

[ThomasA1993]

Tried it Jasonb75

It works great when hiding datat, and defenitely see what u mean, however i use 2 sheets, where i plug data in to 1 and transfer i 2 another, and whenever i plug in data and error occurs. the error doesnt stop the macro from working it is just annoying. i use simple formulas to transfer from one sheet to another.