Excel: VBA Project Versatile Data Form, Use Anywhere On Sheet, Code In Video & Description

A Microsoft Excel VBA project of a versatile data entry form with percentage and basic mathematical functions, the form can enter data to a table anywhere on a sheet using refedit box, default is A1 and to any sheet in the workbook, selected from combobox list, default sheet is the one the data form is opened on.
Can't Fit All Code Below, Watch Video.

Procedures Code for Userform_initialize:
Public Sub headerstolabels()
l = 1
n = 0
For q = 1 To 20
Me("Label" & l).Caption = Range(Me.RefEdit1.Value).Offset(0, n).Value
n = n + 1
l = l + 1
Next q
End Sub

Public Sub boxarrays()
ComboBox1.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")
ComboBox2.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")
ComboBox3.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")
ComboBox5.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")
ComboBox4.List = Array("* Multiply", "/ Divide", "+ Add", "- Subtract")
ComboBox6.List = Array("8", "10", "12", "14")
End Sub

Public Sub sheetbox()
Dim ws As Worksheet
With Me.ComboBox7
For Each ws In Sheets
.AddItem ws.Name
Next ws
End With
End Sub

Procedure Code For Commandbutton1:
Public Sub notemptydown()
If Not IsEmpty(Range(Me.RefEdit1.Value).Offset(1, 0)) Then
Selection.End(xlDown).Select
End If
End Sub

Commandbutton1(Add Data) Code:
Note: notemptydown is a procedure, code is above.
Private Sub CommandButton1_Click()
If Me.ComboBox7.Value = "" Then
Set ws = ActiveSheet
Else
ws = Me.ComboBox7.Value
Worksheets(ws).Activate
End If
Range(Me.RefEdit1).Activate
notemptydown
ActiveCell.Offset(1, 0).Activate
Dim w As Integer, v As Integer, t As Integer
w = 19
v = 20
For t = 1 To 20
If ActiveCell.Value = "" Then ActiveCell.Offset(0, w).Value = Me("TextBox" & v).Value
v = v - 1
w = w - 1
Next t
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Offset(-1, 0).Value = "" Then ActiveCell.Offset(-1, 0).Activate
Dim a As Integer
For a = 1 To 20
Me("textbox" & a).BackColor = &H80000005
Me("textbox" & a).Value = ""
Next a
Me.MultiPage1.Value = 0
Me.TextBox1.SetFocus
End Sub

Commandbutton2(Close Form) Code:
Private Sub CommandButton2_Click()
Unload Me
End Sub

Commandbutton4(Clear Boxes) Code:
Private Sub CommandButton4_Click()
For a = 1 To 20
Me("textbox" & a).BackColor = &H80000005
Me("textbox" & a).Value = ""
Next a
Me.MultiPage1.Value = 0
Me.TextBox1.SetFocus
End Sub

Procedures For Commandbutton5:
Procedure1(Same as userform above):
Public Sub headerstolabels()

Procedure2:
Public Sub enabletext()
Dim f As Integer
For f = 1 To 20
If Me("Label" & f).Caption = "" Then
Me("TextBox" & f).Enabled = False
Else
Me("TextBox" & f).Enabled = True
End If
Next f
End Sub

Commandbutton6(Edit) Code:
Private Sub CommandButton6_Click()
If Me.ComboBox7.Value = "" Then
Set ws = ActiveSheet
Else
ws = Me.ComboBox7.Value
Worksheets(ws).Activate
End If
Dim w As Integer, v As Integer, t As Integer
w = 19
v = 20
For t = 1 To 20
ActiveCell.Offset(0, w).Value = Me("TextBox" & v).Value
v = v - 1
w = w - 1
Next t
End Sub

Commandbutton7(Copy) Code:
Private Sub CommandButton7_Click()
If Me.ComboBox7.Value = "" Then
Set ws = ActiveSheet
Else
ws = Me.ComboBox7.Value
Worksheets(ws).Activate
End If
Range(Me.RefEdit2).Activate
Dim w As Integer, v As Integer, t As Integer
w = 19
v = 20
For t = 1 To 20
ActiveCell.Offset(0, w).Value = Me("TextBox" & v).Value
v = v - 1
w = w - 1
Next t
End Sub

Commandbutton3(Calculations) Code:
Private Sub CommandButton3_Click()
If Me.CheckBox2.Value = True Then y = Me("TextBox" & Me.ComboBox5.Value).Value * (Me.Label26.Caption / 100)
If Me.CheckBox2.Value = True Then Me("TextBox" & Me.ComboBox2.Value).Value = y
If CheckBox6.Value = True And ComboBox4.Value = "* Multiply" Then
x = Me("TextBox" & Me.ComboBox5.Value).Value * Me.CalcBox1.Value
End If
If CheckBox6.Value = True And ComboBox4.Value = "/ Divide" Then
x = Me("TextBox" & Me.ComboBox5.Value).Value / Me.CalcBox1.Value
End If
If CheckBox6.Value = True And ComboBox4.Value = "- Subtract" Then
x = Me("TextBox" & Me.ComboBox5.Value).Value - Me.CalcBox1.Value
End If
If CheckBox6.Value = True And ComboBox4.Value = "+ Add" Then
x = Val(Me("TextBox" & Me.ComboBox5.Value).Value) + Val(Me.CalcBox1.Value)
End If
If CheckBox6.Value = True Then Me("TextBox" & Me.ComboBox3.Value).Value = x
End Sub

Spinbutton Code:
Private Sub SpinButton1_Change()
Me.Label26.Caption = 100 * (Me.SpinButton1.Value / 100 + Me.SpinButton2.Value / 1000 + Me.SpinButton3.Value / 10000)
End Sub

Combobox6(Text Size):
Private Sub ComboBox6_Change()
On Error Resume Next
Dim m As Integer
For m = 1 To 36
Me("Label" & m).Font.Size = Me.ComboBox6.Value
Next m

00:00 The Data Form
05:20 Enter Data To Different Sheet
08:42 Code

#excel #microsoft #dataentry #vba #code