VBA Code:
Sub CreateDataEntryForm()
Dim ws As Worksheet
Dim rngForm As Range
Dim frm As UserForm
' Set the worksheet where you want to create the form
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the desired worksheet name
' Define the range for the form fields
Set rngForm = ws.Range("A1:B5") ' Replace "A1:B5" with the range for the form fields
' Create a new UserForm
Set frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm).Designer
With frm
' Set the UserForm properties
.Caption = "Data Entry Form"
.Width = 300
.Height = 200
' Add labels and text boxes for each form field
For Each cell In rngForm
With .Controls.Add("Forms.Label.1")
.Caption = cell.Value
.Left = 10
.Top = (cell.Row - rngForm.Row) * 30 + 10
.Width = 100
End With
With .Controls.Add("Forms.TextBox.1")
.Name = "txt" & cell.Row
.Left = 120
.Top = (cell.Row - rngForm.Row) * 30 + 10
.Width = 150
End With
Next cell
' Add a submit button
With .Controls.Add("Forms.CommandButton.1")
.Caption = "Submit"
.Left = 120
.Top = rngForm.Rows.Count * 30 + 30
.Width = 70
.OnClick = "SubmitData"
End With
End With
' Show the form
Load frm
frm.Show
' Clean up
ThisWorkbook.VBProject.VBComponents.Remove frm
End Sub
Sub SubmitData()
Dim ws As Worksheet
Dim frm As Object
Dim cell As Range
' Set the worksheet where the data will be entered
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the desired worksheet name
' Set the form object
Set frm = UserForms(0)
' Enter the form data into the worksheet
For Each cell In ws.Range("A1:B5")
If cell.Value <> "" Then
cell.Offset(0, 1).Value = frm.Controls("txt" & cell.Row).Text
End If
Next cell
' Close the form
Unload frm
End Sub
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
