Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Workbook_BeforeSave Event
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Workbook_BeforeSave Event

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Workbook_BeforeSave Event
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" wrote in
message ...

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Workbook_BeforeSave Event

Yes, I've this code placed in sheet

--
Baap

-----------------------------------------------------------------------
Baapi's Profile: http://www.excelforum.com/member.php...fo&userid=2733
View this thread: http://www.excelforum.com/showthread.php?threadid=55823

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default Workbook_BeforeSave Event
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...
--
HTH...

Jim Thomlinson


"Baapi" wrote:


Yes, I've this code placed in sheet3


--
Baapi


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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 380
Default Workbook_BeforeSave Event
Well, put it in the right place as advised.

--

HTH

Bob Phillips

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

"Baapi" wrote in
message ...

Yes, I've this code placed in sheet3


--
Baapi


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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
No screen updating in Workbook_BeforeSave event after ActiveWorkbook.Save bmeijers Excel Programming 0 November 29th 05 01:51 PM
Problem with Workbook_BeforeSave event [email protected] Excel Programming 0 August 25th 05 10:49 PM
Workbook_BeforeSave() bmm Excel Programming 3 August 16th 04 03:37 PM
Workbook_BeforeSave Bill Oertell Excel Programming 5 December 21st 03 07:33 PM
Workbook_BeforeSave() in xla Bent Kjeldsen Excel Programming 6 September 24th 03 01:49 PM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement