Run Excel Text to Columns VBA on a Protected Sheet
Question details
Run a Text to Columns macro while keeping an Excel worksheet protected from user edits.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook uses VBA to split text into columns on a protected worksheet.
- Observed behavior
- The worksheet should remain protected for user edits while VBA can modify it. UserInterfaceOnly protection must be reapplied whenever the workbook opens.
Make sure you know the worksheet protection password and can enable macros for the workbook. Check that the destination columns are empty, because Text to Columns can overwrite existing data.
Reapply UserInterfaceOnly protection when the workbook opens
Use this approach to keep the worksheet protected in the Excel interface while allowing the Text to Columns macro to run.
Open the workbook in Excel and press Alt+F11 to open the Visual Basic Editor. In Project Explorer, double-click ThisWorkbook and add: Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="your_sheet_password", UserInterfaceOnly:=True Next ws End Sub
Replace your_sheet_password with the worksheet password. If sheets use different passwords, protect each sheet separately with its matching password. Include any other required Protect options in the code so the intended permissions are applied.
In the Visual Basic Editor, select Insert > Module and add: Sub SplitText() With ThisWorkbook.Worksheets("Sheet1") .Range("A1:A10").TextToColumns Destination:=.Range("B1"), DataType:=xlDelimited, Comma:=True End With End Sub Change Sheet1, the source range, destination, and delimiter to match your data.
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Close and reopen the workbook, enable macros if prompted, and run SplitText from Developer > Macros. Reopening runs Workbook_Open and reapplies UserInterfaceOnly.
To discourage casual viewing of the password in the code, open the Visual Basic Editor and select Tools > VBAProject Properties > Protection. Select Lock project for viewing, enter and confirm a password, then save, close, and reopen the workbook.

Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for easier migration. It does not fix Excel's VBA protection behavior, so use Excel for the protected-sheet macro in this article.

Frequently Asked Questions
Why does UserInterfaceOnly need to be set every time the workbook opens?
Excel does not permanently store the UserInterfaceOnly setting. Put the Protect call in the Workbook_Open event in ThisWorkbook and enable macros when opening the file.
Can I use different passwords for different worksheets?
Yes. In Workbook_Open, call Protect separately for each worksheet and supply its matching password instead of using one password in a loop.
How do I prevent users from seeing the password in the macro?
In the Visual Basic Editor, select Tools > VBAProject Properties > Protection and enable Lock project for viewing. Save, close, and reopen the workbook. This discourages casual viewing but is not complete security.
What happens if the destination columns contain data?
Text to Columns can overwrite data in the destination area. Check the destination range and adjacent columns before running the macro, and change the Destination argument if needed.
What file type should I use for the VBA code?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm) so the workbook can retain its VBA code.




