Vba Save and Save as STOP

[Eisasuarez]

Hi All,

All i am trying to do is to STOP any other users apart from a few people to be able to SAVE and SAVEAS (Create Copies)

Now if the person presses SAVE then msgbox "You cannot SAVE" should appear

and

If the person presses SAVE AS top create a copy the MSGBOX "You cannot create copies" Should appear

but for some reason only the first part of the code runs...I dont know how to make the system recognise which one was done to pop up the relevant message

This is the CODE

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Environ("Username") <> "LG1" AND Environ("Username") <> "PJ1" Then
 SaveAsUI = True Then
 MsgBox "You cannot make copies of this Workbook"
 Cancel = True
 Exit Sub
Else
 MsgBox "You do not have permission to SAVE", , "Warning!!!"
 Cancel = True
 Exit SUB
End If


End Sub

 

[Eisasuarez]

Would really appreciate if someone could help me

Thank You

 

[James Snyder]

The problem is an extra "exit sub":

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If Environ("Username") <> "LG1" AND Environ("Username") <> "PJ1" Then  
SaveAsUI = True 
Then  MsgBox "You cannot make copies of this Workbook"  
Cancel = True
 [B] [COLOR=#ff0000]Exit Sub[/COLOR][/B] 
Else  MsgBox "You do not have permission to SAVE", , "Warning!!!"
  Cancel = True
 [B][COLOR=#ff0000] Exit SUB[/COLOR][/B]
 End If 
End Sub\

 

[Eisasuarez]

Hi That doesn't appear to be the problem

Say i pressed Cntrl + save then this part of the code will run

SaveAsUI = True Then MsgBox "You cannot make copies of this Workbook" Cancel = True Exit SubHowever if i pressed F12 or SAVE AS the same part runs so the 2nd part after the else never runs</pre>

 

[Eisasuarez]

Bump

 

[James Snyder]

Every sub, function and procedure is built with a single entry and exit point. Within the If Then Else structure is a test that will direct it one way or the other and then the statement is done. You are unlucky that this compiled without telling you that there were extra "Exit Sub" statements, or you would have caught it long ago.