Display message during save operation
 

Dear Experts,

I would like to display a message during a save operation of a sheet (it is
quite a large file so it takes a while).
There is another thing related to this: When a user starts this spreadsheet
without enabling the macro's, the workbook is opened with a sheet showing a
text box with the message "Please reopen with macro function on". In the
BeforeSave event macro I activate this sheet with the text box (so it will be
shown on next start up). However this results in the display of the text box
"Please reopen with macro function on" during a save operation (which is
quite confusing).

Please help,

Jan Bart

Display message during save operation
 

Jan,

Try this BeforeSave alternative, it should take care of both issues

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Application.EnableEvents = False
MsgBox "This may take a while"
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
ThisWorkbook.SaveAs sFile
Worksheets("Startup").Activate
End If
Else
ThisWorkbook.Save
orksheets("Startup").Activate
End If
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in message
...
Dear Experts,

I would like to display a message during a save operation of a sheet (it
is
quite a large file so it takes a while).
There is another thing related to this: When a user starts this
spreadsheet
without enabling the macro's, the workbook is opened with a sheet showing
a
text box with the message "Please reopen with macro function on". In the
BeforeSave event macro I activate this sheet with the text box (so it will
be
shown on next start up). However this results in the display of the text
box
"Please reopen with macro function on" during a save operation (which is
quite confusing).

Please help,

Jan Bart

Display message during save operation
 

Dear Bob,

Thanks for your reply!
I do not realy understand this macro.
Probably I didn't explain myself all to well.

I will make it more explicit:
When opening my file it will display sheet1 with a textbox "You opened the
file without enabling the macro function".
When the macro is enabled this textbox is initially Hidden by the macro (you
will only see a short flash of it) and a menu Form is displayed.
Now before I save the file again the BeforeSave event makes the worksheet
hide all other sheets but Sheet1. Also I have to make the textbox visible
again before saving the file, because it should be there when opened with
macro's disabled.
What happens is that it shows during the actual writing of the file to the
hard disk.
Actually I would need something as a "DuringSave" event.....

I hope this makes thing more clear?!

Regards,
Jan Bart

"Bob Phillips" wrote:

Jan,

Try this BeforeSave alternative, it should take care of both issues

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Application.EnableEvents = False
MsgBox "This may take a while"
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile < False Then
ThisWorkbook.SaveAs sFile
Worksheets("Startup").Activate
End If
Else
ThisWorkbook.Save
orksheets("Startup").Activate
End If
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in message
...
Dear Experts,

I would like to display a message during a save operation of a sheet (it
is
quite a large file so it takes a while).
There is another thing related to this: When a user starts this
spreadsheet
without enabling the macro's, the workbook is opened with a sheet showing
a
text box with the message "Please reopen with macro function on". In the
BeforeSave event macro I activate this sheet with the text box (so it will
be
shown on next start up). However this results in the display of the text
box
"Please reopen with macro function on" during a save operation (which is
quite confusing).

Please help,

Jan Bart

Display message during save operation
 

Jan,

I understand what you are doing and what you want.

I think what I gave does that. Have you tried it? If so, in what way does it
not do what you want?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in message
...
Dear Bob,

Thanks for your reply!
I do not realy understand this macro.
Probably I didn't explain myself all to well.

I will make it more explicit:
When opening my file it will display sheet1 with a textbox "You opened the
file without enabling the macro function".
When the macro is enabled this textbox is initially Hidden by the macro
(you
will only see a short flash of it) and a menu Form is displayed.
Now before I save the file again the BeforeSave event makes the worksheet
hide all other sheets but Sheet1. Also I have to make the textbox visible
again before saving the file, because it should be there when opened with
macro's disabled.
What happens is that it shows during the actual writing of the file to the
hard disk.
Actually I would need something as a "DuringSave" event.....

I hope this makes thing more clear?!

Regards,
Jan Bart

"Bob Phillips" wrote:

Jan,

Try this BeforeSave alternative, it should take care of both issues

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Application.EnableEvents = False
MsgBox "This may take a while"
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If sFile < False Then
ThisWorkbook.SaveAs sFile
Worksheets("Startup").Activate
End If
Else
ThisWorkbook.Save
orksheets("Startup").Activate
End If
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in
message
...
Dear Experts,

I would like to display a message during a save operation of a sheet
(it
is
quite a large file so it takes a while).
There is another thing related to this: When a user starts this
spreadsheet
without enabling the macro's, the workbook is opened with a sheet
showing
a
text box with the message "Please reopen with macro function on". In
the
BeforeSave event macro I activate this sheet with the text box (so it
will
be
shown on next start up). However this results in the display of the
text
box
"Please reopen with macro function on" during a save operation (which
is
quite confusing).

Please help,

Jan Bart

Display message during save operation
 

Dera Bob,

What it does is that it gives the Msgbox (but not during the actual file
save writing proces, because you have to click on Ok to proceed). Then a file
Open menu is displayed?! (shouldn't it be GetSavaAsFilename??). I do not see
that the how the textbox is prevented from showing up in the Startup sheet???

Regards,

Jan Bart
"Bob Phillips" wrote:

Jan,

I understand what you are doing and what you want.

I think what I gave does that. Have you tried it? If so, in what way does it
not do what you want?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in message
...
Dear Bob,

Thanks for your reply!
I do not realy understand this macro.
Probably I didn't explain myself all to well.

I will make it more explicit:
When opening my file it will display sheet1 with a textbox "You opened the
file without enabling the macro function".
When the macro is enabled this textbox is initially Hidden by the macro
(you
will only see a short flash of it) and a menu Form is displayed.
Now before I save the file again the BeforeSave event makes the worksheet
hide all other sheets but Sheet1. Also I have to make the textbox visible
again before saving the file, because it should be there when opened with
macro's disabled.
What happens is that it shows during the actual writing of the file to the
hard disk.
Actually I would need something as a "DuringSave" event.....

I hope this makes thing more clear?!

Regards,
Jan Bart

"Bob Phillips" wrote:

Jan,

Try this BeforeSave alternative, it should take care of both issues

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Application.EnableEvents = False
MsgBox "This may take a while"
Cancel = True
If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If sFile < False Then
ThisWorkbook.SaveAs sFile
Worksheets("Startup").Activate
End If
Else
ThisWorkbook.Save
orksheets("Startup").Activate
End If
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelerate-nl" wrote in
message
...
Dear Experts,

I would like to display a message during a save operation of a sheet
(it
is
quite a large file so it takes a while).
There is another thing related to this: When a user starts this
spreadsheet
without enabling the macro's, the workbook is opened with a sheet
showing
a
text box with the message "Please reopen with macro function on". In
the
BeforeSave event macro I activate this sheet with the text box (so it
will
be
shown on next start up). However this results in the display of the
text
box
"Please reopen with macro function on" during a save operation (which
is
quite confusing).

Please help,

Jan Bart