Supressing "Do you want to save the changes you made to <file?" message
 

Hi there

I have an Excel system that most of my users are only allowed to open
and view read-only.

I have volatile formulaes that mean that even when the user just views
and changes nothing when they close the file Excel asks them:

"Do you want to save the changes you made to <file?"

The problem is that this confuses the user as they now think they have
made a change.

I was hoping to suppress this message and replace with my own more
helpful message - but I got stuck at the first part:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Workbook_BeforeClose"
Application.DisplayAlerts = False
End Sub

Seems disabling alerts is not good enough. Does anyone know how I can
supress the above message?

Thanks for any ideas.
Chrisso

Supressing "Do you want to save the changes you made to <file?" message
 

Hi Chrisso,

Try:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
'<<=============


---
Regards,
Norman


"Chrisso" wrote in message
oups.com...
Hi there

I have an Excel system that most of my users are only allowed to open
and view read-only.

I have volatile formulaes that mean that even when the user just views
and changes nothing when they close the file Excel asks them:

"Do you want to save the changes you made to <file?"

The problem is that this confuses the user as they now think they have
made a change.

I was hoping to suppress this message and replace with my own more
helpful message - but I got stuck at the first part:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Workbook_BeforeClose"
Application.DisplayAlerts = False
End Sub

Seems disabling alerts is not good enough. Does anyone know how I can
supress the above message?

Thanks for any ideas.
Chrisso

Supressing "Do you want to save the changes you made to <file?" message
 

But what is a change is made AND it needs to be Saved?
Wont this code (at present) close the WB, without saving such changes?

TIA,
Jim

"Norman Jones" wrote in message
:

Hi Chrisso,

Try:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
'<<=============


---
Regards,
Norman


"Chrisso" wrote in message
oups.com...
Hi there

I have an Excel system that most of my users are only allowed to open
and view read-only.

I have volatile formulaes that mean that even when the user just views
and changes nothing when they close the file Excel asks them:

"Do you want to save the changes you made to <file?"

The problem is that this confuses the user as they now think they have
made a change.

I was hoping to suppress this message and replace with my own more
helpful message - but I got stuck at the first part:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Workbook_BeforeClose"
Application.DisplayAlerts = False
End Sub

Seems disabling alerts is not good enough. Does anyone know how I can
supress the above message?

Thanks for any ideas.
Chrisso

Supressing "Do you want to save the changes you made to <file?" message
 

If it is read-only, they can't save it anyway.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JMay" wrote in message
...
But what is a change is made AND it needs to be Saved?
Wont this code (at present) close the WB, without saving such changes?

TIA,
Jim

"Norman Jones" wrote in message
:

Hi Chrisso,

Try:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
'<<=============


---
Regards,
Norman


"Chrisso" wrote in message
oups.com...
Hi there

I have an Excel system that most of my users are only allowed to open
and view read-only.

I have volatile formulaes that mean that even when the user just views
and changes nothing when they close the file Excel asks them:

"Do you want to save the changes you made to <file?"

The problem is that this confuses the user as they now think they have
made a change.

I was hoping to suppress this message and replace with my own more
helpful message - but I got stuck at the first part:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Workbook_BeforeClose"
Application.DisplayAlerts = False
End Sub

Seems disabling alerts is not good enough. Does anyone know how I can
supress the above message?

Thanks for any ideas.
Chrisso

Supressing "Do you want to save the changes you made to <file?" message
 

Thanks Bob, But OP says (that):

I have an Excel system that most of my users are only allowed to open

and view read-only



Just thought I'd mention.



"Bob Phillips" wrote in message
:



If it is read-only, they can't save it anyway.



--

HTH



Bob



(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JMay" wrote in message

...

But what is a change is made AND it needs to be Saved?

Wont this code (at present) close the WB, without saving such changes?



TIA,

Jim



"Norman Jones" wrote in message

:



Hi Chrisso,



Try:



'=============

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Me.Saved = True

End Sub

'<<=============





---

Regards,

Norman





"Chrisso" wrote in message

oups.com...

Hi there



I have an Excel system that most of my users are only allowed to open

and view read-only.



I have volatile formulaes that mean that even when the user just views

and changes nothing when they close the file Excel asks them:



"Do you want to save the changes you made to <file?"



The problem is that this confuses the user as they now think they have

made a change.



I was hoping to suppress this message and replace with my own more

helpful message - but I got stuck at the first part:



Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Workbook_BeforeClose"

Application.DisplayAlerts = False

End Sub



Seems disabling alerts is not good enough. Does anyone know how I can

supress the above message?



Thanks for any ideas.

Chrisso

Supressing "Do you want to save the changes you made to <file?" message
 

Hi Jim,

I missed the word "most"! Well spotted!

That being the case, try something like:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Arr As Variant
Dim Res As Variant

Arr = VBA.Array("Chrissol", "Jim", "Bob", "Norman")

Res = Application.Match(Environ("UserName"), Arr, 0)
Me.Saved = IsError(Res)
End Sub
'<<=============

The array values correspond to the users who do have
write permission.


---
Regards,
Norman




"JMay" wrote in message
...
Thanks Bob, But OP says (that):

I have an Excel system that most of my users are only allowed to open

and view read-only



Just thought I'd mention.



"Bob Phillips" wrote in message
:



If it is read-only, they can't save it anyway.



--

HTH



Bob



(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JMay" wrote in message

...

But what is a change is made AND it needs to be Saved?

Wont this code (at present) close the WB, without saving such changes?



TIA,

Jim

Supressing "Do you want to save the changes you made to <file?" message
 

No problem, u da man !!
Jim

"Norman Jones" wrote in message
:

Hi Jim,

I missed the word "most"! Well spotted!

That being the case, try something like:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Arr As Variant
Dim Res As Variant

Arr = VBA.Array("Chrissol", "Jim", "Bob", "Norman")

Res = Application.Match(Environ("UserName"), Arr, 0)
Me.Saved = IsError(Res)
End Sub
'<<=============

The array values correspond to the users who do have
write permission.


---
Regards,
Norman




"JMay" wrote in message
...
Thanks Bob, But OP says (that):

I have an Excel system that most of my users are only allowed to open

and view read-only



Just thought I'd mention.



"Bob Phillips" wrote in message
:



If it is read-only, they can't save it anyway.



--

HTH



Bob



(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JMay" wrote in message

...

But what is a change is made AND it needs to be Saved?

Wont this code (at present) close the WB, without saving such changes?



TIA,

Jim