Solved: Check Cells and Before Close

[shades]

I have code that checks the values in column F (more columns will be added). Right now this code provides what I need - identify those cells that have data not matching the criteria.
Sub Test()
Dim rngCell As Range
Dim rngCheck As Range
Dim lngLastA As Long
Dim strCell As String
Dim strErrors As String
strErrors = ""
lngLastA = Range("A65536").End(xlUp).Row
Set rngCheck = Range("F2:F" & lngLastA)
For Each rngCell In rngCheck
Select Case rngCell.Value
Case 12
GoTo CellOkay
Case 0
GoTo CellOkay
Case Else
strCell = rngCell.Offset(0, -1).Address
strErrors = strErrors & vbCrLf & strCell
End Select
CellOkay:
Next rngCell
If strErrors <> "" Then MsgBox "Check these cells:" _
& vbCrLf & strErrors
End Sub


Now, I have ~15 worksheets in the workbook which have to go through the same sequence of checks. And if there are strErrors then I want to prevent the user from closing the workbook.

My initial thought was to use this in a a Before-Close workbook event. But that is running into problems - I can't get it to do anything. My other thought is that I should have some code in each worksheet that links to this code, but force the person to correct the error before leaving the worksheet. Is it possible/advisable to leave this procedure in the Standard module, and then have a worksheet module call this? If so, how do I go about that?

Thanks for any help for an old codger.

[Zack Barresse]

Hey Rich,

Not sure what you've tried, but this is what I'd try ...

Standard Module:
Option Explicit

Dim strErrors As String

Sub Test(ws As Worksheet)
Dim rngCell As Range
Dim rngCheck As Range
Dim lngLastA As Long
Dim strCell As String
strErrors = ""
lngLastA = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rngCheck = wsRange("F2:F" & lngLastA)
For Each rngCell In rngCheck
Select Case rngCell.Value
Case 12, 0
GoTo CellOkay
Case Else
strCell = rngCell.Offset(0, -1).Address
strErrors = strErrors & strCell & vbCrLf
End Select
CellOkay:
Next rngCell
If strErrors <> "" Then MsgBox "Check these cells:" _
& vbCrLf & Trim(strErrors)
End Sub

ThisWorkbook Module:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Test (ws)
If strErrors <> vbNullString Then Cancel = True
Next ws
End Sub

[shades]

Thanks, Zack. The Workbook event was the problem. I assume that moving the strErrors outside the module then makes it available/usable in all modules.

However, as I tried this, an error appeared on the Before_Close Module, namely Variable "strErrors" is not defined. And if I comment out Option Explicit, then there is an error at Test (ws), "Object does not support..."

I'm learning more little things that make big differences.

Appreciate the help and insight.

Rich

[Zack Barresse]

Actually, I'd change it slightly, to a Function ..

Option Explicit

Dim strErrors As String

Function Test(ws As Worksheet) As String
Dim rngCell As Range
Dim rngCheck As Range
Dim lngLastA As Long
Dim strCell As String
strErrors = ""
lngLastA = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rngCheck = ws.Range("F2:F" & lngLastA)
For Each rngCell In rngCheck
Select Case rngCell.Value
Case 12, 0
GoTo CellOkay
Case Else
strCell = rngCell.Offset(0, -1).Address
strErrors = strErrors & strCell & vbCrLf
End Select
CellOkay:
Next rngCell
If strErrors <> "" Then Test = "Check these cells:" _
& vbCrLf & Trim(strErrors)
End Function

And the ThisWorkbook module ..
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Test(ws) <> vbNullString Then Cancel = True
If Cancel = True Then Exit For
Next ws
End Sub

Yes, moving it outside carries it as a public variable. The key is we need to pass the variable back and forth. That's the big reason I changed it to a Funciton, b/c it returns something that a Sub won't, not across those two modules.

Sorry for the confusion Rich.

Btw, the way the module is setup, it will exit after the first sheet that errors out. You can adapt from there, activate the first "offending" sheet, or have it continue, it's all up to you.

[malik641]

Hey Rich,

Not sure what you've tried, but this is what I'd try ...

Standard Module:
Option Explicit

Dim strErrors As String

Sub Test(ws As Worksheet)
Dim rngCell As Range
Dim rngCheck As Range
Dim lngLastA As Long
Dim strCell As String
strErrors = ""
lngLastA = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rngCheck = wsRange("F2:F" & lngLastA)
For Each rngCell In rngCheck
Select Case rngCell.Value
Case 12, 0
GoTo CellOkay
Case Else
strCell = rngCell.Offset(0, -1).Address
strErrors = strErrors & strCell & vbCrLf
End Select
CellOkay:
Next rngCell
If strErrors <> "" Then MsgBox "Check these cells:" _
& vbCrLf & Trim(strErrors)
End Sub

ThisWorkbook Module:
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Test (ws)
If strErrors <> vbNullString Then Cancel = True
Next ws
End Sub
Hey Zack,
Correct me if I'm wrong, but shouldn't it be:
Public strErrors As String
Instead of a Dim statement???....I got an error if it was Dim (because of the Option Explicit in the _BeforeClose event).

[shades]

I was just trying the Function approach. LOL

I probably will want to activate the offending worksheet. I will play with it for a while.

[shades]

Thanks, Joseph.

Even with all the adjustments I still get an error on the line: Test (ws) in the workbook close

[Zack Barresse]

Yeah, hence the switch to a function (re: the error).

If you want to activate the sheet, just use ws.Activate before Cancel = True.

[shades]

Well, even with the function I still get the error on "Test (ws)"

[Zack Barresse]

Well, even with the function I still get the error on "Test (ws)"
Did you have this line ..
If strErrors <> "" Then Test = "Check these cells:" _
& vbCrLf & Trim(strErrors)
?? Works for me.

[malik641]

I don't get an error where you're talking about shades...:dunno

And I think you should tell the user WHY it won't close....instead of the user just pressing the "X" and have nothing happening...I'm sure you'd get angry users :rofl3: I can see their frustration now...

So I made the "Dim strErrors As String" to "Public strErrors As String" and changed the _BeforeClose event:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Test(ws) <> vbNullString Then
MsgBox "Check these cells before closing: " & vbCrLf & ws.Name & ":" & vbCrLf & strErrors
Cancel = True
End If
If Cancel = True Then Exit For
Next ws
End Sub

Just a thought :thumb

[Zack Barresse]

I think my approach would be a little different ..

MsgBox strErrors & vbCrLf & vbCrLf & "On Sheet " & ws.Name
ws.Activate

[shades]

I don't get an error where you're talking about shades...http://vbaexpress.com/forum/images/smilies/102.gif

And I think you should tell the user WHY it won't close....instead of the user just pressing the "X" and have nothing happening...I'm sure you'd get angry users http://vbaexpress.com/forum/images/smilies/042.gif I can see their frustration now...

Yeah, that was my next step. I wanted to show them the cells that were causing errors, which I could and did do with the MsgBox in the Sub that I originally wrote.


So I made the "Dim strErrors As String" to "Public strErrors As String" and changed the _BeforeClose event:
I had made the change to Public as noted above, and still a problem.


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Test(ws) <> vbNullString Then
MsgBox "Check these cells before closing: " & vbCrLf & ws.Name & ":" & vbCrLf & strErrors
Cancel = True
End If
If Cancel = True Then Exit For
Next ws
End Sub

But, I copied yours in, and now it works perfectly!


Just a thought http://vbaexpress.com/forum/images/smilies/023.gif
The change in the BeforeClose module made the difference. So, more than "just a thought"; it was the thought that broke through!!

Thanks, Zack and Joseph. You have made my day!!!

[malik641]

I think my approach would be a little different ..

MsgBox strErrors & vbCrLf & vbCrLf & "On Sheet " & ws.Name
ws.Activate
Acually....we should REALLY reconsider it...because the first sheet it finds it will show the message and exit the sub...and if there is a different worksheet with the error, the macro won't get to it until the first one is fixed. And I KNOW people would be like: "What the?!?! I JUST FIXED IT!....C'mon!"

So how about this:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim msg As String
Dim Cncl As Boolean

msg = "Check these cells before closing:" & vbCrLf & vbCrLf

For Each ws In ThisWorkbook.Worksheets
If Test(ws) <> vbNullString Then
msg = msg & "On sheet " & ws.Name & ":" & vbCrLf & strErrors & vbCrLf
Cncl = True
End If
Next ws

If Cncl = True Then
MsgBox msg, vbCritical, "Errors..."
Cancel = True
End If
End Sub

eh? eh? :)

[Zack Barresse]

You are very welcome Rich. Glad I could be of some help. I apologize for misleading. It's an honor to be able to help one such as yourself out. It's humbling sir. :yes

[malik641]

Hey no problem Rich :thumb Makin' people's days is fun :yes and so is coding :content:

[shades]

You are very welcome Rich. Glad I could be of some help. I apologize for misleading. It's an honor to be able to help one such as yourself out. It's humbling sir. :yes

Thanks. But you, Zack and Joseph, are the honored ones. You help out us fledging codgers. I am continually amazed and humbled by the knowledge and helpfulness of the people here.

[malik641]

Thanks. But you, Zack and Joseph, are the honored ones. You help out us fledging codgers. I am continually amazed and humbled by the knowledge and helpfulness of the people here.:cloud9: