Étiquette : create

VBA Code to Create a Dropdown List in Excel

This code will:

1. Define a range in a worksheet.
2. Populate it with a list of values.
3. Apply data validation to create a dropdown list in a target cell.

Sub CreateDropdownList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim targetCell As Range
    Dim listRange As Range   
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range where the dropdown options will be stored
    Set rng = ws.Range("A1:A5")   
    ' Populate the range with dropdown options
    rng.Value = WorksheetFunction.Transpose(Array("Option 1", "Option 2", "Option 3", "Option 4", "Option 5"))   
    ' Define the target cell where the dropdown list will be applied
    Set targetCell = ws.Range("C1")   
    ' Set up the validation list
    With targetCell.Validation
        ' Remove existing validation
        .Delete
        ' Add new validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rng.Address
        ' Optional: Display an input message when the cell is selected
        .InputTitle = "Select an Option"
        .InputMessage = "Choose from the list"
        ' Optional: Customize error message
        .ErrorTitle = "Invalid Selection"
        .ErrorMessage = "Please select a valid option from the dropdown list."
        .ShowInput = True
        .ShowError = True
    End With  
    ' Notify the user
    MsgBox "Dropdown list created in " & targetCell.Address, vbInformation, "Success"
End Sub

Explanation

1. Define Worksheet and Ranges
   • The script works on Sheet1, but you can change the sheet name as needed.
   • rng (A1:A5) holds the dropdown values.
   • targetCell (C1) is where the dropdown will appear.
2. Populate the Dropdown List
   • The rng.Value = WorksheetFunction.Transpose(Array(…)) fills the list dynamically.
3. Apply Data Validation
   • .Delete removes any existing validation in the target cell.
   • .Add Type:=xlValidateList creates a dropdown list.
   • Formula1:= »= » & rng.Address links the list source.
   • .InputTitle and .InputMessage show hints when selecting the cell.
   • .ErrorTitle and .ErrorMessage display custom error messages.
4. Notify the User
   • MsgBox confirms that the dropdown list was successfully created.

Creating a drop-down calendar (or date picker) in Excel using VBA (Visual Basic for Applications) typically involves using a calendar control or a custom dialog box. While Excel does not provide a built-in calendar control in all versions, you can work around this by using a UserForm (a custom form) or other controls.

Step 1: Add a Calendar Control in a UserForm

• Open the VBA Editor:
  • Open Excel and press Alt + F11 to open the VBA editor.
• Create a New UserForm:
  • In the VBA editor, go to the Insert menu and select UserForm to create a new UserForm.
• Add a Calendar Control:
  • In the toolbox (if visible), look for the Microsoft Date and Time Picker Control or Microsoft Calendar Control. If these controls are unavailable (which may vary by Excel version), we will simulate a calendar using buttons or labels.

If the control is not visible, right-click on the toolbox, choose « Additional Controls, » and add the calendar control if available.

• Add a Button to Open the Calendar:
  • You can add a Button on your Excel sheet that will open the UserForm.

Step 2: Create a Button to Show the Calendar

Go back to your Excel sheet, then add a button with the following steps:

• Insert a Button:
  • Go to the Developer tab, then click Insert, and choose a button from the Form Controls.
  • Place the button on your sheet.
• Assign a Macro to the Button:
  • Right-click the button and choose « Assign Macro », then select « New » to create a macro.

Step 3: VBA Code to Show the Calendar

Here is an example VBA code to open a calendar when you click the button. This code uses a UserForm with a DatePicker control and shows the selected date in an Excel cell.

• Code for the UserForm: If you’ve added a DatePicker control to your UserForm, use this code:

' Code for the UserForm
Private Sub Calendar1_Click()
    ' Once a date is selected from the calendar, place it in the active cell
    ActiveCell.Value = Calendar1.Value
    ' Close the UserForm after selection
    Me.Hide
End Sub

• Code to Open the UserForm with the Calendar: This code will open the UserForm when you click the button in Excel.

Sub OpenCalendar()
    ' Show the UserForm containing the calendar
    UserForm1.Show
End Sub

Step 4: Using the UserForm

