Hello All -

I'm having a peculiar problem with an Excel app that I inherited. The problem, in a nutshell, is that when I leave a blank in a "memo" field, I should get an error message slapping the user on the wrist to fill in ALL flds. But, in my loop, the computer sees the first memo fld as the only fld in my loop, so that if I have, say, ONE fld with text in it, fine - it thinks all's well, pass to the next page. But, if that 1st fld is BLANK it gives me an error. Fine.
BUT - if the 1st memo fld is filled in AND the OTHERS are blank, it SHOULD flag me with a warning message. Instead it goes on to the next page!!

I've put in msgboxes and looked at the way the code is falling through. My present configuration gives me ALMOST what I want. And that's not good enough.

I have a zipped version of the file - it is an add-in/template; not your usual spreadsheet. I can give anyone who wants to try the zip file and instructions for finding the problem area if they ask.

For those of you who want a quick glance at the code here it is:

Case gsSHEET_IVV_TAILORING 'Tailoring Justification page
ActiveWindow.FreezePanes = False

Dim rngfullCell As Range

For Each rngfullCell In Range("rngJustRows")

If rngfullCell.Value <> "" Then 'All filled in
'then advance to next screen
sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
Set wksSheet = wkbBook.Worksheets(sSheetTab)

'activate sheet and make visible
wksSheet.Activate
wksSheet.Visible = True
Exit Sub
End If

MsgBox "PLEASE ENTER ALL JUSTIFICATIONS", vbOKOnly
Exit Sub

If rngfullCell.Value = "" Then

MsgBox "PLEASE ENTER ALL JUSTIFICATIONS", vbOKOnly
Exit Sub
End If

If rngfullCell.Value = 0 Then 'No justs
'then advance to next screen
sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
Set wksSheet = wkbBook.Worksheets(sSheetTab)

'activate sheet and make visible
wksSheet.Activate
wksSheet.Visible = True
Exit Sub
End If
Next rngfullCell


Much appreciation to anyone who wants to help. I've asked the guy who wrote the program if he could also offer any insights. And I'm working on it myself this weekend.

Mike

Oh, one thing forgot to mention. It seems the function which hides the rows after the ones that show up as being "picked" by the true/false values may be futzing things up. All I know is, my code above seems to see the first memo filled in and assumes that that's ALL the memos. So it allows it to pass.
Sorry.

I tried data validation this weekend, which seems to help, but my code above is still quirky.

Hi , I don't know alot of VBA but I tought using
WorksheetFunction.CountA(Range("A1:A10"))
Could help finding empty cells in a range, just replace A1 and A10 with other fields.
CountA, counts all empty fields in the range so you could do something like this:
x = WorksheetFunction.CountA(Range("A1:A10"))
if x = 0 Then
'Something
Else
'Something
End if

I hope my reply is somehow usefull.

Thanks for your interest and reply. I've finally ( I believe ) found an algorithm that seems to work, after several weeks of frustration and trial/error.
For what it's worth, the code below acts on a previous page of check boxes and true/false logic elsewhere. But, it seems to consistenly flag me for an empty range/empty cell within the range.

Dim c As Range

On Error Resume Next
For Each c In [I13:I68]

If c.EntireRow.Hidden <> True Then 'flag for empty cell/range of cells
If IsEmpty(c) Then
MsgBox "YOU HAVE NOT COMPLETED A JUSTIFICATION. PLEASE ENTER A JUSTIFICATION", vbOKOnly, "IV&V Error Alert"
Exit Sub
End If
End If
Next

For Each c In [I13:I68]
If c.EntireRow.Hidden <> True Then 'successful entry of ALL cells
If Not IsEmpty(c) Then
'then advance to next screen
sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
Set wksSheet = wkbBook.Worksheets(sSheetTab)
'activate sheet and make visible
wksSheet.Activate
wksSheet.Visible = True
Exit Sub
End If
End If

Next

For Each c In [I13:I68]
If c.EntireRow.Hidden = True Then 'no justifications, so pass on to next page
'If Not IsEmpty(c) Then
'then advance to next screen
sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
Set wksSheet = wkbBook.Worksheets(sSheetTab)
'activate sheet and make visible
wksSheet.Activate
wksSheet.Visible = True
Exit Sub
' End If
End If
Next

Case gsSHEET_LABOR_HOURS ' the next page, if successful
End Select


For those of you who are curious, this code checks to see that ALL cells are entered before going on to the next page. If they are, advance; if NO justifications are checked from the previous page (a BIG distinction from NO spaces entered).

Note that it mentions hidden rows - that's because logic not mentioned here is hiding rows that weren't checked from the previous page's logic. So, in essence, it checks for ONLY non-hdden rows, something I found from a web page on hidden rows (sorry, I forgot where, just do a Google on hidden rows, you'll find it) and modified the little algorithm into my project.

Hope somebody can find something useful here!

Just a postscript: If anyone wants to see how the above code works, you can do what I did to prove the concept right - just take a simple spreadsheet and fill up the rows A1:A5 with data (whatever you want). Hide rows A6:A10. Now apply the code without all the stuff I have to get to the next page. Just put a msgbox somewhere to prove that you filled in the rows, or got some blank row error msgs.

Oh, yeah - it works even if you only have SOME blanks and SOME filled.