PDA

View Full Version : Dynamic Userform for data entry



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.

mdmackillop
04-12-2017, 06:48 AM
Can you provide a Workbook with sample data highlighting the fields you need as textboxes/comboboxes, where the data comes from and how this would be identified in a dynamic form. Do you have consistent Headers? Provide as much info as you can as to how the form should work.

marissaliana
04-12-2017, 07:42 AM
Hi,
I attach a simple sample of the workbook I have to handle with. Of course I have added much less columns than the original workbook. The fields with the orange background should become combo boxes and the others textfields. In the second sheet I have created a list of combo boxes data.

JKwan
04-12-2017, 09:25 AM
Why don't you give this a try?
http://spreadsheetpage.com/index.php/dataform/home

mdmackillop
04-12-2017, 09:30 AM
Here's some code to create comboboxes - that's the easy bit. As these are dynamic, you'll neeed additional code to handle the events; see this article (http://yoursumbuddy.com/userform-event-class-multiple-control-types/)

Private Sub UserForm_Activate()
Call AddCombos
End Sub


Sub AddCombos()
Dim arr()
Dim txtBox As MSForms.TextBox
Dim Combo As MSForms.ComboBox
Dim i As Long

Const comboHeight As Long = 18
Const comboWidth As Long = 100
Const cGap As Long = 4

arr = Array("No1", "No4", "Subj2", "New1")
For Each a In arr
Set Combo = Me.Controls.Add("Forms.combobox.1", "cmb" & a, True)
With Combo
.List = Tabelle2.Rows(1).Find(a).EntireColumn.SpecialCells(xlCellTypeConstants).Val ue
.Left = cGap
.Top = cGap + i * (cGap + comboHeight)
.Height = comboHeight
.Width = comboWidth
.ListIndex = 0
i = i + 1
End With
Next a
End Sub

marissaliana
04-13-2017, 02:26 AM
Here's some code to create comboboxes - that's the easy bit. As these are dynamic, you'll neeed additional code to handle the events;

Private Sub UserForm_Activate()
Call AddCombos
End Sub


Sub AddCombos()
Dim arr()
Dim txtBox As MSForms.TextBox
Dim Combo As MSForms.ComboBox
Dim i As Long

Const comboHeight As Long = 18
Const comboWidth As Long = 100
Const cGap As Long = 4

arr = Array("No1", "No4", "Subj2", "New1")
For Each a In arr
Set Combo = Me.Controls.Add("Forms.combobox.1", "cmb" & a, True)
With Combo
.List = Tabelle2.Rows(1).Find(a).EntireColumn.SpecialCells(xlCellTypeConstants).Val ue
.Left = cGap
.Top = cGap + i * (cGap + comboHeight)
.Height = comboHeight
.Width = comboWidth
.ListIndex = 0
i = i + 1
End With
Next a
End Sub




Hi, I have used the code and it works fine. The userform appears with the four comboboxes with values. But now I have stuch to how am I continuing for appear the text fields. Can you help me with these?

mdmackillop
04-13-2017, 11:41 AM
You already have code for textboxes. I cannot see from what you have described how these relate to combos or how data is to be entered/saved.

marissaliana
04-19-2017, 12:21 AM
Hello again,

I manage to create a code that add textboxes and combos. Now I am stuck on how these dynamic boxes will insert the data in the excel. For example from the code that you have written is there anyway to insert the data in the excel?

mdmackillop
04-19-2017, 04:42 AM
I referred you to this article (http://yoursumbuddy.com/userform-event-class-multiple-control-types/) above. I've not worked much with classes and probly not the best to assist. This appears to be a sizeable project which needs to be properly planned.