Now that you’ve created the UserForm and attached the macro to the button:

• Click on the button in Excel.
• The UserForm with the calendar will pop up.
• You select a date, and it will be automatically inserted into the active cell in the Excel sheet.

Option Without DatePicker Control (if not available)

If the DatePicker control is not available in your version of Excel, you can create a custom calendar using buttons and labels to display the days of the month. This is a bit more complex and involves using loops and events to update the calendar each month.

Code VBA for a Custom Calendar (without DatePicker)

Here’s an example of a simple calendar without using a DatePicker control, using buttons to represent the days of the month:

• Create a Calendar Using Buttons and Labels: You can generate a custom calendar using buttons that represent the days of the month. This is a little more involved, but it’s a way to simulate a calendar.

Private Sub UserForm_Initialize()
    ' Initialize the calendar
    Dim i As Integer
    Dim j As Integer
    Dim d As Date
    Dim startDay As Integer
    Dim lastDay As Integer
    Dim currentMonth As Integer
    Dim currentYear As Integer   
    currentMonth = Month(Date)
    currentYear = Year(Date)   
    ' First day of the month
    d = DateSerial(currentYear, currentMonth, 1)
    startDay = Weekday(d, vbSunday)
    ' Last day of the month
    lastDay = Day(DateSerial(currentYear, currentMonth + 1, 1) - 1)   
    ' Clear the old buttons
    For i = 1 To 42
        Me.Controls("Button" & i).Visible = False
    Next i   
    ' Fill buttons with days
    For i = 1 To lastDay
        Me.Controls("Button" & (startDay + i - 1)).Caption = i
        Me.Controls("Button" & (startDay + i - 1)).Visible = True
    Next i
End Sub
Private Sub CommandButton1_Click()
    ' Function to go to the previous month
    currentMonth = currentMonth - 1
    If currentMonth = 0 Then
        currentMonth = 12
        currentYear = currentYear - 1
    End If
    Call UserForm_Initialize
End Sub
Private Sub CommandButton2_Click()
    ' Function to go to the next month
    currentMonth = currentMonth + 1
    If currentMonth = 13 Then
        currentMonth = 1
        currentYear = currentYear + 1
    End If
    Call UserForm_Initialize
End Sub

Explanation:

• UserForm_Initialize: This procedure initializes the calendar, displaying the days of the current month. It uses Weekday to determine the first day of the month and then populates buttons with the days of the month.
• CommandButton1_Click: This moves the calendar to the previous month.
• CommandButton2_Click: This moves the calendar to the next month.

Conclusion

This approach shows you how to create a drop-down calendar in Excel using VBA. You can customize the calendar further based on your needs, for example, by adjusting the layout, adding buttons, or allowing the user to select a date from a dropdown list. If you face limitations with controls in your version of Excel, creating a custom calendar using buttons and labels can be a good alternative

Steps to Create a Donut Chart Using VBA in Excel:

Prepare the Data in Excel: Before running the VBA code, ensure that you have data structured in a table format. For example:

• Access the VBA Editor:
  • Open your Excel workbook.
  • Press Alt + F11 to open the VBA editor.
  • Click Insert, then select Module to create a new module.
• Copy the following VBA code into the module:

Sub CreateDonutChart()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range   
    ' Define the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Replace "Sheet1" with your sheet name
    Set dataRange = ws.Range("A1:B5")  ' Replace "A1:B5" with the range of your data
    ' Create the donut chart
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225) ' Set position and size of the chart
    chartObj.Chart.SetSourceData Source:=dataRange  ' Set the data source for the chart
    ' Set the chart type to Donut
    chartObj.Chart.ChartType = xlDoughnut  ' Donut chart type is xlDoughnut
    ' Customize the chart (optional)
    With chartObj.Chart
        ' Add a chart title
        .HasTitle = True
        .ChartTitle.Text = "Category Distribution"       
        ' Change color of each segment
        .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)  ' Red
        .SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)  ' Green
        .SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)  ' Blue
        .SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)  ' Yellow       
        ' Display data labels (value and percentage)
        .ApplyDataLabels ShowValue:=True, ShowPercentage:=True      
        ' Optional: Add a legend
        .HasLegend = True
    End With
