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

[Chrisso]

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

 

[Norman Jones]

Hi Chrisso,

Try:

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

 

[JMay]

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

 

[Bob Phillips]

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]

Thanks Bob, But OP says (that):

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



Just thought I'd mention.

 

[Norman Jones]

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.

 

[JMay]

No problem, u da man !!
Jim

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.