Create a Dynamic Transaction Number with Textbox and OptionButtons in Excel Userform

A textbox is a control that allows the user to enter or display text on a userform. An optionbutton is a control that allows the user to select one of a predefined set of options on a userform. A userform is a custom dialog box that can contain various controls to interact with the user or display information.

To change the character when showing in textbox on userform based on optionbutton, we need to use VBA code to manipulate the value of the textbox according to the value of the optionbutton. VBA is a programming language that can be used to automate tasks and customize the functionality of Excel.

Procedures

To create a userform with a textbox and optionbuttons, we need to follow these steps:

1. In the Visual Basic Editor, insert a userform by clicking Insert > UserForm.
2. In the Toolbox, select the textbox control and draw it on the userform.
3. In the Toolbox, select the optionbutton control and draw it on the userform. Repeat this step to create as many optionbuttons as needed.
4. In the Properties window, change the name and caption properties of the textbox and optionbuttons as desired. For example, we can name the textbox as TextBox1 and the optionbuttons as OptionButton1, OptionButton2, and OptionButton3. We can also change the caption of the optionbuttons to Sales, Purchase, and Other.
5. In the Code window, write the VBA code to change the character when showing in textbox on userform based on optionbutton. For example, we can use the following code:

Option Explicit
Private Sub UserForm_Initialize()
    'Initialize the textbox value as Sales No 0004300
    Me.TextBox1.Value = "Sales No 0004300"
    'Select the first optionbutton by default
    Me.OptionButton1.Value = True
End Sub

Private Sub OptionButton1_Click()
    'Change the textbox value to Sales No 0004300 if the first optionbutton is selected
    Me.TextBox1.Value = "Sales No 0004300"
End Sub

Private Sub OptionButton2_Click()
    'Change the textbox value to Purchase No 0004300 if the second optionbutton is selected
    Me.TextBox1.Value = "Purchase No 0004300"
End Sub

Private Sub OptionButton3_Click()
    'Change the textbox value to Other No 0004300 if the third optionbutton is selected
    Me.TextBox1.Value = "Other No 0004300"
End Sub

Explanation

The code above uses the UserForm_Initialize event to set the initial value of the textbox and the optionbutton when the userform is loaded. The UserForm_Initialize event occurs when a userform is created but before it is displayed.

The code also uses the OptionButton_Click events to change the value of the textbox when the user clicks on an optionbutton. The OptionButton_Click event occurs when the user changes the state of an optionbutton by clicking on it.

The code uses the Me keyword to refer to the userform object and its controls. The code also uses the Value property to get or set the value of the textbox and the optionbutton. The Value property returns or sets a Boolean value for an optionbutton, indicating whether it is selected or not. The Value property returns or sets a String value for a textbox, containing the text displayed in it.

The code uses the concatenation operator (&) to join two strings together. For example, the expression “Sales No ” & “0004300” returns the string “Sales No 0004300”.

Scenario

Suppose we want to create a userform that allows the user to enter or display a transaction number based on the type of transaction. The transaction number consists of a prefix that indicates the type of transaction (Sales, Purchase, or Other) and a suffix that is a seven-digit number. The user can select the type of transaction from three optionbuttons and the transaction number will be displayed in a textbox. The user can also edit the transaction number in the textbox if needed.

Calculation

To implement this scenario, we can use the same code as above, but we need to modify the UserForm_Initialize event to generate a random suffix for the transaction number. We can use the following code:

Option Explicit
Private Sub UserForm_Initialize()
    'Generate a random suffix for the transaction number
    Dim suffix As String
    suffix = Format(Int(Rnd * 10000000), "0000000")
    'Initialize the textbox value as Sales No followed by the suffix
    Me.TextBox1.Value = "Sales No " & suffix
    'Select the first optionbutton by default
    Me.OptionButton1.Value = True
End Sub

The code above uses the Rnd function to generate a random number between 0 and 1. The code then multiplies the random number by 10000000 and rounds it down to the nearest integer using the Int function. The code then formats the integer as a seven-digit string using the Format function. The code then concatenates the string “Sales No ” with the suffix and assigns it to the textbox value.

Excel table

To display the userform and the transaction number in an excel table, we can use the following steps:

1. In the Visual Basic Editor, insert a module by clicking Insert > Module.
2. In the Code window, write the following code to show the userform:

Sub ShowUserForm()
    'Show the userform as a modal dialog box
    UserForm1.Show
End Sub

3. In the Excel worksheet, create a table with two columns: Transaction Type and Transaction Number.
4. In the Excel worksheet, assign the ShowUserForm macro to a button or a shape by right-clicking on it and selecting Assign Macro.
5. Click on the button or the shape to show the userform.
6. Select the type of transaction from the optionbuttons and enter or edit the transaction number in the textbox.
7. Click on the OK button on the userform to close it and transfer the transaction type and number to the table.

Other approaches

There are other possible approaches to achieve the same functionality as the userform. For example, we can use:

• Data validation and formulas: We can use data validation to create a drop-down list of transaction types in a cell and use formulas to generate and display the transaction number in another cell.
• ActiveX controls and macros: We can use ActiveX controls such as optionbuttons and textboxes to create a user interface on the worksheet and use macros to change the value of the textbox based on the optionbutton selection.
• User-defined functions and custom formats: We can use user-defined functions to create a custom function that returns the transaction number based on the transaction type and use custom formats to display the transaction number in a desired format.