Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA code for excel form

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code for excel form

    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 by phillip87; Aug 7th, 2019 at 05:40 PM.

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with a simple VBA code for excel form

    thanks for this, exactly what i needed.

  4. #4
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: help with a simple VBA code for excel form

    Try using:

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

  6. #6
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,039
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default 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

  8. #8
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,039
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: help with a simple VBA code for excel form

    Quote Originally Posted by phillip87 View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •