Problems with Workbook_BeforeSave (or Close)

  • Thread starter Thread starter AmyAnders
  • Start date Start date
A

AmyAnders

Can someone help me understand the proper way to use the
"Workbook_BeforeSave" or "Workbook_BeforeClose" functions?

I would like to be able to save the current time and date into
my worksheet. When I 'step through' the code using F8, it
works fine. However, when I close or save the file, I would
expect "Workbook_BeforeSave" or "Workbook_BeforeClose" to
do something, correct? I have tried both of these, and saved
the file manually; as well as closing Excel and saving the file when
prompted. When I reopen the file though, there are still is no
date or no time displayed in the cells.

Here's my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)
DateTime
End Sub

Sub DateTime()
Dim rng_date As Range
Dim rng_time As Range
Set rng_date = Worksheets("Sheet1").Range("E2")
Set rng_time = Worksheets("Sheet1").Range("B2")
rng_date.Value = Format(Date, "dd-mmm-yyyy")
rng_time.Value = Format(Time, "hh:mm")
End Sub

By the way, I am using Excel 2003 and WinXP.

Thanks in advance,

Am
 
Amy

Make sure the "Private Sub Workbook_BeforeSave........." code is placed in
the "ThisWorkBook" module, not a general module.

--
XL2002
Regards

William

(e-mail address removed)

| Can someone help me understand the proper way to use the
| "Workbook_BeforeSave" or "Workbook_BeforeClose" functions?
|
| I would like to be able to save the current time and date into
| my worksheet. When I 'step through' the code using F8, it
| works fine. However, when I close or save the file, I would
| expect "Workbook_BeforeSave" or "Workbook_BeforeClose" to
| do something, correct? I have tried both of these, and saved
| the file manually; as well as closing Excel and saving the file when
| prompted. When I reopen the file though, there are still is no
| date or no time displayed in the cells.
|
| Here's my code:
|
| Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
| Boolean)
| DateTime
| End Sub
|
| Sub DateTime()
| Dim rng_date As Range
| Dim rng_time As Range
| Set rng_date = Worksheets("Sheet1").Range("E2")
| Set rng_time = Worksheets("Sheet1").Range("B2")
| rng_date.Value = Format(Date, "dd-mmm-yyyy")
| rng_time.Value = Format(Time, "hh:mm")
| End Sub
|
| By the way, I am using Excel 2003 and WinXP.
|
| Thanks in advance,
|
| Amy
|
|
| ---
| Message posted
|
 
Yes, it already was placed there. "The Private Su
Workbook_BeforeSave"
is in "ThisWorkbook".

I've actually tried the code in all the areas I could think
of: Sheet1, ThisWorkbook, and Module1 (general module).
It does not seem to have any effect though no matter
which area I place it. The only time the DateTime will
work is if I use the Debug feature.

Amy
 
Try amending the DateTime sub to this...

Sub DateTime()
Dim rng_date As Range
Dim rng_time As Range
Set rng_date = Worksheets("Sheet1").Range("E2")
Set rng_time = Worksheets("Sheet1").Range("B2")
rng_date.NumberFormat = "dd-mmm-yyyy"
rng_time.NumberFormat = "hh:mm"
rng_date = Date
rng_time = Time
End Sub
--
XL2002
Regards

William

(e-mail address removed)

| Yes, it already was placed there. "The Private Sub
| Workbook_BeforeSave"
| is in "ThisWorkbook".
|
| I've actually tried the code in all the areas I could think
| of: Sheet1, ThisWorkbook, and Module1 (general module).
| It does not seem to have any effect though no matter
| which area I place it. The only time the DateTime will
| work is if I use the Debug feature.
|
| Amy
|
| William wrote:
| > *Amy
| >
| > Make sure the "Private Sub Workbook_BeforeSave........." code is
| > placed in
| > the "ThisWorkBook" module, not a general module.
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > message
| > | > | Can someone help me understand the proper way to use the
| > | "Workbook_BeforeSave" or "Workbook_BeforeClose" functions?
| > |
| > | I would like to be able to save the current time and date into
| > | my worksheet. When I 'step through' the code using F8, it
| > | works fine. However, when I close or save the file, I would
| > | expect "Workbook_BeforeSave" or "Workbook_BeforeClose" to
| > | do something, correct? I have tried both of these, and saved
| > | the file manually; as well as closing Excel and saving the file
| > when
| > | prompted. When I reopen the file though, there are still is no
| > | date or no time displayed in the cells.
| > |
| > | Here's my code:
| > |
| > | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
| > As
| > | Boolean)
| > | DateTime
| > | End Sub
| > |
| > | Sub DateTime()
| > | Dim rng_date As Range
| > | Dim rng_time As Range
| > | Set rng_date = Worksheets("Sheet1").Range("E2")
| > | Set rng_time = Worksheets("Sheet1").Range("B2")
| > | rng_date.Value = Format(Date, "dd-mmm-yyyy")
| > | rng_time.Value = Format(Time, "hh:mm")
| > | End Sub
| > |
| > | By the way, I am using Excel 2003 and WinXP.
| > |
| > | Thanks in advance,
| > |
| > | Amy
| > |
| > |
| > | ---
| > | Message posted
| > | *
|
|
| ---
| Message posted
|
 
Yes, it already was placed there. "The Private Su
Workbook_BeforeSave"
is in "ThisWorkbook".

I've actually tried the code in all the areas I could think
of: Sheet1, ThisWorkbook, and Module1 (general module).
It does not seem to have any effect though no matter
which area I place it. The only time the DateTime will
work is if I use the Debug feature.

Amy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workbook_BeforeSave 8
Workbook_BeforeSave not working. 2
save as cell A1 to to a specific drive 3
Private Sub Workbook_BeforeSave 2
Excel VBA - Workbook_BeforeSave 0
using two cell references to make up the save as file name 3
Workbook_BeforeSave behaviour 4
Save as crashes Excel - sometimes 7

Back
Top