Workbook BeforeSave Event causing workbook to close

Hi

I'm trying to create a workbook which users update then send via a separate command button to the 'data collector'. I'm trying to build in that they can't save the workbook, only the 'data collector' can.
I've added in the below event but when the user clicks OK on the message box the workbook closes, what I'd like it to do is when OK is clicked the user returns back to the workbook so they can 'click the send button'.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Environ("Username") <> "jrussell" Then
 Cancel = True
 MsgBox "You cannot save this form, click the Send button instead"
End If
End Sub

Can anyone see where I'm going wrong?

Thanks!

Hello Jo2710,

This where the Cancel argument comes in. Setting Cancel to True exits the event. Do this after the message is displayed.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Environ("Username") <> "jrussell" Then
 MsgBox "You cannot save this form, click the Send button instead"
 Cancel = True
End If
End Sub

@Leith: so you need

Cancel = True

twice?

@ Trevor,

The site is a little slow in updating. Cancel is set only once, after the message is displayed.

@Leith: ah, so it is, no surprise there ... and now it makes sense.

So, basically, do whatever you want/need to do and THEN cancel.

Cheers, TMS

@Trevor,

No, it is Scottish Gaelic.

@ Trevor,

Mar bu choir. (Exactly)

In Klingon?

@ Trevor,

I have notified Google many times that Irish (Gaeilge) and Scottish (Gàidhlig) are not the same language. But, they don't care.

Ah, that's may be why Google tried (and failed) with Irish

Hello Jo2710,

It is usually the simple things that escape our attention. I've done the same thing many times.

I'll marked this post as solved.