Restrict User From Interrupting Macro

[hrayani]

Hello Friends,

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

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

 

[daverunt]

Gotta dash but I found this. It's untested but fits your request.

http://www.databison.com/prevent-users-ctrl-break-ing-your-vba-code-during-execution/

I guess you could close the file in the error handler for either method of break

 

[hrayani]

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

 

[daverunt]

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.....

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

 

[hrayani]

Thanks daverunt,

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

with this part of code being highlighted

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

Any idea ???

 

[hrayani]

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

 

[daverunt]

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?

 

[hrayani]

Sure why not......

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

Regards,

 

[hrayani]

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

 

[daverunt]

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