VBA code for excel form

[phillip87]

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.​

 

[steve the fish]

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

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]

Re: help with a simple VBA code for excel form

thanks for this, exactly what i needed.

 

[phillip87]

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]

Re: help with a simple VBA code for excel form

Try using:

CDate(TextBox1.Text)

rather than just Textbox1.Text

 

[phillip87]

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]

Re: help with a simple VBA code for excel form

Hi,
see if this update to your code helps

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]

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]

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]

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