Restrict User From Interrupting Macro

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using the below code which is linked with Workbook open event

Code:
Private Sub Workbook_Open()

Application.Calculation = xlCalculationAutomatic


Call show_index_sheet


Sheets("INDEX").Unprotect Password:="merchant"


Sheets("INDEX").Range("A1").Value = GetMACAddress()

If Sheets("INDEX").Range("A1") = "A0:C5:89:28:4A:6A" Or _
   Sheets("INDEX").Range("A1") = "50:3E:AA:65:02:2F" Or _
   Sheets("INDEX").Range("A1") = "C4:E9:84:17:B3:1B" Or _
   Sheets("INDEX").Range("A1") = "14:CC:20:1F:FD:20" Then
           
Sheets("INDEX").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="merchant"


Else


ActiveWorkbook.Close savechanges = False


End If


End Sub

I have allowed only 4 users who can access the file & for that I have mentioned there mac addresses in the code. Now what I want is when a new user tries to open the file then it should close which it does but the problem is that the code can be interrupted like pressing the ESC key or control + break key.

Is there any way where I can restrict the user to interrupt the code.

Regards,

Humayun
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Thanks daverunt for the reply,

Can you please let me know how to do it as I am newbie to VBA...

If the macro is interrupted then the file should close - yes that would serve the purpose.

But I don't know what lines to enter & where to enter

Can you please help

Regards,

Humayun
 
Upvote 0
Hi,

Try this but in trying to simulate test this I'm not convinced this will work in a Workbook_Open event.
My simulation does seem to work in a normal module.....

Code:
Private Sub Workbook_Open()


Application.EnableCancelKey = xlErrorHandler
On Error GoTo MyError
Application.Calculation = xlCalculationAutomatic


Call show_index_sheet


Sheets("INDEX").Unprotect Password:="merchant"


Sheets("INDEX").Range("A1").Value = GetMACAddress()

If Sheets("INDEX").Range("A1") = "A0:C5:89:28:4A:6A" Or _
   Sheets("INDEX").Range("A1") = "50:3E:AA:65:02:2F" Or _
   Sheets("INDEX").Range("A1") = "C4:E9:84:17:B3:1B" Or _
   Sheets("INDEX").Range("A1") = "14:CC:20:1F:FD:20" Then
           
Sheets("INDEX").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="merchant"


Else


ActiveWorkbook.Close savechanges = False


End If

MyErrorHandler:
If Err.Number = 18 Then
ActiveWorkbook.Close savechanges = False
Exit Sub
End If


End Sub
 
Last edited:
Upvote 0
Thanks daverunt,

Its debuging with label not defined ... compile error.

with this part of code being highlighted

Code:
[/COLOR]On Error GoTo MyError[COLOR=#333333]

Any idea ???


 
Upvote 0
Hi,

I tried to work it out a bit and changed

This > MyErrorHandler:

To > MyError:

Now the code is working but I am still able to interrupt the code and the file still not closes :(
 
Upvote 0
Hi,

I'm not sure why it's failing.

Can you move the code out of the ThisWorkbook and paste it into a module and run it as a normal macro just to see if it works?
 
Upvote 0
Sure why not......

I will try tomorrow as right now I don’t have access to my computer.
Will keep you posted

Regards,
 
Upvote 0
Hi,

I Moved the code out of this Workbook and pasted it into a module and runs it as a normal macro.

Still I am able to interrupt the macro

With ESC key or CTL+BREAK :(
 
Upvote 0
I would have expected it to work.
Do you get any dialogs when you interrupt the macro?

Can you post the function GetMacAddress
Macro show_index_sheet
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top