• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Run-time error -2147024809 (80070057) - You cannot use this command on a protected sheet.... but the sheet is NOT protected!|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Run-time error -2147024809 (80070057) - You cannot use this command on a protected sheet.... but the sheet is NOT protected!|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosRun-time error -2147024809 (8007005…
sp_PrintTopic sp_TopicIcon
Run-time error -2147024809 (80070057) - You cannot use this command on a protected sheet.... but the sheet is NOT protected!
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
1
June 4, 2022 - 7:18 am
sp_Permalink sp_Print

I have a module with the following code called from a button on the sheet. Even if the sheet is unlocked (which I have checked both programmatically through VBA and manually by right-clicking the sheet tab), I get an error saying that the sheet is protected. And the error number is negative! It's "-2147024809 (80070057)." 

Option Explicit

Public Type SlicerFormatSettings
  Name As String
  DisplayHeader As Boolean
  Caption As String
  Top As Single
  Left As Single
  Width As Single
  Height As Single
  NumberOfColumns As Long
End Type


Sub PrepWork()

 ' Unprotect the sheet before doing the work
  Dim Unlocked As Boolean
  Unlocked = LockSheet(ActiveSheet.Index, False)

  Dim FormatSettings As SlicerFormatSettings
  Dim SlicerFormat As SlicerFormatSettings

  ' Set slicer properties
  With SlicerFormat
    .Name = "NEW_SLICER"
    .DisplayHeader = True
    .Caption = "Slicer Caption"
    .Top = Application.InchesToPoints(1.25)
    .Left = Application.InchesToPoints(5)
    .Width = Application.InchesToPoints(1.25)
    .Height = Application.InchesToPoints(1.5)
    .NumberOfColumns = 1
  End With

  ' Apply properties to the slicer
  Call SetSlicerProperties

  ' Protect the sheet after doing the work
  Dim Locked As Boolean
  Locked = LockSheet(ActiveSheet.Index, True)

End Sub

Sub SetSlicerProperties( _
    ByVal myName As String, _
    ByVal myDisplayHeader As Boolean, _
    ByVal myCaption As String, _
    ByVal myTop As Single, _
    ByVal myLeft As Single, _
    ByVal myWidth As Single, _
    ByVal myHeight As Single, _
    ByVal myNumberOfColumns As Long)

  ' Make sure the sheet is unlocked
  If Islocked(ActiveSheet.Index) Then Msgbox "Try again. The sheet is locked." : Exit Sub

  Dim mySlicer As Slicer

  ' Get the slicer object (or exit)
  Set mySlicer = GetSlicer(myName)
  If mySlicer Is Nothing Then
    MsgBox "That slicer was not found"
    Exit Sub
  End If

  With mySlicer
    ' Set slicer dimensions
    .NumberOfColumns = SlicerFormat.NumberOfColumns   <---- ERROR HAPPENS HERE
   ' Set slicer position
    With .Shape
      .Top = SlicerFormat.Top
      .Left = SlicerFormat.Left
      .Width = SlicerFormat.Width
      .Height = SlicerFormat.Height
      .Placement = xlFreeFloating
      .locked = False
    End With ' Shape

    ' Set slicer caption, style, display header choice, sort and filter options, and lock option  
    With mySlicer
      .Caption = SlicerFormat.Caption
      .Style = "SlicerStyleDark5"
      .DisplayHeader = SlicerFormat.DisplayHeader
      .SlicerCacheLevel.SortItems = xlSlicerSortAscending
      .SlicerCacheLevel.CrossFilterType = xlSlicerSortAscending ' xlSlicerCrossFilterHideButtonsWithNoData
      .DisableMoveResizeUI = True
    End With 'slcr

  End With ' mySlicer

End Sub

Function IsLocked(ByVal iSheetIndex As Integer) As Boolean

  ' Return true if sheet is protected, false if not
  IsLocked = ActiveSheet.ProtectContents

End Function

Function LockSheet(ByVal iSheetIndex As Integer, bSwitch As Boolean) As Boolean

  LockSheet = False

  Dim SecretPassword as String
  SecretPassword = "12345"
  
  ' Protect or unprotect the sheet
  If bSwitch Then ' Protect
    ThisWorkbook.Worksheets(iSheetIndex).Protect Password:=SecretPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

  Else ' Unprotect
    ThisWorkbook.Worksheets(iSheetIndex).Unprotect Password:=SecretPassword

  End If

  If Err.Number = 0 Then LockSheet = True

End Function

Function GetSlicer(sName As String) As Slicer

  Dim N As Long
  Dim C As Long

  ' In case of run-time error, move to next statement and continue execution
  On Error Resume Next

  C = ActiveWorkbook.SlicerCaches.Count
  For N = 1 To C
    Set GetSlicer = ActiveWorkbook.SlicerCaches(N).Slicers(sName)
    If Not GetSlicer Is Nothing Then Exit Function
    Debug.Print "GetSlicer " & N & " of " & C & ": " & sName
  Next N

End Function

Excel-Negative-Error.JPGImage Enlarger
Excel-Code-Snippet.JPGImage Enlarger
sp_PlupAttachments Attachments
  • sp_PlupImage Excel-Negative-Error.JPG (31 KB)
  • sp_PlupImage Excel-Code-Snippet.JPG (89 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
June 6, 2022 - 2:54 pm
sp_Permalink sp_Print

The IsLocked function is wrong.

Function IsLocked(ByVal iSheetIndex As Integer) As Boolean

  ' Return true if sheet is protected, false if not
  IsLocked = ActiveSheet.ProtectContents

End Function

It is checking the active sheet, NOT the sheetIndex argument passed to the function.

Hard to evaluate based on images, you should try that sometime 🙂

If you cannot prepare a sample file that replicates the error, you can send the file to us using our helpdesk, where your file remains private:

https://www.myonlinetraininghu...../helpdesk/

sp_AnswersTopicAnswer
Answers Post
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
3
June 7, 2022 - 6:12 am
sp_Permalink sp_Print

THANK YOU! It was right there and I missed it.

And... yes, I am trying to get better (and more thoughtful) about creating shareable sample files.

As always... much appreciated.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
June 11, 2022 - 5:33 pm
sp_Permalink sp_Print

Hi Shawn,

The error comes from the fact that you are wrongly using the Disable Resize from UI.

Instead of:

' Enable resizing and moving slicers
Call EnableResizeMoveSlicers(sheetindexnumber, True)

' Align slicers vertically
Call SpaceSlicers(sheetindexnumber)

' Disable resizing and moving slicers
Call EnableResizeMoveSlicers(sheetindexnumber, False)

 

It should be the opposite, set it to False before calling SpaceSlicers to enable resizing.

Setting the option to True DISABLES the resizing.

' Enable resizing and moving slicers
Call EnableResizeMoveSlicers(sheetindexnumber, False)

' Align slicers vertically
Call SpaceSlicers(sheetindexnumber)

' Disable resizing and moving slicers
Call EnableResizeMoveSlicers(sheetindexnumber, True)

It's a confusion caused by misleading function names. It should be named DisableResizeMoveSlicers:

DisableResizeMoveSlicers(sheetindexnumber, False) to allow resizing

DisableResizeMoveSlicers(sheetindexnumber, True) to disable resizing

 

You are excessively using The Error Resume Next statement.

You should allow errors to break the execution, this will force you to analyze the cause then MANAGE the cause of the error properly.

Hiding the errors under the carpet does not mean that everything is right, you should use it ONLY when you know exactly what error you want to avoid or ignore.

To debug your app, I disabled all these statements (lots of them), after fixing the problem described above, the code works without enabling them back.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Ben Hughes, Alison West, Ramon Lagos, Shawn Barwick
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.