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

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

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

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

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

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

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