Workbook_BeforeSave Event

[Baapi]

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

 

[Bob Phillips]

Did you store it in the Thisworkbook code module, not a standard code
module.

To get at thta, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Baapi]

Yes, I've this code placed in sheet

 

[Jim Thomlinson]

The code has to be in ThisWorkbook, not in a sheet. To get to thisworkbook
right click the Excel icon beside file and then choose View Code...

 

[Bob Phillips]

Well, put it in the right place as advised.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)