Userform Dynamic TextBoxes/Labels/Checkboxes and SQL Insert

[James123456]

Hi all,

I have a spreadsheet and the purpose is to create a dynamic userform, show all values from Columns N (ID) and O (Name) as lebels and column P (Percentage) as editable textboxes from Sheet1 and create checkboxes at the end of each row on the userform.

The user then changes these percentages on the userform, as sson as the textbox is changed the checkbox become chekced. When clicking OK button if the percentage value has been changed then copy the new value to column Q on sheet1 and create a sql insert statement to insert the changed percentage with the ID into a table but if the textbox percentage has not been changed then only insert the ID.

So far I have manged to create the dynamic userform with all the labels, textboxes and checkboxes that displays the values from columsn N, O and P (With some online search and help). The code is as below:

Private Sub UserForm_Activate() 
    Dim lbl 
    Dim chkbox As MSForms.CheckBox 
    Dim txtbox 
    Dim Control As Control 
    Dim fields As Range 
    Dim field As Range 
    Const top1 = 32 
    Dim counter 
    counter = 1 
    Dim ws As Worksheet 
    Application.EnableEvents = False 
    Set ws = Worksheets("Sheet1") 
    Set fields = Worksheets("Sheet1").Range("N2", Worksheets("Sheet1").Range("N1048576").End(xlUp)).SpecialCells(xlCellTypeVisible) 
    For Each Control In Me.Controls 
        If Not Control.Top = 6 Then 
            On Error Resume Next 
            Me.Controls.Remove (Control.name) 
            On Error Goto 0 
        End If 
         
    Next Control 
    For Each field In fields 
        If Not field.Value = "ID" Then 
            Set lbl = Me.Controls.Add("Forms.Label.1", "N" & (counter + 1)) 
            With lbl 
                .Caption = field.Value 
                .Left = 6 
                .Top = top1 * counter 
                .Width = 94 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
                .BorderStyle = fmBorderStyleSingle 
            End With 
             
            Set lbl = Me.Controls.Add("Forms.Label.1", "O" & (counter + 1)) 
            With lbl 
                .Caption = field.Value 
                .Left = 96 
                .Top = top1 * counter 
                .Width = 115 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
                .BorderStyle = fmBorderStyleSingle 
            End With 
             
            Set txtbox = Me.Controls.Add("Forms.TextBox.1", "P" & (counter + 1)) 
            With txtbox 
                .Text = field.Offset(0, 2).Value 
                .Left = 210 
                .Top = top1 * counter 
                .Width = 78 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
            End With 
            Set chkbox = Me.Controls.Add("Forms.CheckBox.1", "Q" & (counter + 1)) 
            With chkbox 
                .Caption = field.Offset(0, 3).Value 
                .Left = 310 
                .Top = top1 * counter 
                .Width = 84 
                .Height = 24 
                .Tag = "CheckBox" 
                .Font.Bold = True 
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
            End With 
             
            counter = counter + 1 
             
        End If 
    Next field 
     
    If counter > 10 Then 
        Me.Height = top1 * 10 + top1 
        Me.ScrollBars = fmScrollBarsVertical 
        Me.ScrollHeight = top1 * counter + top1 
    Else 
        Me.Height = top1 * counter + top1 
        Me.ScrollBars = fmScrollBarsNone 
    End If 
     
    Application.EnableEvents = True 
    making = False 
End Sub

I have also managed to get the changed Percentage and copy it on the spreadsheet when OK button is clicked with the code below:

Private Sub Button1_Click() 
    Dim chkbox As Control 
    Dim name 
    Dim counter 
    Dim sheet As Worksheet 
    For Each chkbox In Me.Controls 
        If chkbox.Tag = "CheckBox" Then 
            If chkbox.Value = True Then 
                name = chkbox.name 
                counter = counter + 1 
                 
                Set sheet = Worksheets("Sheet1") 
                sheet.Range("Q" & counter) = Me.Controls(Replace(name, "Q", "P")).Text 
                 
            End If 
        End If 
    Next chkbox 
    Application.CutCopyMode = False 
    Unload Me 
End Sub

However it doesnt do what I am trying to do and I really need your help with this please.

It copies the changed percentages onto Sheet1 Column Q, however when OK button is clicked first it copies the changed percentage into first cell of column Q (Q1), I want this to be copied on the second cell (Q2) as Q1 is the header.

Second problem is that when I change percentages, I would like the checkbox next to it to become checked automatically.

Third issue is that when I have changed the percentages and checked the checkbox manually, press OK then the changed percentages are copied to column Q but does not corespond to the correct row e.g change textbox 1, 2 and 4 on the userform, it should copy the changed percentages to cell Q2, Q3, Q5 but instead it copies it to Q1, Q2, and Q3.


Really really really appreciate all the help you can give me.

Thank you all in advance.

 

[Kyle123]

Hi James

I'm a little confused, if you are using Excel as a front end to a database, why aren't you interacting with the database directly?

 

[James123456]

Hi James

I'm a little confused, if you are using Excel as a front end to a database, why aren't you interacting with the database directly?

Hi Kyle,

There are aspects to this project that relies on values brought back from database, processed in excel by user then injected back into sql. The values in the columns N, O and P are generated by the user.

Hope that explains it.

 

[Norie]

Why are you using dynamic controls instead of something like a listbox or listview?

Both would allow you to display multiple items/records and have a checkbox on each row.

A listview would also allow the values to be editable.

It would also be easy to place values back on the worksheet on the correct row.

 

[James123456]

Why are you using dynamic controls instead of something like a listbox or listview?

Both would allow you to display multiple items/records and have a checkbox on each row.

A listview would also allow the values to be editable.

It would also be easy to place values back on the worksheet on the correct row.

Hi Norie,

Thanks for replying. I didnt know you could do that. I have used lisbox before with the checkboxes and multiple selection but did not know you could edit as well.

I know I am asking alot here but could you possibly show me an example worksheet with some data please?

Thanks.

 

[Norie]

A listbox wouldn't be directly editable but a listview would be.

I think I've got an suitable listview example somewhere.

I'll need to look it out though.

PS If I don't find it I should be able to mock something up.

 

[James123456]

A listbox wouldn't be directly editable but a listview would be.

I think I've got an suitable listview example somewhere.

I'll need to look it out though.

PS If I don't find it I should be able to mock something up.

Norie,

I would be forever appreciative. Thanks alot.

 

[Norie]

Started setting something up but ealised that it's only the first item that's directly editable in the ListView.

So I'm not sure that would really be suitable.

I did have another idea of putting values from the listbox/listview into textboxes when a 'row' is selected.

Then the user could edit them and the things could be updated.

Again probably not suitable though.

 

[James123456]

Started setting something up but ealised that it's only the first item that's directly editable in the ListView.

So I'm not sure that would really be suitable.

I did have another idea of putting values from the listbox/listview into textboxes when a 'row' is selected.

Then the user could edit them and the things could be updated.

Again probably not suitable though.

First item would do as well, i could move things around.

The idea of having the row seleted and then change the value in the textbox could work as well.

Could you please give me both examples?

Thanks.

 

[Norie]

You wouldn't need to move things about, just populate the list slightly differently than you normally would.

I'll need to go back to the file, I kind of abandoned it when I realised it was only the first column that could be edited.

I did save it though.