VBA & Macro Problems

Run Excel Text to Columns VBA on a Protected Sheet

Khadija KhanKhadija Khan Nov 22, 2026 868 views

Question details

Run a Text to Columns macro while keeping an Excel worksheet protected from user edits.

Run Excel Text to Columns VBA on a Protected Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Add the startup event

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

2
Adjust the protection settings

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.

3
Add the Text to Columns macro

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.

4
Save, reopen, and run

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.

5
Optionally lock the VBA project

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.

Reapply UserInterfaceOnly protection when the workbook opens
Protection is not a security boundary: Worksheet and VBA project passwords do not provide complete security. UserInterfaceOnly is not stored permanently, so the startup event must run each time the workbook opens.
Free Microsoft Office alternative

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.

Split spreadsheet text into columns using a familiar interfaceCompatible with Microsoft Excel formats such as XLSXOpen and edit everyday spreadsheets with a lightweight office suiteMigrate common spreadsheet workflows using familiar tools
microsoft office alternative - wps office

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.