End Sub

Detailed Explanation of the Code:

• Variable Declarations:
  • ws: Represents the worksheet where the chart will be created.
  • chartObj: Represents the chart object that we will create.
  • dataRange: Represents the data range to be used for the chart.
• Defining the Worksheet and Data Range:
  • Set ws = ThisWorkbook.Sheets(« Sheet1 »): Defines the worksheet containing your data. Replace « Sheet1 » with your actual worksheet name.
  • Set dataRange = ws.Range(« A1:B5 »): Defines the range of data to be used for the chart. Change « A1:B5 » to the actual range of your data.
• Creating the Chart:
  • Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225): This creates a new chart object in the worksheet and sets its position (left, width, top, height) in pixels.
  • chartObj.Chart.SetSourceData Source:=dataRange: Sets the data source for the chart.
• Setting the Chart Type:
  • chartObj.Chart.ChartType = xlDoughnut: This changes the chart type to a donut chart (xlDoughnut).
• Customizing the Chart (Optional):
  • With chartObj.Chart: Opens a section to customize the chart.
  • .HasTitle = True: Adds a title to the chart.
  • .ChartTitle.Text = « Category Distribution »: Sets the title text of the chart.
  • .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0): Changes the color of the first segment to red (you can set other colors as well).
  • .ApplyDataLabels ShowValue:=True, ShowPercentage:=True: Displays data labels on the chart, showing both the values and percentages.
  • .HasLegend = True: Adds a legend to the chart.

Running the Code:

1. After pasting the code into the module, close the VBA editor.
2. Go back to Excel and press Alt + F8 to open the Macros window.
3. Select CreateDonutChart and click Run.

A donut chart will be created in the specified worksheet with the data you have defined.

Conclusion:

This VBA code creates a donut chart based on the defined data range and allows for various customizations, such as the chart title, segment colors, data labels, and legend. You can adjust the parameters and data range to suit your needs and adapt it for different scenarios.

We will use three ComboBox controls (for day, month, and year) to allow the user to select a date, and a Label to display the selected date.

Steps:

• Create a UserForm with three ComboBoxes and a Label to display the selected date.
• Populate the ComboBoxes with days, months, and years.
• Add code to display the selected date in a Label when the user selects a day, month, and year.

Complete VBA Code:

1. Creating the Form:

First, create a UserForm with:

• 3 ComboBox controls (for Day, Month, and Year).
• 1 Label control to display the selected date.

2. VBA Code in the UserForm Module:

' In the UserForm module
Private Sub UserForm_Initialize()
    ' Populate the Day ComboBox (1 to 31)
    Dim i As Integer
    For i = 1 To 31
        ComboBoxDay.AddItem i
    Next i   
    ' Populate the Month ComboBox (January to December)
    ComboBoxMonth.AddItem "January"
    ComboBoxMonth.AddItem "February"
    ComboBoxMonth.AddItem "March"
    ComboBoxMonth.AddItem "April"
    ComboBoxMonth.AddItem "May"
    ComboBoxMonth.AddItem "June"
    ComboBoxMonth.AddItem "July"
    ComboBoxMonth.AddItem "August"
    ComboBoxMonth.AddItem "September"
    ComboBoxMonth.AddItem "October"
    ComboBoxMonth.AddItem "November"
    ComboBoxMonth.AddItem "December"
    ' Populate the Year ComboBox (for example, from 2000 to 2024)
    Dim year As Integer
    For year = 2000 To 2024
        ComboBoxYear.AddItem year
    Next year
End Sub
Private Sub ComboBoxDay_Change()
    ' Update the displayed date whenever a day is selected
    DisplayDate
End Sub
Private Sub ComboBoxMonth_Change()
    ' Update the displayed date whenever a month is selected
    DisplayDate
End Sub
Private Sub ComboBoxYear_Change()
    ' Update the displayed date whenever a year is selected
    DisplayDate
