Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: VBA code for excel form

  1. #11
    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

    Quote Originally Posted by dmt32 View Post
    Hi,
    what is your combox name? Counting 13 controls, where in the sequence of does it need to be placed?

    Dave
    Combobox1 and located in column B on the data sheet.

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

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

    Quote Originally Posted by phillip87 View Post
    Combobox1 and located in column B on the data sheet.
    Hi bit short of time as on granddad duties today - its a bit crude but give this tweak a try and see if works ok for you


    replace this line

    Code:
    With Me.Controls("TextBox" & i)

    with this line

    Code:
     With Me.Controls(IIf(i = 2, "Combobox", "TextBox") & IIf(i = 2, 1, IIf(i > 2, i - 1, i)))

    If tweak works, Textbox1 value should go to Col A , Combobox to Col B & TextBoxes 2 to 12 Cols C to M.

    This assumes that you have sized the array correctly

    Code:
    Dim arr(1 To 13) As Variant
    Dave

  3. #13
    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 heaps, I will give it a try.

  4. #14
    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 now added in another textbox which is to have revenue entered into it. However when the form submits the data it shows as a zero and does not enter the data as shown on the form.

    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("PENROSE KPI DATA")

    For i = 1 To UBound(arr)
    With Me.Controls(IIf(i = 2, "Combobox", "TextBox") & IIf(i = 2, 1, IIf(i > 2, i - 1, 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


    Private Sub TextBox19_change()
    TextBox19 = Format(TextBox19, "$#,##0")
    End Sub

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

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

    Quote Originally Posted by phillip87 View Post
    I have now added in another textbox which is to have revenue entered into it. However when the form submits the data it shows as a zero and does not enter the data as shown on the form.
    Code:
    Private Sub TextBox19_change()
        TextBox19 = Format(TextBox19, "$#,##0")
    End Sub
    Delete the above code & see if data submits correctly & if so, format your data in the range not in your textbox.

    Dave

  6. #16
    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

    Hey, I put in the above code after I saw it only entered a zero into the rage but the above only changes what's shown on the form but still entering a zero into the range.

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

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

    Quote Originally Posted by phillip87 View Post
    Hey, I put in the above code after I saw it only entered a zero into the rage but the above only changes what's shown on the form but still entering a zero into the range.
    I can see what your code does, did you delete it & apply the format in the range as I suggested?

    Dave

  8. #18
    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

    Yes that was my first thing to do, before I tried this code i saw it entered a zero, i then formatted the range of cells and tried again, still zero. I then tried this code but again only shows a zero, not even with a $.

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

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

    Quote Originally Posted by phillip87 View Post
    Yes that was my first thing to do, before I tried this code i saw it entered a zero, i then formatted the range of cells and tried again, still zero. I then tried this code but again only shows a zero, not even with a $.
    This section of the code coerces your textbox text values in to the appropriate data types (date, numeric) but the functions have their limitations & do not always perform as intended

    Code:
    If IsDate(.Value) Then
                    arr(i) = DateValue(.Value)
                ElseIf IsNumeric(.Value) Then
                    arr(i) = Val(.Value)
                Else
                    arr(i) = (.Value)
                End If
    Also, the use of Val function stops reading the string at the first character that it does not recognize as part of a number so if you have a currency symbol in your textbox it would return 0.

    Are you able to place copy of your workbook with sample data in a dropbox & post a link to it here?

    I am out most of day but will, unless another can step in & solve for you, have a look when I return.


    Dave

  10. #20
    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

    It will see what I can upload for you, appreciate the help thanks.

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
  •