VBA code for excel form

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
I have made a data form which I would like to always show on "sheet1" and add new lines of data to "sheet2" horizontally so I can then work with the data without having people mess with it however I keep getting debug errors.

I have tried looking online however everything i find is too complicated and i am too new to VBA to debug them.

can someone help me out with the VBA code on this, new to the VBA so will not bother attaching what I have, just need something simple to work the below.

my form has the following.

date (text box)
department (combo box)
file numbers(text box)
box numbers(text box)
add(command box)
new(command box)

Thanks.​
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,712
Office Version
365
Platform
Windows
Re: help with a simple VBA code for excel form

Thats a little bit vague... you can add the textbox text, for example, to a worksheet by using

Code:
Private Sub CommandButton1_Click()

With Sheets("Sheet2")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & lr).Value = TextBox1.Text
End With

End Sub
Similar with the rest. But you need to do some research into userforms.
 

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
Re: help with a simple VBA code for excel form

thanks for this, exactly what i needed.
 

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
Re: help with a simple VBA code for excel form

my date texbox is switching the day and month around, how do i change this?

.i.e. 01/02/2019 becomes 02/01/2019
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,712
Office Version
365
Platform
Windows
Re: help with a simple VBA code for excel form

Try using:

Code:
CDate(TextBox1.Text)
rather than just Textbox1.Text
 

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
Re: help with a simple VBA code for excel form

I have the below code which is working perfectly however I cannot seem to attach a clear form code to command button 3, I have tried 3-4 variations with no luck.

Private Sub CommandButton1_Click()
With Sheets("KPI DATA")
lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & lr).Value = CDate(TextBox1.Text)
lr = .Range("B" & .Rows.Count).End(xlUp).Row + 1
.Range("B" & lr).Value = TextBox2.Text
lr = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("C" & lr).Value = TextBox3.Text
lr = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("D" & lr).Value = TextBox4.Text
lr = .Range("E" & .Rows.Count).End(xlUp).Row + 1
.Range("E" & lr).Value = TextBox5.Text
lr = .Range("F" & .Rows.Count).End(xlUp).Row + 1
.Range("F" & lr).Value = TextBox6.Text
lr = .Range("G" & .Rows.Count).End(xlUp).Row + 1
.Range("G" & lr).Value = TextBox7.Text
lr = .Range("H" & .Rows.Count).End(xlUp).Row + 1
.Range("H" & lr).Value = TextBox8.Text
lr = .Range("I" & .Rows.Count).End(xlUp).Row + 1
.Range("I" & lr).Value = TextBox9.Text
lr = .Range("J" & .Rows.Count).End(xlUp).Row + 1
.Range("J" & lr).Value = TextBox10.Text
lr = .Range("K" & .Rows.Count).End(xlUp).Row + 1
.Range("K" & lr).Value = TextBox11.Text
lr = .Range("L" & .Rows.Count).End(xlUp).Row + 1
.Range("L" & lr).Value = TextBox12.Text
lr = .Range("M" & .Rows.Count).End(xlUp).Row + 1
.Range("M" & lr).Value = TextBox13.Text
lr = .Range("N" & .Rows.Count).End(xlUp).Row + 1
.Range("N" & lr).Value = TextBox14.Text
lr = .Range("O" & .Rows.Count).End(xlUp).Row + 1
.Range("O" & lr).Value = TextBox15.Text
lr = .Range("P" & .Rows.Count).End(xlUp).Row + 1
.Range("P" & lr).Value = TextBox16.Text
lr = .Range("Q" & .Rows.Count).End(xlUp).Row + 1
.Range("Q" & lr).Value = TextBox17.Text
lr = .Range("R" & .Rows.Count).End(xlUp).Row + 1
.Range("R" & lr).Value = TextBox18.Text
lr = .Range("S" & .Rows.Count).End(xlUp).Row + 1
.Range("S" & lr).Value = TextBox19.Text
lr = .Range("T" & .Rows.Count).End(xlUp).Row + 1
.Range("T" & lr).Value = TextBox20.Text


End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub


Private Sub CommandButton3_Click()
Dim ctl As Controls
For Each ctl In Me.Controls
If TypeName(ctl) = "textbox" Then
ctl.Value = ""
End If
Next ctl
End Sub


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)


'Dim dDate As Date
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
dDate = TextBox1.Value


End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
Re: help with a simple VBA code for excel form

Hi,
see if this update to your code helps

Code:
Private Sub CommandButton1_Click()
    Dim wsKPI As Worksheet
    Dim lr As Long
    Dim i As Integer
    
    Dim arr(1 To 20) As Variant
    
    Set wsKPI = ThisWorkbook.Worksheets("KPI DATA")
    
    For i = 1 To UBound(arr)
        With Me.Controls("TextBox" & i)
            If IsDate(.Value) Then
                arr(i) = DateValue(.Value)
            ElseIf IsNumeric(.Value) Then
                arr(i) = Val(.Value)
            Else
                arr(i) = (.Value)
            End If
'clear textboxes
            .Value = ""
        End With
        Next i
        
        With wsKPI
            lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
'add record to worksheet
            .Cells(lr, 1).Resize(, UBound(arr)).Value = arr
        End With
        
        MsgBox "Record Submitted", 48, "Record Submitted"
End Sub

Code will automatically clear the form after record submitted

Dave
 

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
Re: help with a simple VBA code for excel form

thanks for that Dave.

i have run this and its working great thank you, i have another sheet however it has a combobox. how do i add this into the mix?
 

phillip87

New Member
Joined
Jan 28, 2019
Messages
33
Re: help with a simple VBA code for excel form

The other sheet has 12 text boxes and 1 combo box, with the above code the sheet records the text boxes but leaves out the combo box.

Appreciate the help on this.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
Re: help with a simple VBA code for excel form

The other sheet has 12 text boxes and 1 combo box, with the above code the sheet records the text boxes but leaves out the combo box.

Appreciate the help on this.
Hi,
what is your combox name? Counting 13 controls, where in the sequence of does it need to be placed?

Dave
 

Forum statistics

Threads
1,077,617
Messages
5,335,261
Members
399,009
Latest member
twcaddell

Some videos you may like

This Week's Hot Topics

Top