End Sub
Private Sub DisplayDate()
    ' Check if all ComboBoxes have a selected value
    If ComboBoxDay.ListIndex <> -1 And ComboBoxMonth.ListIndex <> -1 And ComboBoxYear.ListIndex <> -1 Then
        ' Display the selected date in the Label
        LabelDate.Caption = ComboBoxDay.Value & " " & ComboBoxMonth.Value & " " & ComboBoxYear.Value
    End If
End Sub

Explanation:

• UserForm_Initialize:
  • This procedure is triggered when the form is initialized. It fills the three ComboBox controls with the days, months, and years.
  • For the days, it populates the ComboBox with values from 1 to 31.
  • For the months, it populates the ComboBox with the month names from January to December.
  • For the years, it populates the ComboBox with a range of years (from 2000 to 2024 in this case).
• ComboBoxDay_Change, ComboBoxMonth_Change, ComboBoxYear_Change:
  • These procedures are triggered when a user makes a selection in one of the ComboBox controls (day, month, or year).
  • Each time the user selects a day, month, or year, the DisplayDate function is called to update the displayed date in the Label.
• DisplayDate:
  • This procedure checks if all three ComboBoxes have a selected value (by checking the selected index of each ComboBox).
  • If all selections are valid, it displays the selected date in the Label control in the format « Day Month Year ».

User Interface:

• When the user opens the form, they see three ComboBox controls (for day, month, and year) along with a Label that will display the selected date.
• After the user selects a day, month, and year, the selected date is displayed in the Label (for example: « 15 February 2024 » if the user selects day 15, month February, and year 2024).

Example:

If the user selects:

• Day: 15
• Month: February
• Year: 2024

The Label will display:
« 15 February 2024 ».

Additional Suggestions:

• You can add further functionality, such as validating the date (for example, checking that February doesn’t have more than 29 days depending on the year).
• You can add buttons to validate or cancel the selection if needed.

Conclusion:

This code allows you to create a simple date selector using ComboBox controls in an Excel UserForm with VBA. You can customize it further for more specific needs, such as formatting the date or saving it to a cell in the workbook.

This script allows you to select a data range to be used as a source for a drop-down list, and then apply this validation to a specific range of cells.

Objective

• Use an existing data range to create a drop-down list (data validation) in another range.
• Apply data validation to a defined range of cells.

Detailed VBA Code

Sub CreateValidationList()
    Dim SourceRange As Range
    Dim DestRange As Range   
    ' Define the source range (the data to be used for the drop-down list)
    ' Example: A1:A10 contains the values for data validation
    Set SourceRange = Range("A1:A10")   
    ' Define the destination range (the cells where you want to apply the validation)
    ' Example: B1:B10 is the range where you want to apply the data validation
    Set DestRange = Range("B1:B10")   
    ' Delete any existing validations in the destination range
    DestRange.Validation.Delete   
    ' Apply the data validation with the source range
    With DestRange.Validation
        .Delete ' Remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & SourceRange.Address(, , , True)
        .IgnoreBlank = True
        .InCellDropdown = True ' Creates a drop-down list in the cell
        .ShowInput = True
        .ShowError = True
    End With  
    ' Optional: Show a confirmation message
    MsgBox "Data validation created from " & SourceRange.Address, vbInformation
End Sub

Explanation of the Code

• Define the Ranges:
  • SourceRange: This is the range of cells containing the data that will be used for the drop-down list. In this example, it is Range(« A1:A10 »), meaning the cells A1 to A10 will be used as the source.
  • DestRange: This is the range of cells where you want to apply the data validation. In this case, it’s Range(« B1:B10 »), meaning the cells B1 to B10 will have the data validation applied.
• Delete Previous Validations:
  • DestRange.Validation.Delete is used to delete any existing data validation in the destination range. This ensures there are no validation conflicts.
• Apply Data Validation:
  • The block With DestRange.Validation is used to define and apply the data validation to the DestRange cells.
  • .Add is used to set the validation type. Here, we specify that we are using a list (xlValidateList), and the source for the list is from SourceRange. The formula Formula1:= »= » & SourceRange.Address(, , , True) refers to the source range dynamically.
  • .InCellDropdown = True creates the drop-down list in the destination cells.
