VBA & Macro Problems

Allow Row Height Changes in a Protected Excel Sheet on Mac

Ayan MasoodAyan Masood Nov 22, 2026 868 views

Question details

Allow users to change row heights only for rows 18 through 43 on a protected worksheet.

Allow Row Height Changes in a Protected Excel Sheet on Mac
Product
Microsoft Excel for Mac
Device & OS
macOS
Scenario
Users need to adjust the height of specific rows without removing worksheet protection themselves.
Observed behavior
Excel does not offer a built-in protection setting that restricts row-height changes to a particular range.
Before you start

Check that you know the worksheet password and can use macros in Excel for Mac. Enable the Developer tab through Excel > Preferences or Settings > Ribbon & Toolbar if it is not visible.

Solution 1Recommended

Use a VBA button to change heights in rows 18–43

Use this approach when users must be limited to changing the height of one row at a time within the specified range.

The macro validates the requested row and height, temporarily unprotects the active worksheet, makes the change, and protects it again. Users must enable macros for the button to work.

The Protect command below uses Excel's default protection options, which may differ from the sheet's existing permissions. Customize the Protect arguments if your workbook requires other permissions. The password is stored in the code; locking the VBA project only discourages casual viewing and is not strong security.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select the workbook in Project Explorer, then choose Insert > Module.

2
Add the macro

Paste the following code into the module and replace YOUR_PASSWORD with the worksheet password: Sub ChangeAllowedRowHeight() Dim ws As Worksheet Dim rowInput As Variant, heightInput As Variant Dim pw As String, errorText As String Set ws = ActiveSheet pw = "YOUR_PASSWORD" If Not ws.ProtectContents Then MsgBox "Protect this worksheet before using the button." Exit Sub End If rowInput = Application.InputBox("Enter a row number from 18 to 43:", "Row height", Type:=1) If VarType(rowInput) = vbBoolean Then Exit Sub If rowInput < 18 Or rowInput > 43 Or rowInput <> Int(rowInput) Then MsgBox "Enter a whole row number from 18 to 43." Exit Sub End If heightInput = Application.InputBox("Enter a height from 1 to 409 points:", "Row height", Type:=1) If VarType(heightInput) = vbBoolean Then Exit Sub If heightInput < 1 Or heightInput > 409 Then MsgBox "Enter a height from 1 to 409 points." Exit Sub End If On Error GoTo Failed ws.Unprotect Password:=pw ws.Rows(CLng(rowInput)).RowHeight = CDbl(heightInput) ws.Protect Password:=pw MsgBox "Row height updated." Exit Sub Failed: errorText = Err.Description On Error Resume Next If Not ws.ProtectContents Then ws.Protect Password:=pw On Error GoTo 0 If ws.ProtectContents Then MsgBox "Could not complete the change: " & errorText Else MsgBox "Could not complete the change, and the sheet is not protected. Protect it manually. Error: " & errorText End If End Sub

3
Save as a macro-enabled workbook

Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm).

4
Add and assign a button

If the sheet is protected, choose Review > Unprotect Sheet and enter its password. Select Developer > Insert, add a Form Control button to the worksheet, and assign the ChangeAllowedRowHeight macro when prompted.

5
Protect and test the sheet

Select Review > Protect Sheet, set the required permissions and password, and click the button. Test a permitted row such as 20 and an out-of-range row such as 44.

Use a VBA button to change heights in rows 18–43
Protect the VBA project: To discourage casual viewing of the password, open the VBA editor and select Tools > VBAProject Properties > Protection. Select Lock project for viewing, set a password, and save the workbook. This is not a substitute for protecting sensitive data.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Excel formats, making it easier to move everyday spreadsheet work between applications. Use Excel for the VBA-based protection workflow described above.

Open and edit spreadsheets with familiar row and column toolsCompatible with common Microsoft Excel formats, including XLSXFree and lightweight for everyday office workFamiliar interface for a smoother transition from Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel allow row-height changes only in rows 18–43 without a macro?

No. Review > Protect Sheet offers a Format rows permission, but it applies across the worksheet rather than to a selected range.

Why does the workbook need to be saved as XLSM?

The macro is stored in the workbook. Select File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code.

What happens if someone enters a row outside the allowed range?

The macro checks the row number before unprotecting the sheet. It displays a message and exits if the number is not a whole number from 18 through 43.

Can users change several row heights at once with this macro?

No. The button prompts for one row number and one height per run. Repeat the action for another row, or modify the code to validate and process a range.

Does locking the VBA project hide the worksheet password securely?

No. Tools > VBAProject Properties > Protection can discourage casual viewing, but the password remains in the macro and VBA project locking is not strong security.