Create an Excel Worksheet with VBA Text Box and Option Buttons
Question details
Create a worksheet whose name combines an entered number with a selected Quote or Invoice option.

- 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.
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.
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.
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.
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.
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
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 named ActiveX controls when the workbook runs in Excel for Windows
Use this option only when the workbook is intended for desktop Excel for Windows and requires a TextBox1 control and named option buttons.
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. Install WPS Office: Download and install WPS Office on your computer, then open WPS Spreadsheets.
- 2. Open the Excel workbook: Select Open and choose the existing .xlsx, .xls, or .xlsm workbook that contains your worksheet workflow.
- 3. Review workbook automation: Check worksheet names, formulas, controls, and any VBA-dependent features before using the workbook for production tasks.

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.




