Combining Subs in VBA

[DrPancho]

Hi

Sorry if my terminology isn’t correct I’m quite new to this, I’m stuck trying to get the below 2 actions to run one after another.

When a user attempts to save, a msgbox will appear saying “Nope no saving I’m afraid!” with only 1 option to cancel.

When the user click cancel I want the workbook to change from the active sheet to another sheet in the same workbook.

Can this be done?

Below is the code I’ve been messing around with but as I said I’m very new and pretty stuck lol.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "Nope no saving I'm afraid!", , "Your request has been denied!"
    Cancel = True
    
End Sub
Sub Activate_Workbook()
    'Activate Worksheet
    Workbooks("MyWorkbook.xls").Sheets("Change to this worksheet!").Activate
End Sub

Many thanks in advance.

Also the title might not be best suited for this request, If you can suggest something else it may help
somebody looking for something similar find it easier

 

[shg]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Cancel = True
  MsgBox "Prompt:=Nope no saving I'm afraid!", Title:="Your request has been denied!"
  Workbooks("MyWorkbook.xls").Sheets("Change to this worksheet!").Select
End Sub

But then you can never save the workbook ...

 

[DrPancho]

I can, i can save it in "Design Mode"... nobody else can save the workbook.. Thats the plan anyway lol

Also, Thanks i will try that now

 

[deletedalien]

He pancho but they could still do save as...

 

[DrPancho]

@sgh - When i run the code i get the error "Run-time error "9": Subscript out of range"
Any ideas?

@deletedalien - Yes i was thinking that, Is there any way to remove that option from the menu upon opening the workbook?

Thanks peeps

 

[deletedalien]

@sgh - When i run the code i get the error "Run-time error "9": Subscript out of range"
Any ideas?

@deletedalien - Yes i was thinking that, Is there any way to remove that option from the menu upon opening the workbook?

Thanks peeps

Disable Saveas:

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim lReply As Long
If SaveAsUI = True Then
lReply = MsgBox("Sorry, you are not allowed to save this Workbook."
End If
End Sub

 

[shg]

@sgh - When i run the code i get the error "Run-time error "9": Subscript out of range"

There has to be a workbook named MyWorkbook.xls open containing a sheet named Change to this worksheet!

 

[DrPancho]

Thanks, I get a syntax error on the below line

Disable Saveas:

lReply = MsgBox("Sorry, you are not allowed to save this Workbook."

 

[DrPancho]

shg - Sorry ignore my last message, Your code works fine!

My workbook was .xlsm not .xls

Thanks again!

 

[shg]

You're welcome.