VBA & Macro Problems

Create an Excel Worksheet with VBA Text Box and Option Buttons

Tauseeq MagsiTauseeq Magsi Nov 12, 2026 870 views

Question details

Create a worksheet whose name combines an entered number with a selected Quote or Invoice option.

Create an Excel Worksheet with a VBA Text Box and Option Buttons
Product
Microsoft Excel for Mac
Device & OS
macOS
Scenario
The user needs worksheet controls and a VBA macro to collect a number, choose Quote or Invoice, and add a correctly named worksheet.
Observed behavior
Excel for Mac may not provide the same VBA UserForm or ActiveX control support as Excel for Windows.
Before you start

Enable macros for the workbook and save it in the Excel Macro-Enabled Workbook (*.xlsm) format. Decide whether the workbook must run on macOS, because ActiveX text boxes are primarily a Windows Excel feature.

Solution 1Recommended

Use an input cell and Form Control option buttons on Excel for Mac

Use this Mac-friendly approach when you need the same worksheet-creation result without relying on an ActiveX VBA text box.

Excel for Mac can use worksheet cells as input fields and Form Control option buttons for the selection. Link the option buttons to a cell so the VBA macro can identify the selected value.

1
Create the number input cell

On the worksheet, enter a label such as Number in A2 and use B2 as the input cell where users enter the number for the new worksheet name.

2
Add and link the option buttons

Open the Developer tab, select Insert, and add two Form Control Option Buttons. Set their captions to Quote and Invoice, then right-click each button, choose Format Control, and set both Cell link fields to $B$3. The linked cell returns 1 for Quote or 2 for Invoice.

3
Add the worksheet-creation macro

Press Option+F11 or open Developer > Visual Basic, choose Insert > Module, and paste this macro: Sub CreateNewTab() Dim newName As String Dim selectedType As String Dim inputValue As String inputValue = Trim(ActiveSheet.Range("B2").Value) Select Case ActiveSheet.Range("B3").Value Case 1: selectedType = "Quote" Case 2: selectedType = "Invoice" End Select If inputValue = "" Or selectedType = "" Then MsgBox "Enter a number and select Quote or Invoice." Exit Sub End If newName = inputValue & " " & selectedType If Len(newName) > 31 Or newName Like "*[:\/?*[]*" Then MsgBox "The worksheet name contains invalid characters or exceeds 31 characters." Exit Sub End If On Error Resume Next If Worksheets(newName) Is Nothing Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = newName Else MsgBox "A worksheet named '" & newName & "' already exists." End If On Error GoTo 0 End Sub

4
Assign the macro to a button

Choose Developer > Insert > Button, draw the button on the worksheet, and select CreateNewTab when Excel asks which macro to assign. Enter a number, choose an option, and click the button to test it.

Use an input cell and Form Control option buttons on Excel for Mac
Mac compatibility: Using a worksheet cell instead of an ActiveX text box avoids a common Excel for Mac limitation while preserving the same user workflow.
Free Microsoft Office alternative

Manage Excel Workbooks and Spreadsheet Tasks with WPS Office

If Excel for Mac control limitations interrupt your workflow, WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and seamless document migration. Open your existing workbooks, review formulas and worksheet structure, and check macro compatibility before relying on VBA-dependent automation.

  1. 1. Install WPS Office: Download and install WPS Office on your computer, then open WPS Spreadsheets.
  2. 2. Open the Excel workbook: Select Open and choose the existing .xlsx, .xls, or .xlsm workbook that contains your worksheet workflow.
  3. 3. Review workbook automation: Check worksheet names, formulas, controls, and any VBA-dependent features before using the workbook for production tasks.
Free, lightweight spreadsheet software with a familiar Office-style interfaceOpen and edit Microsoft Excel .xlsx and .xls workbook formatsCreate, rename, and organize worksheets for quote and invoice workflowsImport existing Excel files with minimal disruption to workbook layouts
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel for Mac use ActiveX text boxes and option buttons?

ActiveX controls are primarily supported in Excel for Windows. For a Mac-compatible workflow, use a worksheet input cell with Form Control option buttons linked to a cell.

How do I make two option buttons act as Quote or Invoice choices?

Add two Form Control Option Buttons from Developer > Insert, place them in the same group, and set both controls to the same linked cell through Format Control. The linked cell returns a different number for each choice.

Why does Excel say the new worksheet name is invalid?

Worksheet names cannot exceed 31 characters or contain characters such as colon, backslash, slash, question mark, asterisk, or square brackets. Check the entered number and the combined name before adding the sheet.

What happens if a Quote or Invoice worksheet already exists?

A macro should check for an existing worksheet name before calling Worksheets.Add. If the name already exists, show a message instead of attempting to create a duplicate sheet.

Do I need to save the workbook as XLSM for this macro?

Yes. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA project and assigned macro button are retained.