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

 

[hrayani]

Hi davernut,

Sorry for a late reply, I was on holidays.

Do you get any dialogs when you interrupt the macro?

Sometimes yes & sometimes no. But when I do get the message its......

code execution has been interrupted

Can you post the function GetMacAddress
Macro show_index_sheet

Sure, why not

Function GetMACAddress() As String    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    Dim myMACAddress As String
    
    sComputer = "."
    
    Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
    
    Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
    For Each oItem In cItems
        If Not IsNull(oItem.IPAddress) Then myMACAddress = oItem.MACAddress
        Exit For
    Next
    'it will return mac address in format MM:MM:MM:SS:SS:SS
    
    GetMACAddress = myMACAddress


End Function

Sub show_index_sheet()

Sheets("INDEX").Visible = True


Sheets("CURRENT PRODUCTION STATUS").Visible = xlSheetVeryHidden
Sheets("Supplier Wise").Visible = xlSheetVeryHidden
Sheets("Year Wise").Visible = xlSheetVeryHidden
Sheets("PO Wise").Visible = xlSheetVeryHidden
Sheets("DATABASE").Visible = xlSheetVeryHidden
Sheets("STATS").Visible = xlSheetVeryHidden
Sheets("R VALIDATION").Visible = xlSheetVeryHidden
Sheets("RUNNING ORDER STATUS").Visible = xlSheetVeryHidden
Sheets("FACTORY TARGETS").Visible = xlSheetVeryHidden
Sheets("R FRI DPI LABTEST").Visible = xlSheetVeryHidden
Sheets("R DAILY PRODUCTION").Visible = xlSheetVeryHidden
Sheets("R PO DESIGN COLOUR").Visible = xlSheetVeryHidden


Sheets("INDEX").Select


End Sub

 

[daverunt]

Hi,

I've been away for a few days.

I've added all the code including my MAC address but I am unable to reproduce what you report with the code given using a workbook open event
If while the workbook is opening and I press escape Excel closes - this is normal behaviour before any workbook opens.

If I paste the code (plus a loop to cycle down a column as a form of delay) in a normal module and run it, then press Escape, the ErrorHandler is invoked and my workbook closes.

What state is the workbook in when you are pressing Escape? Open, worksheet visible?
If it is open and you are Escaping what code is actually running and exiting?

As far as I can see any code in the Workbook Open event must complete before the workbook does in fact open.
So I don't think the Escape key code will work in this case because the code has completed once the book is open.

So I have to pass on this one. Sorry