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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

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
 
Upvote 0
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
 
Upvote 0
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.

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

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
 
Upvote 0
Re: help with a simple VBA code for excel form

It will see what I can upload for you, appreciate the help thanks.
 
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