Unable to run macro on locked sheet

[vk7]

Hello All,

I have a requirement to do "TexttoColumn" operations on a selected range ("A1:A50") using a macro and wanted to lock all other cells in the sheet. So unlocked the cells from ("A1:A50") and then locked all the other cells in the Sheet and then protected the sheet.

However, when I run the macro it says "it can't be executed in the protected sheet". How can I fix this without keeping my worksheet unlocked? I have attached a sample sheet here with.

Note: PWD (excel2013)

 

[NARAYANK991]

Hi ,

There is no alternative other than to unprotect your worksheet.

Even when you work in Excel , without recourse to VBA , once the worksheet is protected , the Text to Columns menu option is grayed out and unavailable.

It is very unlikely that VBA can offer a work around other than to unprotect the worksheet first.

Narayan

 

[vk7]

Could you share me the said workaround if possible?

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[p45cal]

Sheet protection is often only used to protect the sheet from pesky users messing it about. An option is available to allow code to change a sheet while still preventing users from changing it.
You can use the likes of:
ws.Protect "excel2013", UserInterfaceOnly = True
on a sheet (even if it's already protected with the same or no password)
Then there is no need to unprotect/reprotect the sheet.

Better still, you can unprotect the sheet this way when the workbook is first opened, with the Workbook_Open event handler, it will keep its UserInterfaceOnly protection as long as the file remains open, so ALL vba code will be able to change that sheet without doing anything about protection.

 

[vk7]

Sub Text2Column()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Unprotect "excel2013"
    ws.Range("A1:A20").Select
    Selection.TextToColumns , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
    Columns("A:A").EntireColumn.AutoFit
    ws.Protect "excel2013"
    Application.DisplayAlerts = True
End Sub

Just wondering if this can be improved a bit. The above converts the data in column A to TexttoColumn. Here the problem is, if there is no data in ColumnA and if someone executes the above macro. Then it will throw the below error and as a result, the sheet will get unprotected. I am looking for someway to handle this exception. Also is there a better way to shorten the code?

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[vk7]

Thank you, @NARAYANK991 that helps!