marissaliana
04-12-2017, 04:46 AM
Hello,
in my work we have a really big database in excel which keeps the results of some tests. We have about 300 columns with the details of tests and each row it' s a new test. Now my boss want me to create a tools/application for entering the test results in the excel file more easy than inform the main list by hand. So I was thinking about creating a dynamic form that creates text fields and combo boxes from the main list and then add the data of these boxes in the main list.
I have found and used this code
Private Sub UserForm_Activate() Dim txtBox As MSForms.TextBox
Dim rngFields As Excel.Range
Dim field As Excel.Range
Dim lngNextTop As Long
Dim lngTitleBarHeight As Long
Const cTextBoxHeight As Long = 18
Const cTextBoxWidth As Long = 100
Const cGap As Long = 4
Set rngFields = Excel.Range("tblFields")
lngTitleBarHeight = Me.Height - Me.InsideHeight
lngNextTop = cGap
For Each field In rngFields
If field.Value <> "" Then
Set txtBox = Me.Controls.Add("Forms.TextBox.1", "txt"
& field.Value, True)
txtBox.Text = field.Value
txtBox.Left = cGap
txtBox.Top = lngNextTop
txtBox.Height = cTextBoxHeight
txtBox.Width = cTextBoxWidth
lngNextTop = lngNextTop + cTextBoxHeight + cGap
Me.Height = lngNextTop + lngTitleBarHeight
End If
Next field
Set txtBox = Nothing
End Sub
The code worked and now I am able to create a dynamic form from the Name Ranged. Now I have two problems
1) I need some of the fields in the userform to be textfields and some other to be combo boxes. How is it possible to do this?
2) I want to add a button that adds the data in the next empty row in the worksheet. Usually this is something easy, but now with the dynamic fields I have no idea, how am I suppose to do this.
in my work we have a really big database in excel which keeps the results of some tests. We have about 300 columns with the details of tests and each row it' s a new test. Now my boss want me to create a tools/application for entering the test results in the excel file more easy than inform the main list by hand. So I was thinking about creating a dynamic form that creates text fields and combo boxes from the main list and then add the data of these boxes in the main list.
I have found and used this code
Private Sub UserForm_Activate() Dim txtBox As MSForms.TextBox
Dim rngFields As Excel.Range
Dim field As Excel.Range
Dim lngNextTop As Long
Dim lngTitleBarHeight As Long
Const cTextBoxHeight As Long = 18
Const cTextBoxWidth As Long = 100
Const cGap As Long = 4
Set rngFields = Excel.Range("tblFields")
lngTitleBarHeight = Me.Height - Me.InsideHeight
lngNextTop = cGap
For Each field In rngFields
If field.Value <> "" Then
Set txtBox = Me.Controls.Add("Forms.TextBox.1", "txt"
& field.Value, True)
txtBox.Text = field.Value
txtBox.Left = cGap
txtBox.Top = lngNextTop
txtBox.Height = cTextBoxHeight
txtBox.Width = cTextBoxWidth
lngNextTop = lngNextTop + cTextBoxHeight + cGap
Me.Height = lngNextTop + lngTitleBarHeight
End If
Next field
Set txtBox = Nothing
End Sub
The code worked and now I am able to create a dynamic form from the Name Ranged. Now I have two problems
1) I need some of the fields in the userform to be textfields and some other to be combo boxes. How is it possible to do this?
2) I want to add a button that adds the data in the next empty row in the worksheet. Usually this is something easy, but now with the dynamic fields I have no idea, how am I suppose to do this.