none
Excel VBA - Clearcontents method not working in password-protected workbook RRS feed

  • Question

  • I am trying to create a macro which would clear contents from particular cells based on a criteria. The code snippet worked perfectly fine in the normal macro-enabled excel. However, I copy pasted the same code into a password-protected macro workbook and the same code is not working. I debugged and found that the it is reaching the correct cells and all the values are correct, except that the cell content is not cleared. Kindly help!!

    Dim i As Integer
            Dim j As Integer
            Dim step As Long
            Dim desc As Long
            Dim sCol As String
            Dim sVal As String
            Dim sDesc As String
            step = Application.WorksheetFunction.Match("Step Name", Sheet2.Range("2:2"), 0)
            desc = Application.WorksheetFunction.Match("Description", Sheet2.Range("2:2"), 0)
            sVal = Cells(3, step).Value
            MsgBox (step)
            MsgBox (desc)
    sCol = Left(Cells(1, step).Address(False, False), _
            1 - (ColNumber > 26))
    sDesc = Left(Cells(1, desc).Address(False, False), _
            1 - (ColNumber > 26))
            MsgBox (sCol)
            MsgBox (Cells(3, step).Value)
            i = 3
            j = step

            While Cells(i, step) <> ""
            MsgBox ("Entering loop")
            MsgBox (i)
            MsgBox (j)
            If Cells(i, step).Value <> sVal Then
                Worksheets("Sheet2").Cells(i, step - 1).ClearContents
            End If
            i = i + 1
            Wend
        MsgBox ("Ending loop")

    Wednesday, January 23, 2013 12:13 PM

All replies

  • If Cells.Locked=True and Sheet.Protected=True then

    Excel VBA will will not allow you to edit/delete.And normally all cells of sheet is Locked by default.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, January 23, 2013 1:06 PM
    Answerer
  • Thanks for your response. But I am not able to unlock or unprotect the sheet. Can you tell me how I can do it?

    I tried setting it to false at the workbook level and at the sheet level, but nothing worked.

    Monday, January 28, 2013 9:29 AM
  • If you can't unprotect the worksheet you can't change locked cells. For code to unprotect a worksheet, record a macro.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, January 28, 2013 8:06 PM
  • Thanks Rod.

    I tried recording in the same workbook, but I couldn't record it. I tried recording the macro in another Excel but unprotected the sheet in the required workbook and the code recorded was Activesheet.unprotect.

    I tried the same code, but still I was not able to clear the contents.

    Kind Regards,

    Annie

    Wednesday, January 30, 2013 4:41 AM
  • Do you know the password ? then insert below line....

    ActiveSheet.Unprotect password:="YourActualPassword"


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, January 30, 2013 8:24 AM
    Answerer
  • Yes Asadulla, I know the password and I exactly tried the same as you have given. But still it doesn't work. The strange part is it doesn't give me any error. It just doesn't unprotect and hence it doesn't clear the contents.

    Kind Regards,

    Annie

    Thursday, January 31, 2013 3:59 AM
  • Just a guess.Is more than one sheet selected ?Are you able to unprotect atleast manually ?

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, January 31, 2013 8:22 AM
    Answerer