#BLOCKED! error

[sparky2205]

Hi folks,
I have a spreadsheet with a number of macros.
Everything runs fine on my laptop but when I send the file to someone else I get the #BLOCKED! error.
I unblocked the file before sending it but I still have the issue.
This is what it looks like on other sets:

This is what it looks like on my set:

The offending macro is "Update Worksheet Names"

This is the code behind the macro:

Sub GetWorkSheetName()

Dim swsname As String
Dim iSheetCount As Integer
Dim c As Range
Dim cb  As CheckBox
Dim a As Integer: a = 3
Dim lr As Integer

Dim pw As String: pw = "Accipiter$17"
    Worksheets("Setup").Unprotect Password:=pw

On Error GoTo errhandler

    Application.ScreenUpdating = False

    iSheetCount = ThisWorkbook.Sheets.Count
    lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

    ActiveSheet.Range("A2:A" & lr).Clear
    ActiveSheet.CheckBoxes.Delete
    ActiveSheet.Range("A1").Clear
    With ActiveSheet.Range("B1:B100")
        .Clear
        .Locked = True
    End With
    ActiveSheet.Range("B3:B" & lr).Locked = False
    
    ActiveSheet.Range("A2").Formula2R1C1 = "=TRANSPOSE(INDEX(SheetNames,R[-1]C))"

    For Each c In Range("C3:C" & iSheetCount)
        Set cb = ActiveSheet.CheckBoxes.Add(c.Left + 25, _
                                    c.Top, _
                                    c.Width, _
                                    c.Height)
        With cb
            .Caption = ""
            .Value = xlOff
            .LinkedCell = "B" & a
            .Display3DShading = False
        End With
        a = a + 1
   Next

    'Application.DisplayAlerts = False
    ActiveSheet.Range("A2#").Copy
    ActiveSheet.Range("A2:A" & iSheetCount + 1).PasteSpecial xlPasteValues
    'Application.DisplayAlerts = True
    ActiveSheet.Range("A" & iSheetCount + 1).Clear
    ActiveSheet.Range("A2").Clear
    
    With ActiveSheet.Range("A1")
        .Value = "Worksheet Name"
        .Font.Bold = True
    End With
    
    lr = 0
    lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    
    With ActiveSheet.Range("A" & lr + 1)
        .Value = "Denotes hidden sheet"
        .Interior.ColorIndex = 6
        .BorderAround ColorIndex:=1
    End With
    
    Application.CutCopyMode = False
    Application.CutCopyMode = True
    Application.Range("A1").Select
    Application.ScreenUpdating = True
    
    Worksheets("Setup").Protect Password:=pw
Exit Sub

errhandler:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ThisWorkbook.Protect pw
    Worksheets("Setup").Protect Password:=pw
        
End Sub

I emailed the file to my colleague who had the issue.
I also had him open the file at a shared location which also resulted in the same issue for him.
There is no option to Unblock the file now.
The older macros, these were existing ones on the file; Copy, Rename, Delete a Worksheet all work fine on both sets. Prepare for Printing, which is a new macro, also works fine. I cannot check Hide or Unhide ALL Worksheets as these depend on the Worksheet Name list being populated.

Any assistance would be greatly appreciated folks.

 

[RoryA]

Unblocking the file on your computer does not affect what happens on another computer (or the file wouldn't have ever been blocked in the first place).

 

[sparky2205]

I understand the new macro security and how a file can be ok in one domain and not another which makes it computer independent.
I wanted to cover all bases and explain exactly what I've done so far to give as much information as possible to anyone looking at this post.

 

[RoryA]

What is Sheetnames referring to? If it’s a name using XLM then perhaps the recipient has Excel4 macros blocked.

 

[CephasOz]

When someone else gets a macro-enabled workbook, they need to:

1. close the file if its open,
2. open the folder where they have stored it,
3. right-click on the file,
4. select "Properties"
5. ensure that the "General" tab is showing
6. down the bottom, you'll see a Security item - tick "Unblock"
7. Click the "OK" button.

Then open the file.

 

[MARK858]

...or put the file in a Trusted Location

@sparky2205

Dim lr As Integer

should really be a Long rather than an Integer as you are using 365/2016

 

[sparky2205]

What is Sheetnames referring to? If it’s a name using XLM then perhaps the recipient has Excel4 macros blocked.

@RoryA
This is exactly what is happening. The recipients of the file didn't have Excel4 macros enabled. It works fine when they are enabled.
For my own information, is there a way to change that piece of code so that it works regardless of having Excel4 macros enabled or not?

@MARK858
thanks for that. I have changed that in my code.

@CephasOz
I had already gone that route without success.

Thank you to you all for your responses.
Another successful visit to MrExcel.

 

[RoryA]

Don't use the defined name. Simply loop and populate the sheet names you are interested in - for example:

    Dim idx As Long
    For idx = 1 To iSheetCount
      ws.Cells(2 + idx, "A").Value = Sheets(idx).Name
    Next idx

 

[sparky2205]

Hi Rory,
I'm testing this on a new workbook with 4 worksheets like this:

Sub Names()
    Dim idx As Long
    Dim iSheetCount As Long
    Dim ws As Worksheet
    
    iSheetCount = ThisWorkbook.Sheets.Count
    
    For idx = 1 To iSheetCount
      ws.Cells(2 + idx, "A").Value = Sheets(idx).Name
    Next idx
    
End Sub

I'm getting the; "Object variable of With block variable not set" error
What am I missing?

 

[RoryA]

You never assigned a worksheet to the ws variable.