• Confirmation Message:
  • An optional message box appears to confirm that data validation has been created from the source range. This can be useful for the user.

Customizing the Code

• Change the Source Range: You can modify the Range(« A1:A10 ») to point to the range that contains your desired values for the drop-down list.
• Change the Destination Range: Modify Range(« B1:B10 ») to apply the validation to a different range of cells.
• Add Additional Features: You can extend the script to add custom input or error messages using .InputMessage or .ErrorMessage.

Example Use Case

If you have a list of categories in cells A1 to A10 (like « Fruit », « Vegetable », « Meat », etc.), this code will create a drop-down list in cells B1 to B10 with those values, so users can select a category from the list.

Conclusion

This VBA script allows you to easily create data validation lists in Excel based on a specific data range. It’s fully customizable to suit your needs and can be extended to include additional features such as input or error messages.

The goal is to create a dynamic dropdown in a cell and allow the user to search through the options by typing into the cell.

Step 1: Prepare the Data

We begin by creating a list of items in a specific column (e.g., Column A). The data validation dropdown will be based on this list.

Step 2: Create the Dropdown with Search Feature

The following VBA code adds data validation with a search function. The search will be performed as the user starts typing, and matching items will appear in the dropdown list.

VBA Code:

Sub CreateDropdownWithSearch()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim listRange As Range
    Dim listName As String
    Dim validationFormula As String
    Dim lastRow As Long   
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the range for the list to be used in the dropdown
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set listRange = ws.Range("A1:A" & lastRow)   
    ' Name for the list range
    listName = "DropdownListSearch"   
    ' Create a named range for the list
    ws.Names.Add Name:=listName, RefersTo:=listRange   
    ' Apply data validation to cell B1
    Set rng = ws.Range("B1")
    rng.Validation.Delete ' Delete any existing validation   
    ' Add data validation for the dropdown
    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=" & listName   
    ' Create a search event (Ctrl + L) to filter the list
    Application.OnKey "^l", "FilterList" ' Ctrl+L to trigger search   
End Sub
Sub FilterList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim searchTerm As String
    Dim filteredRange As Range
    Dim cell As Range   
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the data range
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)   
    ' Prompt the user to enter a search term
    searchTerm = InputBox("Enter a search term to filter the list:")
    ' Apply filter based on the search term
    rng.AutoFilter Field:=1, Criteria1:="*" & searchTerm & "*"   
    ' Reset the data validation to show only the filtered results
    Set filteredRange = rng.SpecialCells(xlCellTypeVisible)
    rng.Validation.Delete
    rng.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=" & filteredRange.Address
End Sub

Explanation of the Code:

CreateDropdownWithSearch:

• Define the Worksheet and Data Range:
  • We define the worksheet (ws) and the data range (listRange) that will be used for the dropdown list (Column A in this case).
• Create a Named Range for the List:
  • We create a named range (DropdownListSearch) that refers to the data range.
• Apply Data Validation to the Cell:
  • We apply data validation to cell B1, specifying the named range (DropdownListSearch) as the source for the dropdown list.
• Add a Keyboard Shortcut for Search:
  • We assign a keyboard shortcut (Ctrl+L) using OnKey to trigger the search function when pressed.

FilterList:

• Define the Worksheet and Data Range:
  • We access the worksheet and the data range that contains the items for the dropdown.
• Prompt the User for a Search Term:
  • An InputBox is used to ask the user to enter a search term, which will be used to filter the list.
• Apply the Filter:
  • We apply an AutoFilter to the range using the search term as a filter criterion (it looks for items that contain the search term).
• Reset the Data Validation:
  • After applying the filter, we reset the data validation to show only the filtered results in the dropdown list.

Step 3: Using the Code

• Run the Code:
  • Open the VBA editor (Alt + F11), paste this code into a module, and then run the CreateDropdownWithSearch macro.
• Search in the List:
  • Select cell B1, start typing to show the dropdown, and use the Ctrl+L shortcut to filter the items based on a search term.

Conclusion

This code enables the creation of a dynamic dropdown list in Excel with a search feature using VBA. The user can easily search for an item by filtering the list as they type, making it easier to find and select items from large datasets.

This code will guide you step-by-step in creating a simple form where the user can enter data. We will also include validation to ensure that the entered data is correct.

Steps to Create the Form:

• Access the VBA Editor:
  Open Excel, then press Alt + F11 to open the VBA editor.
• Create a Form (UserForm):
  In the VBA editor, go to the Insert menu and select UserForm to add a new form.
• Add Controls to the Form:
  You can add several controls (TextBox, ComboBox, Label, CommandButton) using the toolbox.

For this example, we will use:

• • 3 TextBox: to enter the name, age, and city.
  • 1 ComboBox: to select gender (Male, Female).
  • 2 CommandButton: one button to save the data and another to cancel.

Add VBA Code:
After creating the form, here is the code you can use for validation and saving the data.

Detailed VBA Code for the Data Entry Form with Validation

1. Code for the Form (UserForm)

Here’s the code for the form, with input fields and validation.

Private Sub UserForm_Initialize()
    ' Fill the ComboBox with default values
    ComboBoxSex.AddItem "Male"
    ComboBoxSex.AddItem "Female"
End Sub
Private Sub CommandButtonSave_Click()
    ' Validation of fields
    If TextBoxName.Value = "" Then
        MsgBox "Name is required!", vbExclamation, "Error"
        TextBoxName.SetFocus
        Exit Sub
    End If   
    If TextBoxAge.Value = "" Or Not IsNumeric(TextBoxAge.Value) Then
        MsgBox "Please enter a valid age!", vbExclamation, "Error"
        TextBoxAge.SetFocus
        Exit Sub
    End If   
    If ComboBoxSex.Value = "" Then
        MsgBox "Please select a gender!", vbExclamation, "Error"
        ComboBoxSex.SetFocus
        Exit Sub
    End If   
    If TextBoxCity.Value = "" Then
        MsgBox "City is required!", vbExclamation, "Error"
        TextBoxCity.SetFocus
        Exit Sub
    End If   
    ' Save data to the Excel sheet
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row + 1   
    Sheets("Sheet1").Cells(LastRow, 1).Value = TextBoxName.Value
    Sheets("Sheet1").Cells(LastRow, 2).Value = TextBoxAge.Value
    Sheets("Sheet1").Cells(LastRow, 3).Value = ComboBoxSex.Value
    Sheets("Sheet1").Cells(LastRow, 4).Value = TextBoxCity.Value  
    MsgBox "Data saved successfully!", vbInformation, "Success"  
    ' Reset the fields
    TextBoxName.Value = ""
    TextBoxAge.Value = ""
    ComboBoxSex.Value = ""
    TextBoxCity.Value = ""
End Sub
Private Sub CommandButtonCancel_Click()
    ' Close the form without saving
    Unload Me
End Sub

2. Code Explanation

• UserForm_Initialize:
  This procedure runs when the form is initialized. It populates the ComboBoxSex with two options (« Male » and « Female »).
• CommandButtonSave_Click:
  This procedure triggers when the user clicks the « Save » button.
  • It starts by checking if all required fields are filled correctly. If a field is empty or invalid, an error message is displayed, and the user is asked to correct it.
  • Then, the data is saved into the Excel sheet. The information is inserted into the first empty row on Sheet1.
  • After saving, a confirmation message appears, and the form fields are reset for new data entry.
• CommandButtonCancel_Click:
  This procedure closes the form without saving the data when the user clicks the « Cancel » button.

3. Example Excel Sheet Structure

Before testing the form, make sure that Sheet1 in your workbook contains the following columns:

• Column A: Name
• Column B: Age
• Column C: Gender
• Column D: City

4. Launch the Form

To open the form from an Excel sheet, you can add a command button and link this button to the following macro in a module:

Sub OpenForm()
    UserForm1.Show
End Sub

Then, assign this macro to the button in your Excel sheet.

5. Summary of Key Points

• Data Validation: The form checks if all fields are correctly filled before saving the data.
• Reset Fields: After each save, the form is reset to allow for new data entry.
• Interaction with Excel Sheet: The entered data is saved into the Excel sheet, starting from the first empty row.