Vba Save and Save as STOP

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
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

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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
The problem is an extra "exit sub":
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
 [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\
 
Last edited:

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
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>
 

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,863
Messages
5,598,504
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top