VBA UserForm with free text and radio buttons

SarahRhodes333 

In Excel for Mac, VBA userforms are not available, so far I know. However, you can achieve similar functionality using Excel's built-in form controls and VBA code. Here's how you can create a dialog box with a free text box and radio buttons to create a new tab based on the input:

1. Insert Form Controls:

• Go to the "Developer" tab (if it's not visible, enable it in Excel preferences).
• Click on "Insert" in the Controls group.
• Select "Text Box" and draw a text box on your worksheet for entering the number.
• Next, insert two option buttons (radio buttons) for "Quote" and "Invoice".

1. Add Code:

• Right-click on the sheet tab and select "View Code" to open the VBA editor.
• In the code window, paste the following code:

Vba code is untested, please backup your file.

Sub CreateNewTab()
    Dim ws As Worksheet
    Dim textBoxValue As String
    Dim radioButtonValue As String
    
    ' Get the values from the text box and radio buttons
    textBoxValue = ActiveSheet.Shapes("TextBox1").OLEFormat.Object.Text
    radioButtonValue = ""
    If ActiveSheet.Shapes("Option Button 1").OLEFormat.Object.Value = True Then
        radioButtonValue = "Quote"
    ElseIf ActiveSheet.Shapes("Option Button 2").OLEFormat.Object.Value = True Then
        radioButtonValue = "Invoice"
    End If
    
    ' Create a new worksheet with the specified name
    If textBoxValue <> "" And radioButtonValue <> "" Then
        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
        ws.Name = textBoxValue & " " & radioButtonValue
    Else
        MsgBox "Please enter a number and select either Quote or Invoice."
    End If
End Sub

1. Assign Macro to Button:

• Insert a button (Form Control) on your worksheet for creating the new tab.
• Right-click on the button and choose "Assign Macro."
• Select the "CreateNewTab" macro and click "OK."

1. Test the Functionality:

• Enter a number in the text box and select either "Quote" or "Invoice" using the radio buttons.
• Click the button to create a new tab with the specified name.

This setup replicates the functionality of a userform using Excel's built-in controls and VBA code. It allows you to enter a number and select an option, and then creates a new tab based on the input. The text, steps and the code were created with the help of AI

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.