VBA code for excel form

phillip87

Board Regular
Joined
Jan 28, 2019
Messages
69
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:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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
 
Upvote 0
Re: help with a simple VBA code for excel form

Try using:

Code:
CDate(TextBox1.Text)

rather than just Textbox1.Text
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top