Restrict User From Interrupting Macro

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
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
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,734
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:

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
Windows
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 ???


 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,734
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

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
Windows
Sure why not......

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

Regards,
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
795
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,734
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:

Forum statistics

Threads
1,084,748
Messages
5,379,613
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top