Dynamic Userform for data entry

[marissaliana]

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]

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]

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]

Why don't you give this a try?
http://spreadsheetpage.com/index.php/dataform/home

[mdmackillop]

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

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).Value
            .Left = cGap
            .Top = cGap + i * (cGap + comboHeight)
            .Height = comboHeight
            .Width = comboWidth
            .ListIndex = 0
            i = i + 1
            End With
        Next a
End Sub

[marissaliana]

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).Value
            .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]

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]

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]

I referred you to this article 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.