+ Reply to Thread
Results 1 to 6 of 6

macro error when sheet is protected

  1. #1
    Registered User
    Join Date
    02-20-2018
    Location
    wales
    MS-Off Ver
    ms office 365 subscription
    Posts
    92

    macro error when sheet is protected

    The macro below causes my worksheet to stop working when its protected.

    I have a macro which pastes onto cells. It works to begin with as I have formatted the cells to unlocked
    The macro below clears the contents but it seems to be leaving the cells protected.
    So after clearing the contents with this the first macro stops working with a "sheet is protected error"

    Im really hoping to have a solution which keeps the sheet protected but leaves the cleard cell unlocked so they can be rewritten on.


    If anyone has any advice/solution to this problem that would be amazing. thansk


    Sub ResetSims()
    Dim lr As LongPtr
    Dim Num As LongPtr
    Dim LC As Range
    Dim CopyRow As Range
    Dim CopyTo As Range

    Application.ScreenUpdating = False

    Set LC = ActiveSheet.Rows("1:1").Find(What:="helper", LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If LC Is Nothing Then
    MsgBox ("The last column of data before the top 10 must be called 'helper'")
    Exit Sub
    End If

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

    Set CopyRow = ActiveSheet.Range(ActiveSheet.Cells(lr, 1), ActiveSheet.Cells(lr, LC.Column))
    Set CopyTo = ActiveSheet.Range("A3")
    Application.Calculation = xlManual
    CopyRow.Copy Destination:=CopyTo
    ActiveSheet.Range(Cells(4, 1), Cells(lr, LC.Column)).Clear
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: macro error when sheet is protected

    Hi Maxpower.
    I assume your sheet is protected with a password. You could try set these 2 lines in your code. First line unprotect your sheet. Second protect it again.
    Change your password between "" to the password you use to protect the sheet. I would try to set the lines in here in your code.
    Try it and see if working.
    Abjac



    Please Login or Register  to view this content.
    Forgot to say if not working you can upload a test sheet with your code and how it works. It could also be some how you lock and protect cells and protect the sheet after. But try it.
    Last edited by abjac; 04-09-2020 at 06:35 PM.

  3. #3
    Registered User
    Join Date
    02-20-2018
    Location
    wales
    MS-Off Ver
    ms office 365 subscription
    Posts
    92

    Re: macro error when sheet is protected

    hi abjac

    I popped your solution into the other macro as well. Your solution has the worksheet back working again when its protected. Its the best method I currently have now. Thanks a lot. The problem I have is that if I press escape during the macro it stops and the worksheet is left unprotected as the macro didn't finish. The macro sometimes takes a long time so its very likely people will be using escape to cancel it sometimes. I have found this online and wonder if you are familiar with the command and where it could fit into my macro.
    Attached Images Attached Images

  4. #4
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: macro error when sheet is protected

    Hi Maxpower.
    Well you can try disable so they cant stop the macro. I cant see you image. Not working i think.

    Try to put this line in just before the code/line for unprotect sheet.
    application.EnableCancelKey=False

    And put this line in just after the code/line to protect the sheet in the end before end sub.
    application.EnableCancelKey=True

    I never tried this not sure if working but try it. Will go bed here let me know if working .

    Abjac

  5. #5
    Registered User
    Join Date
    02-20-2018
    Location
    wales
    MS-Off Ver
    ms office 365 subscription
    Posts
    92

    Re: macro error when sheet is protected

    Thanks for the help abjac. I replaced ActiveSheet.Unprotect Password:="yourpassword" with UserInterFaceOnly:=True. Seems to be working well.

  6. #6
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: macro error when sheet is protected

    Thats great you solved it that way. I actually could see your picture in my computer and was thinking the same if the other not working you could try with that line to put in there. Good you solved it. Remember mark the thread as solved. Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 07-31-2018, 08:14 AM
  2. [SOLVED] Macro error for protected sheet
    By bryden2008 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2017, 04:17 PM
  3. [SOLVED] Macro Button Error on Protected Sheet
    By mwatson2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2015, 10:14 AM
  4. [SOLVED] Macro Giving Error On Protected Sheet
    By isc0rpi0 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2013, 01:07 AM
  5. Error- Running a Macro on a Protected Sheet
    By nacho in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2007, 08:21 PM
  6. Macro Error when Sheet is Protected
    By Johnny in forum Excel General
    Replies: 6
    Last Post: 07-28-2006, 02:45 PM
  7. [SOLVED] macro on protected sheet-error
    By michaelberrier in forum Excel General
    Replies: 6
    Last Post: 06-11-2006, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1