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

 

[phillip87]

Re: help with a simple VBA code for excel form

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.

 

[dmt32]

Re: help with a simple VBA code for excel form

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

With Me.Controls("TextBox" & i)

with this line

 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

Dim arr(1 To 13) As Variant

Dave

 

[phillip87]

Re: help with a simple VBA code for excel form

Thanks heaps, I will give it a try.

 

[phillip87]

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

 

[dmt32]

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

 

[phillip87]

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.

 

[dmt32]

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.

I can see what your code does, did you delete it & apply the format in the range as I suggested?

Dave

 

[phillip87]

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

 

[dmt32]

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

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

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

 

[phillip87]

Re: help with a simple VBA code for excel form

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