Hi,
I've the following piece of code in a module. I save the workbook, But
the macro doesn't run. Can someone please comment?
-----------------------------
Option Explicit
Option Base 1
-----------------------------
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim CellstobeChecked(25), I As Long, J As Long
Dim Msg, Style, Title, Response
'Enable events
Application.EnableEvents = True
Worksheets("Sheet3").Range("D5").Value = "A"
'Initialize array with blanks
For I = 1 To 25
CellstobeChecked(I) = ""
Next I
'Load array with entered values, only if blank
J = 1
For I = 5 To 21
If I = 12 Then GoTo NEXTROW
If Worksheets("Sheet3").Cells(I, 4).Value = "" Or "-" Then
CellstobeChecked(J) = Worksheets("Sheet3").Cells(I, 3)
J = J + 1
End If
NEXTROW:
Next I
For I = 24 To 37
If Worksheets("Sheet3").Cells(I, 3).Value < "" Then
If Worksheets("Sheet3").Cells(I, 4).Value = "" Or "-" Then
CellstobeChecked(J) = Worksheets("Sheet3").Cells(23, 4)
If Worksheets("Sheet3").Cells(I, 5).Value = "" Or "-" Then
CellstobeChecked(J + 1) = Worksheets("Sheet3").Cells(23, 5)
If Worksheets("Sheet3").Cells(I, 7).Value = "" Or "-" Then
CellstobeChecked(J + 2) = Worksheets("Sheet3").Cells(23, 7)
If Worksheets("Sheet3").Cells(I, 8).Value = "" Or "-" Then
CellstobeChecked(J + 3) =
Worksheets("Sheet3").Cells(23, 8)
ElseIf UCase(Worksheets("Sheet3").Cells(I, 8).Value) =
"OTHERS" Then
If Worksheets("Sheet3").Cells(I, 10).Value = "" Or
Worksheets("Sheet3").Cells(I, 10).Value = "-" Then CellstobeChecked(J +
4) = "Bank - Region"
End If
End If
Next I
For I = 1 To 25
If CellstobeChecked(I) = "" Then Exit For
Next I
Style = vbOKOnly + vbInformation + vbDefaultButton1
Title = "Missing Information"
Msg = CellstobeChecked(1) & Chr(13) & _
CellstobeChecked(2) & Chr(13) & _
CellstobeChecked(3) & Chr(13) & _
CellstobeChecked(4) & Chr(13) & _
CellstobeChecked(5) & Chr(13) & _
CellstobeChecked(6) & Chr(13) & _
CellstobeChecked(7) & Chr(13) & _
CellstobeChecked(8) & Chr(13) & _
CellstobeChecked(9) & Chr(13) & _
CellstobeChecked(10) & Chr(13) & _
CellstobeChecked(11) & Chr(13) & _
CellstobeChecked(12) & Chr(13) & _
CellstobeChecked(13) & Chr(13) & _
CellstobeChecked(14) & Chr(13) & _
CellstobeChecked(15) & Chr(13) & _
CellstobeChecked(16) & Chr(13) & _
CellstobeChecked(17) & Chr(13) & _
CellstobeChecked(18) & Chr(13) & _
CellstobeChecked(19) & Chr(13) & _
CellstobeChecked(20) & Chr(13) & _
CellstobeChecked(21) & Chr(13) & _
CellstobeChecked(22) & Chr(13) & _
CellstobeChecked(23) & Chr(13) & _
CellstobeChecked(24) & Chr(13) & _
CellstobeChecked(25)
Response = MsgBox(Msg, Style, Title)
Cancel = False
End Sub


--
Baapi


------------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...o&userid=27333
View this thread: http://www.excelforum.com/showthread...hreadid=558236