Use sheets name for UserForm Drop Down.

Question

Dear all,

I would like to make an Excel VBA UserForm containing a DropDown menu that contains the names of all Sheet excluding a few sheets like "Master", "Results", and "Help", at the same time if i select "Sheet1" and enter few data to another input fields then all data shall be append in the same sheet which I had selected from the drop-down. Attached is an image of the user form for your reference.

.

Answer 1

Hi SR. SudhirRaj, I'm Femi and I'd be happy to help you with your question. Kindly follow the steps below to do that: Open the Visual Basic for Applications (VBA) editor by pressing Alt+F11 in Excel. Insert a new UserForm by right-clicking on your workbook's name in the Project Explorer window, selecting "Insert," and then choosing "UserForm." Design your UserForm with the necessary input fields and a DropDown menu. For this example, let's assume you have a TextBox control named "txtData" for entering data and a DropDown control named "ddlSheets" for selecting the sheet. In the UserForm module, add the following code: Private Sub UserForm_Initialize() ' Initialize the UserForm ' Populate the DropDown menu with sheet names excluding specific sheets

Dim ws As Worksheet Dim excludedSheets As Variant excludedSheets = Array("Master", "Results", "Help")

' Clear previous items from the DropDown menu ddlSheets.Clear

' Add sheet names to the DropDown menu For Each ws In ThisWorkbook.Worksheets If Not IsInArray(ws. Name, excludedSheets) Then ddlSheets.AddItem ws. Name End If Next ws

' Set the default selection for the DropDown menu ddlSheets.Value = ddlSheets.List(0) End Sub

Private Sub cmdAppendData_Click() ' Append data to the selected sheet

Dim selectedSheet As Worksheet Dim rowData As Range

' Get the selected sheet Set selectedSheet = ThisWorkbook.Worksheets(ddlSheets.Value)

' Find the first empty row in the selected sheet Set rowData = selectedSheet.Cells(selectedSheet.Rows.Count, 1). End(xlUp). Offset(1)

' Append the data to the selected sheet rowData.Value = txtData.Value

' Clear the input field txtData.Value = ""

' Close the UserForm Unload Me End Sub

Private Function IsInArray(val As Variant, arr As Variant) As Boolean ' Check if a value is in the array

Dim element As Variant On Error GoTo ErrorHandler

For Each element In arr If element = val Then IsInArray = True Exit Function End If Next element

ErrorHandler: On Error GoTo 0 IsInArray = False End Function

Close the VBA editor and return to the Excel worksheet. Insert a button or a macro that opens the UserForm. You can add a button to the worksheet and assign it the following macro: Sub ShowMyUserForm() UserForm1.Show End Sub

Now, when you click the button, the UserForm will appear with a DropDown menu populated with the names of all sheets in the workbook (excluding "Master", "Results", and "Help"). After selecting a sheet from the DropDown menu and entering data in the TextBox control, clicking the "Append Data" button will append the entered data to the selected sheet in the first empty row. Kindly ensure you save your workbook as a macro-enabled file format (e.g., .xlsm) to preserve the VBA code.

Hope this helps!

Best Regards, Femi

Answer 2

Dear Femi,

Thanks for your prompt response, But I am unable to add the data from other fields like age, address, etc, whenever I am trying to append the data it only allows me to enter the last data, here please see the part of the data which was modified by me for myself.

Your suggestion is working properly for single entry, but i want to use multiple text fileds in my form.

' Append the data to the selected sheet

rowData.Value = txtName.Value 

rowData.Value = txtAge.Value 

rowData.Value = txtAddress.Value 

' Clear the input field

txtName.Value = "" 

txtAge.Value = "" 

txtAddress.Value = ""

Thanks for your help..